Error on sqlite3preparev2

Jun 28, 2012 at 5:04 PM
Edited Jun 28, 2012 at 5:06 PM

Hi Govert,

I appreciate your efforts in creating these dlls. I'm trying to use them to make a very small, simple database. When I run your tests (sub AllTests() ) everything works fine.

When I try to run the code below I get an error 1 on the prepare statement.

------------------------------------------------------------------------------

Public Const sqlite3dllpath = "L:\Reference\Electrical\SQLiteDLLS\Source\SQLite3_StdCall"

Sub IntializeEverything()
    Dim dbpath As String
    Dim mydbhandle As Long
    Dim myStmtHandle As Long
    Dim retval As Long
    Dim stepMsg As Long
    Dim loopkill As Integer
     
    dbpath = "C:\foods.db"
   
    SQLite3Initialize (sqlite3dllpath)
    retval = SQLite3Open(dbpath, mydbhandle)
    Debug.Print "SQLite3Open returned " & retval
       
    Debug.Print "Preparing statement"
    retval = SQLite3PrepareV2(mydbhandle, "SELECT * FROM foods_type", myStmtHandle)
   
    Debug.Print "Prepare returned " & retval
   
    loopkill = 1
    Do While retval <> SQLITE_DONE

        retval = SQLite3Step(myStmtHandle)
        loopkill = loopkill + 1

        If loopkill = 10000 Then
            SQLite3Finalize (myStmtHandle)
            SQLite3Close (mydbhandle)
            Exit Sub
        End If

    Loop
   
    PrintColumns (myStmtHandle)
   
    retval = SQLite3Finalize(myStmtHandle)
   
End Sub

Some notes: the dlls are stored on a shared drive. The database I'm trying to use above is on my C: drive. I have moved the db to the same folder as the dlls and it didn't fix the error. This database is used on sqlzoo for teaching sql (something else I'm learning) and is freely available.

I am not a programmer by trade. I'm just trying to simplify some work we do here. I can't figure this out- or, at least, I haven't yet.

 

Thanks very much for your time.

 

Don

Jun 28, 2012 at 6:07 PM

Hi Don,

I presume your SQLite3Open call is returning 0 (SQLITE_OK)?

I see (at the top of the SQLite3 module) that error 1 means this:

Public Const SQLITE_ERROR       As Long = 1   ' SQL error or missing database

I think this is the error you'd get if the table name is wrong, i.e. if your database has no table called "foods_type".

You can also try this query to get a list:

SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;

There is also a SQLite command-line utility on the SQLite site (with usage examples here: http://sqlite.org/sqlite.html) which can help you explore the database to check the exact table name.  There are also numerous graphical tools for SQLite.

Regards,

Govert

Jun 28, 2012 at 7:18 PM

Hello again,

I am indeed getting SQLITE_OK on the call to open. I think I figured out the problem, although I'm not sure why I was getting what I got. Somehow I copied the food.db that I was using as an empty database. I copied it again and this time I'm getting correct results. You can be sure I'll have more questions. Thanks again.