Problems with Oracle column type LONG

[Metalpalo]

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

[abhijit]

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.

[Metalpalo]

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.

[abhijit]

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.)

[Metalpalo]

I have one simply table containing column 'additionalinfo' with LONG type and I'm going to load data from this table by following way:

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

[abhijit]

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.

[Metalpalo]

Can you post me example based on ADO technology. I would to try replace it.

thanks

[abhijit]

Go through this tutorial: http://www.vbforums.com/showthread.php?t=376767

It is for access, but the part where you have this 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.

[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.

[abhijit]

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?

[Metalpalo]

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:

rs.Fields("VersionDescription").Value

doesn't contain whole string.

Can somebody tell me where is problem? I'm using 'OraOLEDB.Oracle' provider.

Thanks

[abhijit]

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.

[Metalpalo]

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

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?

[abhijit]

Hello
I have located the code in a subversion repository. The long column is not the same as a varchar2 column.

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.

[Metalpalo]

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

[abhijit]

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.

[Metalpalo]

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.

[abhijit]

Do you have a loop in the code? I want to see the code that you have used to fetch the long column contents.

[Metalpalo]

Here is my 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.