-
Jan 21st, 2011, 07:58 AM
#1
Thread Starter
Addicted Member
Problems with Oracle column type LONG
Hello
My VB6 application reads data from one oracle table via DAO objects. This table contains also one column with LONG type.
When I loaded data into recordset I had got wrong texts for this LONG column from 105. rowid. But data for other columns are correct also after rowid 105.
I found that problem appears on computers with german Windows. I have english Windows and I have got the correct data for all columns.
Can somebody help me where is problem?
Thanks
-
Jan 21st, 2011, 09:13 AM
#2
Re: Problems with Oracle column type LONG
Which version of Oracle are you using? What do you mean by wrong texts? We had a similar problem a few years ago with French characters. French characters with accents were getting replaced by inverted question marks in the database. We had to change the character set of the Oracle database.
This was not happening for just the long columns, but all columns. So your issue might be a different one.
Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
save a blobFileStreamDataTable To Text Filemy blog
-
Jan 21st, 2011, 09:19 AM
#3
Thread Starter
Addicted Member
Re: Problems with Oracle column type LONG
I mean that rows from number 105 contain other texts assigned to another rows for this column.
I haven't any problems with characters as you mentioned.
-
Jan 21st, 2011, 09:28 AM
#4
Re: Problems with Oracle column type LONG
In that case there are two possibilities:
1) Your query is incorrect. (You need to post your table structures, sample data, query and query results for that sample data.)
2) You have discovered a new Oracle Bug. (You need to contact Oracle and report this.)
Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
save a blobFileStreamDataTable To Text Filemy blog
-
Jan 21st, 2011, 10:53 AM
#5
Thread Starter
Addicted Member
Re: Problems with Oracle column type LONG
I have one simply table containing column 'additionalinfo' with LONG type and I'm going to load data from this table by following way:
Code:
Private Sub cmdTest_Click()
Dim cSQL As String
Dim rs As Recordset
lstVersionDescriptions.Clear
Err.Clear
On Error GoTo ErrorHandler
cSQL = "SELECT projectversionid,additionalinfo as VersionDescription FROM projectversiontab WHERE projectversionid < 300 Order By projectversionid"
Set rs = MyDataBase.OpenRecordset(cSQL, dbOpenSnapshot)
If Not rs Is Nothing Then
With rs
While Not .EOF
lstVersionDescriptions.AddItem lstVersionDescriptions.ListCount + 1 & vbTab & .Fields("ProjectVersionID").Value & vbTab & .Fields("VersionDescription").Value
.MoveNext
Wend
Set rs = Nothing
End With
End If
Exit Sub
ErrorHandler:
MsgBox Err.Description, vbCritical
End Sub
When I compiled this program and started on my PC i had got correct texts for every projectversionid, but on the german PC the texts from 102(sorry not 105) are assigned incorrect.
I don't know where can be problem. DAO? As far as I know the column with LONG type contains unicode texts. Maybe it causes these problems.
Any ideas?
Thanks
-
Jan 21st, 2011, 04:14 PM
#6
Re: Problems with Oracle column type LONG
You can not use AddItem to update Long Columns if they exceed 2000 characters in length.
If you want an ADO based solution, I have one for you.
Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
save a blobFileStreamDataTable To Text Filemy blog
-
Jan 24th, 2011, 02:55 AM
#7
Thread Starter
Addicted Member
Re: Problems with Oracle column type LONG
Can you post me example based on ADO technology. I would to try replace it.
thanks
-
Jan 24th, 2011, 03:55 PM
#8
Re: Problems with Oracle column type LONG
Go through this tutorial: http://www.vbforums.com/showthread.php?t=376767
It is for access, but the part where you have this code
Code:
With rs
.AddNew
.Fields("Description") = strDescription 'adding record to db
.Fields("Picture").AppendChunk bytData 'adding the picture to the db
.Update
End With
is the trick to adding long information in the database.
My Oracle Example is very similar.
I will check my computer to see if I cna find it.
Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
save a blobFileStreamDataTable To Text Filemy blog
-
Jan 24th, 2011, 05:53 PM
#9
Thread Starter
Addicted Member
Re: Problems with Oracle column type LONG
thanks abhijit
i've remaked it and ado has not any problems with oracle longs. it seems that i will have to use it instead of dao.
-
Jan 25th, 2011, 07:56 AM
#10
Re: Problems with Oracle column type LONG
Originally Posted by Metalpalo
thanks abhijit
i've remaked it and ado has not any problems with oracle longs. it seems that i will have to use it instead of dao.
Could you please post your code? Which ado libraries are you using?
Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
save a blobFileStreamDataTable To Text Filemy blog
-
Jan 25th, 2011, 09:47 AM
#11
Thread Starter
Addicted Member
Re: Problems with Oracle column type LONG
Hello
It seems that I have got problem also with ADO.
I don't why the field with Long value returns only part of whole string cca(100 characters). That means that following code:
Code:
rs.Fields("VersionDescription").Value
doesn't contain whole string.
Can somebody tell me where is problem? I'm using 'OraOLEDB.Oracle' provider.
Thanks
-
Jan 25th, 2011, 10:54 AM
#12
Re: Problems with Oracle column type LONG
As I said earlier, you cannot use a rs.Fields("") to fetch value from a long column.
The correct way to do this is use a method called Append.
For a more complete example, you can check this link.
Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
save a blobFileStreamDataTable To Text Filemy blog
-
Jan 25th, 2011, 11:25 AM
#13
Thread Starter
Addicted Member
Re: Problems with Oracle column type LONG
Sorry
but I don't understant
I found that all texts longer than 100 characters are truncated to this mentioned length.
I've tried to use something as
Code:
rs.Fields("").GetChunk(500)
but also in this case I can fetch only first 100 characters
I found very important thing:ChunkSize (OraOleDb provider)
Does somebody know it?
Last edited by Metalpalo; Jan 25th, 2011 at 12:15 PM.
-
Jan 25th, 2011, 11:55 AM
#14
Re: Problems with Oracle column type LONG
Hello
I have located the code in a subversion repository. The long column is not the same as a varchar2 column.
vb Code:
Const conChunkSize = 100 'This is the amount of bytes you want to fetch at a time.
Dim lngOffset As Long
Dim lngLogoSize As Long
Dim varlogo As Variant
Dim varChunk As Variant
lngOffset = 0
lngLogoSize = rsctx1(gsCOMMENTS).ActualSize
Do While lngOffset < lngLogoSize
varChunk = rsctx1(gsCOMMENTS).GetChunk(conChunkSize)
varlogo = varlogo & varChunk
lngOffset = lngOffset + conChunkSize
Loop
If you use this code, I am positive that you will get everything in the varlogo variable.
Please let me know how this code works out for you. In our case, we have been using this code for several years. We use a slightly different provider though. I would like to know how this works with your provider.
Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
save a blobFileStreamDataTable To Text Filemy blog
-
Jan 25th, 2011, 01:23 PM
#15
Thread Starter
Addicted Member
Re: Problems with Oracle column type LONG
Hello abhijit
I've tried your code for both cursors(client, server) but with same results. All longer texts(len() > 100) are trucated by my provider. It seems that the GetChunk method works on already truncated strings and has not any sense.
I found following link:
http://forums.oracle.com/forums/thre...3157&tstart=30
It seems that was my problem. Default value for this property "ChunkSize" is 100. So I changed it to 4000 and it works fine.
Thanks
-
Jan 25th, 2011, 02:00 PM
#16
Re: Problems with Oracle column type LONG
What if your field has more than 4000 characters in it?
You will then need to use the loop to append all characters to the variable. That's what I have demonstrated in the code above.
Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
save a blobFileStreamDataTable To Text Filemy blog
-
Jan 25th, 2011, 03:35 PM
#17
Thread Starter
Addicted Member
Re: Problems with Oracle column type LONG
It's good question.
I wish that your code is sufficient to gell all characters but I don't know why doesn't work for my provider. I will have to try it for Oracle 10.2.0, now I'm using version 9.
If I keep default value 100(ChunkSize) then your code gives max 100 characters.
If I set it to 4000 then your code gives me max 4000 characters.
I'm unhappy for this moment.
-
Jan 26th, 2011, 06:37 AM
#18
Re: Problems with Oracle column type LONG
Do you have a loop in the code? I want to see the code that you have used to fetch the long column contents.
Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
save a blobFileStreamDataTable To Text Filemy blog
-
Jan 26th, 2011, 03:48 PM
#19
Thread Starter
Addicted Member
Re: Problems with Oracle column type LONG
Here is my code:
Code:
Set xResult = New Collection
Set rs = New ADODB.RecordSet
rs.Open cSQL, gConnection, adOpenForwardOnly, adLockReadOnly, adCmdText
With rs
While Not .EOF
Set xItem = New VersionInfo
xItem.VersionID = .Fields("VersionID")
If Not IsNull(.Fields("VersionDescription")) Then
Dim lngOffset As Long
Dim lngLogoSize As Long
Dim varlogo As Variant
Dim varChunk As Variant
lngOffset = 0
varlogo = ""
varChunk = ""
lngLogoSize = .Fields("VersionDescription").ActualSize
Do While lngOffset < lngLogoSize
varChunk = .Fields("VersionDescription").GetChunk(100)
varlogo = varlogo & varChunk
lngOffset = lngOffset + 100
Loop
xItem.AdditionalInfo = varlogo
End If
xResult.Add xItem
.MoveNext
Wend
End With
I found that it works for my provider only in case that SQL query doesn't contain JOIN(s). It was mentioned in link above. The simply select query from one table will not cause problem. I've tried to check also MSDASLQ provider and i found that it doesn't truncate longs as one for oracle.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|