Results 1 to 19 of 19

Thread: Problems with Oracle column type LONG

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Feb 2007
    Posts
    177

    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

  2. #2
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    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

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Feb 2007
    Posts
    177

    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.

  4. #4
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    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

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Feb 2007
    Posts
    177

    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

  6. #6
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    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

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Feb 2007
    Posts
    177

    Re: Problems with Oracle column type LONG

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

    thanks

  8. #8
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    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

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Feb 2007
    Posts
    177

    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.

  10. #10
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    Re: Problems with Oracle column type LONG

    Quote Originally Posted by Metalpalo View Post
    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

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Feb 2007
    Posts
    177

    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

  12. #12
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    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

  13. #13

    Thread Starter
    Addicted Member
    Join Date
    Feb 2007
    Posts
    177

    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.

  14. #14
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    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:
    1. Const conChunkSize = 100 'This is the amount of bytes you want to fetch at a time.
    2. Dim lngOffset As Long
    3. Dim lngLogoSize As Long
    4. Dim varlogo As Variant
    5. Dim varChunk As Variant
    6. lngOffset = 0
    7.     lngLogoSize = rsctx1(gsCOMMENTS).ActualSize
    8.     Do While lngOffset < lngLogoSize
    9.         varChunk = rsctx1(gsCOMMENTS).GetChunk(conChunkSize)
    10.         varlogo = varlogo & varChunk
    11.         lngOffset = lngOffset + conChunkSize
    12.     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

  15. #15

    Thread Starter
    Addicted Member
    Join Date
    Feb 2007
    Posts
    177

    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

  16. #16
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    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

  17. #17

    Thread Starter
    Addicted Member
    Join Date
    Feb 2007
    Posts
    177

    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.

  18. #18
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    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

  19. #19

    Thread Starter
    Addicted Member
    Join Date
    Feb 2007
    Posts
    177

    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
  •  



Click Here to Expand Forum to Full Width