Results 1 to 18 of 18

Thread: RC6+SQLite error: No such Field-Def

  1. #1

    Thread Starter
    Hyperactive Member Mith's Avatar
    Join Date
    Jul 2017
    Location
    Thailand
    Posts
    445

    Question RC6+SQLite error: No such Field-Def

    RC6 v6.0.015 + cairo_sqlite

    I dont understand why i get the error "-2147221504 - No such Field-Def: id" when i access the existing column via RS("id").

    How is this possible if i explicit select the id column with the sql statement and RS.RecordCount is larger than 0?

    My code:

    Code:
    SQL = "SELECT id FROM files WHERE file = ?"
    Set sCMD = connDBhash.CreateSelectCommand(SQL)
    sCMD.SetText 1, sFile
    Set RS = sCMD.Execute
    If RS.RecordCount > 0 Then
       ID = RS("id")
    end if

  2. #2
    Frenzied Member VanGoghGaming's Avatar
    Join Date
    Jan 2020
    Location
    Eve Online - Mining, Missions & Market Trading!
    Posts
    1,220

    Re: RC6+SQLite error: No such Field-Def

    Why don't you print the Field names in that RecordSet to see it any of them is called "id"?

  3. #3
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,046

    Re: RC6+SQLite error: No such Field-Def

    Try to return a second (i.e. one last) dummy column like this SELECT id, 0 AS Dummy FROM files WHERE file = ?

    When sometimes the wrapper loses track of the last column in the resultset this fixes the issue (loses track of the dummy column instead) and it's not apparent if the problem is in sqlite or RC6.

    cheers,
    </wqw>

  4. #4

    Thread Starter
    Hyperactive Member Mith's Avatar
    Join Date
    Jul 2017
    Location
    Thailand
    Posts
    445

    Re: RC6+SQLite error: No such Field-Def

    Quote Originally Posted by VanGoghGaming View Post
    Why don't you print the Field names in that RecordSet to see it any of them is called "id"?
    Because i cant reproduce this error. I just wondering why the RecordCount is >0 but there is no id column in the recordset...

  5. #5

    Thread Starter
    Hyperactive Member Mith's Avatar
    Join Date
    Jul 2017
    Location
    Thailand
    Posts
    445

    Re: RC6+SQLite error: No such Field-Def

    Quote Originally Posted by wqweto View Post
    When sometimes the wrapper loses track of the last column in the resultset this fixes the issue (loses track of the dummy column)
    Is this a known issue of SQLite or the wrapper?

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,293

    Re: RC6+SQLite error: No such Field-Def

    Does your SQL work in any other SQLite-Client (e.g. DB-Browser)?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,835

    Re: RC6+SQLite error: No such Field-Def

    In dhRichClient3 and vbRichClient5 the Command.Execute returns a Boolean and not a recordset.

  8. #8

    Thread Starter
    Hyperactive Member Mith's Avatar
    Join Date
    Jul 2017
    Location
    Thailand
    Posts
    445

    Re: RC6+SQLite error: No such Field-Def

    Quote Originally Posted by Arnoutdv View Post
    In dhRichClient3 and vbRichClient5 the Command.Execute returns a Boolean and not a recordset.
    RC6 returns a recordset:

    Name:  rc6_recordset.jpg
Views: 159
Size:  9.6 KB

  9. #9

    Thread Starter
    Hyperactive Member Mith's Avatar
    Join Date
    Jul 2017
    Location
    Thailand
    Posts
    445

    Re: RC6+SQLite error: No such Field-Def

    Quote Originally Posted by Zvoni View Post
    Does your SQL work in any other SQLite-Client (e.g. DB-Browser)?
    my SQL-statement works fine when i run several tests but there must be some special circumstance why it can happen that RecordCount is >0 but the recordset doesnt contain the selected column!

    I already have an idea about that:

    When the error occurred, the memory usage of the VB6 application was quite high (1.394,75 MB). Something was probably found by the select statement, which is why the RecordCount was greater than 0, but the recordset could not be filled due to lack of memory and was therefore empty!?

  10. #10
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,046

    Re: RC6+SQLite error: No such Field-Def

    Quote Originally Posted by Mith View Post
    Is this a known issue of SQLite or the wrapper?
    I've dealt with the same problem w/ earlier dhRichClient (version 2.3).

    cheers,
    </wqw>

  11. #11

    Thread Starter
    Hyperactive Member Mith's Avatar
    Join Date
    Jul 2017
    Location
    Thailand
    Posts
    445

    Re: RC6+SQLite error: No such Field-Def

    In the meantime, the user sent me the complete error log file.

    The 1. error was "-2147220991 - An unhandled error (EXCEPTION_ACCESS_VIOLATION) " at the line "Set RS = sCMD.Execute".

    The VB exe file was edited with /LARGEADDRESSAWARE and the memory usage was around 1.394,75 MB when the error occurred.

    I guess RC6 v6.0.015 and/or cairo_sqlite isnt large address aware?

  12. #12
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,046

    Re: RC6+SQLite error: No such Field-Def

    Possible however unlikely. . . but a fix is relatively easy to attempt w/ something like this in your application's startup code:


                '--- allocate 64KB at the 2GB boundary
                
    Call VirtualAlloc(&H7FFF0000, &H10000, MEM_COMMIT Or MEM_RESERVE, PAGE_READWRITE)


    This pre-emptive allocation prevents any heap allocation (ReDim, CoTaskMemAlloc, etc.) spanning the 2GB boundary and this way any VB6 code which uses lPtr + lOffset pointer math is safe from Overflow errors i.e. there is no need to refactor pointer math to use UnsignedAdd(lPtr, lOffset) helper function for instance in your code or any 3-rd party (compiled) code too.

    cheers,
    </wqw>

  13. #13

    Thread Starter
    Hyperactive Member Mith's Avatar
    Join Date
    Jul 2017
    Location
    Thailand
    Posts
    445

    Re: RC6+SQLite error: No such Field-Def

    Quote Originally Posted by wqweto View Post
    This pre-emptive allocation prevents any heap allocation (ReDim, CoTaskMemAlloc, etc.) spanning the 2GB boundary
    i guess i cant use this command because my binary tree collection class uses redim to allocate memory after the 2GB boundary if necessary...

  14. #14
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,293

    Re: RC6+SQLite error: No such Field-Def

    Getting back to your original error-message (No such Field-Def: id):
    Can you access it with ordinal?
    Code:
    If RS.RecordCount > 0 Then
       ID = RS.Fields(0)  'Or RS(0)
    end if
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  15. #15
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,835

    Re: RC6+SQLite error: No such Field-Def

    Quote Originally Posted by Mith View Post
    RC6 returns a recordset:

    Name:  rc6_recordset.jpg
Views: 159
Size:  9.6 KB
    Duh, of course, you are using a Select Command, I thought an Update Command
    My bad

  16. #16
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,173

    Re: RC6+SQLite error: No such Field-Def

    Quote Originally Posted by Mith View Post
    i guess i cant use this command because my binary tree collection class uses redim to allocate memory after the 2GB boundary if necessary...
    I guess the error creeps in, due to operating "near the 2GB Mem-Limits" (and/or due to making the App LargeAddressAware).

    If I may ask, what part of your App is causing this large mem-usage?

    If it is due to your self-written "binary tree"-collection -
    why don't you use an "on-disk" SQLite-DB instead of this BinTree-Col (to save sys-memory in your App)?

    SQLite table-access is based on Binary-Trees already -
    and the engine incorporates also a quite clever caching (when working against a File-DB).

    Also, the fastest way to do "single-record-lookups" against such an SQLite-based "Key-Value"-Table:
    - is not the cRecordset
    - but the new (more lightweight) cCursor-Class of the RC6.

    E.g. If you define a Table like this (in a FileDB-Connection):
    Create Table BinTree(Key Text Collate NoCase Primary Key, Value Text)

    You can then do superfast lookups "on disk" (not wasting system-memory) via cCursor...

    Here is an example:
    Class cBinTree
    Code:
    Option Explicit
    
    Private mInsert As cCursor, mLookup As cCursor
    
    Public Sub CreateOnFile(FileName As String)
      If New_c.FSO.FileExists(FileName) Then New_c.FSO.DeleteFile FileName
      
      With New_c.Connection(FileName, DBCreateNewFileDB)
          .Execute "Create Table BinTree(Key Text Collate NoCase Primary Key, Value Text) Without RowId"
          .BeginTrans
     
          'finally create Insert- and Lookup-Cursors on our (now filled) File-based SQLite-Connection
          Set mInsert = .CreateCursor("Insert Into BinTree Values(?,?)")
          Set mLookup = .CreateCursor("Select Value From BinTree Where Key=?")
      End With
    End Sub
    
    Public Sub Insert(Key As String, Value As String) 
      mInsert.SetText 1, Key
      mInsert.SetText 2, Value
      mInsert.Step
      mInsert.Reset
    End Function
    
    Public Function GetValue(Key As String) As String
      mLookup.SetText 1, Key
      If mLookup.Step Then GetValue = mLookup.ColVal(0)
      mLookup.Reset
    End Function
    TestForm-Code:
    Code:
    Option Explicit
    
    Const KeyValuePairsCount As Long = 500000 'half a Mio Test-KV-Pairs
    
    Private BinTree As New cBinTree
    
    Private Sub Form_Load()
      New_c.Timing True
        BinTree.CreateOnFile "c:\temp\BinTree.db3"
        
        Dim i As Long 'add some dummy Key-Value-pairs
        For i = 1 To KeyValuePairsCount
            BinTree.Insert "Key_" & i, "Value_" & i
        Next
        
      Caption = i - 1 & " Inserts done after:" & New_c.Timing
    End Sub
    
    Private Sub Form_Click()
      New_c.Timing True
        Dim i As Long, Value As String
        For i = 1 To 1000
          Value = BinTree.GetValue("key_" & Int(Rnd * KeyValuePairsCount) + 1) 'key-lookups are case-insensitive due to defining the Key-Column with Collate NoCase
          If Len(Value) = 0 Then Stop 'this should not happen
        Next
      Caption = "1000 Value-retrievals ""by Key"" took:" & New_c.Timing
    End Sub
    The above cCursor-based BinTree-Class can:
    - add half a Mio entries in less than a second
    - later doing Key-based Value-lookups on it in roughly 4 Microseconds per call

    That's not that far off from normal (InMemory)Collection- or Dictionary-Classes

    Olaf

  17. #17

    Thread Starter
    Hyperactive Member Mith's Avatar
    Join Date
    Jul 2017
    Location
    Thailand
    Posts
    445

    Re: RC6+SQLite error: No such Field-Def

    First: Thank you Olaf!

    Quote Originally Posted by Schmidt View Post
    If I may ask, what part of your App is causing this large mem-usage?
    It's the binary tree collection and the display of the data in a tree grid control.

    why don't you use an "on-disk" SQLite-DB instead of this BinTree-Col (to save sys-memory in your App)?
    I already tried this but its much much slower compared with the memory access of the collection.

    Also, the fastest way to do "single-record-lookups" against such an SQLite-based "Key-Value"-Table:
    - is not the cRecordset
    - but the new (more lightweight) cCursor-Class of the RC6.
    I will test this new cursor feature!

    Create Table BinTree(Key Text Collate NoCase Primary Key, Value Text)
    Is "BinTree" a exclusive RC6 feature or can i create this tables with other tools like SQLiteStudio too?


    'finally create Insert- and Lookup-Cursors on our (now filled) File-based SQLite-Connection
    Is this the correct process:

    1. Create DB (only with Bin Tree?)
    2. Fill DB
    3. Create cursors
    4. Use cursors

    Do i have to create the cursor after i filled the DB or before?

  18. #18
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,173

    Re: RC6+SQLite error: No such Field-Def

    Quote Originally Posted by Mith View Post
    It's the binary tree collection and the display of the data in a tree grid control.
    Please use a virtual TreeList or -Grid with OwnerDrawing-support in case you don't do so already.
    (copying larger amount of data into UI-Controls is bad, bad, bad design)...

    Keep the Data outside (e.g. in the cBinTree-Class I've posted) -
    and you will save system-memory twice (in case your TreeList is indeed virtual).

    Also, remove that /LargeAddressAware Flag from your compilations.
    It never worked right for us - there's always some component which will choke
    (from our experience - gathered at my workplace)...

    E.g. we had "weird errors" popping up in "pure ADO-Rs" + "ComponentOne-VSFlexGrids" (without any RC5 or RC6-involvment) -
    after we enabled this compilation-flag. (and that specific, memory-hungry App was including the "64KB-reserved-Mem"-Fix of wqweto).
    We've solved this, by switching to the OwnerDraw-Feature of the VSFlexGrid - to bring the memory-consumption down (and removing the Flag).

    Quote Originally Posted by Mith View Post
    [SQLite-FileDB instead of InMemory-BinTree]
    I already tried this but its is much much slower compared with the memory access of the collection.
    Nope, not in my tests...
    Instead of "much much slower" the SQLite-based cBinTree is roughly:
    - twice as fast as a VB-Collection when adding a larger amount of Items
    - and about 20% faster with "random, Key-based access" against that larger amount of Key/Value-Pairs

    Here's code, which demonstrates that:
    (using the same test-routines as in my snippet above, which introduced cBinTree, simply changed to VBA.Collection)
    Code:
    Const KeyValuePairsCount As Long = 500000
    
    Private Col As New VBA.Collection
    
    Private Sub Form_Load()
      New_c.Timing True
     
        Dim i As Long 'add some dummy Key-Value-pairs
        For i = 1 To KeyValuePairsCount
           Col.Add "Value_" & i, "Key_" & i
        Next
         
      Caption = i - 1 & " Inserts done after:" & New_c.Timing
    End Sub
    
    Private Sub Form_Click()
      New_c.Timing True
        Dim i As Long, Value As String
        For i = 1 To 1000
          Value = Col.Item("key_" & Int(Rnd * KeyValuePairsCount) + 1)
          If Len(Value) = 0 Then Stop 'this should not happen
        Next
      Caption = "1000 Value-retrievals ""by Key"" took:" & New_c.Timing
    End Sub

    Quote Originally Posted by Mith View Post
    Is "BinTree" a exclusive RC6 feature or can i create this tables with other tools like SQLiteStudio too?
    "BinTree" is just a table-name (and the SQL-Create Table statement is Std-SQL, which should work in any SQLite-Tool).


    Quote Originally Posted by Mith View Post
    Is this the correct process:

    1. Create DB (only with Bin Tree?)
    2. Fill DB
    3. Create cursors
    4. Use cursors
    Why not use the cBinTree-Class "as posted", which encapsulates (and hides) all the SQLite-mechanics?
    (as Table-creation and Cursor-usage)...

    And no, you don't have to "Fill the DB" (again and again) - in case the Data it holds, is mostly "static" (unchanging).
    In case you want to keep that Data of your "BinTree-DBFile", you will have to adapt the Cnn-Open-statement
    (from DBCreateNewFileDB to DBOpenFromFile ) ...and the first line, which deletes the FileDB if it exists, can go as well

    As for your question 1) above:
    I'd create each cBinTree-instance on its own "separate FileDB" (that DB-File then hosting only the single BinTree-Table),
    which should show performance-advantages, when the content you stuff into it is indeed in the range of about 1GByte.

    So, take care (in case you use multiple instance of it), that you provide "unique DB-FileNames" to each of these -
    via the initial CreateOnFile(FileName) method.

    Quote Originally Posted by Mith View Post
    Do i have to create the cursor after i filled the DB or before?
    As said, just use the little cBinTree-Class "as it is" (aside from the suggested adaptions, in case you want to skip the initial filling from static data which sits "elsewhere").

    Olaf
    Last edited by Schmidt; Dec 9th, 2023 at 06:08 AM.

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