step returns zero columns

Aug 3, 2012 at 5:24 PM

Hi,

I apologize for posting this here if you received my email...I was just thinking that maybe other people would appreciate any guidance you might have.

Question is:

 I’m having trouble with the Select call returning a result with zero columns.

I’m getting RetVal values of 0 from the Open and prepareV2 calls (which I believe means successful), and 100 from the Step call, but column count is always at 0….

The sqlite file was created in python (and has a .sqlite extension). The same Select call actually works in python, but I need this database to be read in another project…in excel.

If you have any insight I’d be greatly appreciative!!!!

Coordinator
Aug 3, 2012 at 6:05 PM

Hi,

It's hard to say how the Python binding affects the SQLite usage, but the SQLite for Excel wrapper doesn't really dp much, so you're seeing the result directly as it comes out of SQLite. Do you have result values in the Python case - for an empty result set the Python binding might be retrieving column information that is not returned by default, or something.

You might also try the SQLite command line with your database and query, just to have another point of reference.

But maybe this is simpler - like there are different databases in two locations that are getting confused.

Otherwise, maybe you can try from scratch to make a small database and query that misbehaves, and I could have a look.

Regards,

Govert

Aug 3, 2012 at 6:14 PM

Govert,

Thanks for you fast response!

The select query is as follows:

' Create the sql statement - getting a StmtHandle back

RetVal = SQLite3PrepareV2(myDbHandle, "SELECT * FROM Fuel_Loads WHERE TMY_ID = 724940", myStmtHandle)   

Debug.Print "SQLite3PrepareV2 returned " & RetVal

 

Thanks so much for taking a look at this. Dylan

Aug 3, 2012 at 10:46 PM

I've just come back to this after looking at some python/sqlite documentation...it seems that python uses UTF-8. Could this be the issue? That the database is expecting a UTF-8 encoded "prepare" statement? I tried to change your SQLite3PrepareV2 function from sqlite3_prepare16_v2 to sqlite3_prepare_v2 but that was not defined. Maybe this change has to happen in the dll ??

Not sure but seems like this might be a possibility?

Any thoughts are appreciated as always! Thanks Dylan

Coordinator
Aug 6, 2012 at 7:38 PM

Hi Dylan,

The SQLite for Excel mapping deals with the UTF8 strings properly - this won't be the issue.

I think you are getting the path to the database wrong. In this case the SQLite3Open will succeed (it just made the new database) but the SQLite3Prepare will return 1 (SQLITE_ERROR) and if you check with SQLIte3ErrMsg you get: "no such table: Fuel_Loads". This code worked fine on my machine (it depends on the PrintColumns sub in the SQLite3Demo module):

Public Sub DylanTest()

    Dim InitReturn As Long
    
    InitReturn = SQLite3Initialize ' Default path is ThisWorkbook.Path but can specify other path where the .dlls reside.
    If InitReturn <> SQLITE_INIT_OK Then
        Debug.Print "Error Initializing SQLite. Error: " & Err.LastDllError
        Exit Sub
    End If


    Dim testFile As String
    
    Dim myDbHandle As Long
    Dim myStmtHandle As Long
    Dim RetVal As Long
    
    Dim stepMsg As String
    
    Debug.Print "----- TestSelect Start -----"
    
    ' Open the database - getting a DbHandle back
    testFile = ThisWorkbook.Path & "\SiteLoads.sqlite"
    RetVal = SQLite3Open(testFile, myDbHandle)
    Debug.Print "SQLite3Open returned " & RetVal

    '-------------------------
    ' Select statement
    ' ===============
    ' Create the sql statement - getting a StmtHandle back
    RetVal = SQLite3PrepareV2(myDbHandle, "SELECT * FROM Fuel_Loads WHERE TMY_ID = 724940", myStmtHandle)
    Debug.Print "SQLite3PrepareV2 returned " & RetVal
    
    If RetVal = SQLITE_ERROR Then
        Debug.Print "SQLite3Step Error: " & SQLite3ErrMsg(myDbHandle)
        SQLite3Close myDbHandle
        Exit Sub
    End If
    
    ' Start running the statement
    RetVal = SQLite3Step(myStmtHandle)
    Do While RetVal = SQLITE_ROW
        PrintColumns myStmtHandle
        RetVal = SQLite3Step(myStmtHandle)
    Loop
    
    If RetVal = SQLITE_ERROR Then
        Debug.Print "SQLite3Step Error: " & SQLite3ErrMsg(myDbHandle)
        SQLite3Finalize myStmtHandle
        SQLite3Close myDbHandle
        Exit Sub
    ElseIf RetVal = SQLITE_DONE Then
        Debug.Print "SQLite3Step Done"
    Else
        Debug.Print "SQLite3Step returned " & RetVal
    End If
    
    ' Finalize (delete) the statement
    RetVal = SQLite3Finalize(myStmtHandle)
    Debug.Print "SQLite3Finalize returned " & RetVal
    
    ' Close the database
    RetVal = SQLite3Close(myDbHandle)
    Debug.Print "SQLite3Close returned " & RetVal
End Sub

Regards,

Govert

Aug 6, 2012 at 8:04 PM

Govert,

 

Thanks very much for looking into this...I would say the issue is solved...I used you code above and it worked perfectly. It seems like the simplest answer is often the right one, I can't believe I was looking in the wrong dir, sorry about the hassle.

This is a very nice product that you have developed.

Thank you again for the help!

 

Dylan