SQLitePreparedStatement type not found?

Hi all!

I have been getting to know Xojo for the past few days and really love what I see so far. Unfortunately, I’ve hit an odd roadblock with the following code:

If Not IsConnected Then
    MsgBox("Database not found.")
    Return
  End If
    
  Dim ps As SQLitePreparedStatement = DB.Prepare("SELECT * FROM Login WHERE Name = ? AND ID >= ?")
  
  ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
  
  Dim rs As SQLiteRecordSet = ps.SQLSelect(txtName.Text, txtUserID.Text)
  If rs <> Nil Then
    While Not rs.EOF
      CurrentUser = rs.Field("UserID").TextValue
      MsgBox("UserID: " + rs.Field("UserID").TextValue)
      rs.MoveNext
    Wend
  Else
    If db.Error Then MsgBox(db.ErrorMessage)
  End If

When I go to run the app with the above code, I get a “Can’t find a type with this name” error pointing to SQLitePreparedStatement as well as a “Type “SQLiteDatabase” has no member named “Prepare”” error below where I try to prepare the statement. I followed the code examples in the docs but have not had any luck getting them to work.

Been really banging my head against the wall on this one, so, any help would be greatly appreciated…

Thanks in advance!

Riz

what is the declared type of the variable DB ?

Your prepared statement is using field names “Name” and “ID”, but you’re referencing rs.Field(“UserID”). in your code. Try using rs.field(“ID”). instead.

Hi guys,

Thanks for the feedback so far…

@Norman Palardy: DB is a constant I have declared as SQLiteDatabase
@Tanner Lee : Thanks for noticing that, but it still did not fix my previous errors unfortunately

I even tried the following with the same results:

Dim ps As SQLitePreparedStatement 
ps = DB.Prepare("SELECT * FROM Login WHERE Name = ? AND ID >= ?")

Riz

Go back and make sure your DB property really is of type SQLiteDatabase… As a matter of fact, select it in the navigator, copy it and paste it here. I’d really like to see how it’s declared.

He appears to be using the new framework, based on sqliterecordset. The new framework documentation doesn’t list a Prepare method, but the example code does. Is that an error in the docs?

If you’re right, he’s mixing old and new framework code and that’s not going to work.

[quote=240192:@Riz Mawani]Hi guys,

Thanks for the feedback so far…

@Norman Palardy: DB is a constant I have declared as SQLiteDatabase
[/quote]
A constant ???
Or a property
That makes an enormous difference

Greg, below is the declaration for the DB property…

DB As SQLiteDatabase

Tim, yes, I switched from RecordSet (as per the docs) to SQLiteRecordSet due to a compile error.

Greg, I had no idea that I was mixing up frameworks… I assume I am mixing code from the REALBasic days with today’s Xojo code? The docs are confusing the crap out of me…

[quote=240242:@Norman Palardy]A constant ???
Or a property
That makes an enormous difference[/quote]

Sorry, Norman. I meant to write Property, not constant. DB is a property of type SQLiteDatabase that I had defined in a module with Global scope.

What kind of app are you creating ?
Desktop, Console, Web, iOS ?

[quote=240348:@Norman Palardy]What kind of app are you creating ?
Desktop, Console, Web, iOS ?[/quote]

iOS. Damn… Does iOS SQLiteDatabase support prepared SQL statements?

Ok, gents… I did a bit more digging around and eventually came across this thread, where @Norman Palardy’s reply in that thread gave me the fix I needed.

Below is the working code:

  If Not DB.Connect Then
    MsgBox("Database not found.")
    Return
  End If
  
  Dim sql As Text = "SELECT * FROM Login WHERE Name = ?1 AND ID = ?2"
  
  Try
    Dim rs As SQLiteRecordSet = DB.SQLSelect(sql, txtName.Text, txtUserID.Text)
    
    If rs <> Nil Then
      While Not rs.EOF
        CurrentUser = rs.Field("ID").TextValue
        MsgBox("UserID: " + rs.Field("ID").TextValue)
        rs.MoveNext
      Wend
    Else
      MsgBox("No records found.")
      Return
    End If
  Catch e As SQLiteException
    Dim err As Text = e.Reason
    MsgBox("Error: " + err)
  End Try

Getting the above to work, however, did not get me any closer to figuring out how to use SQLitePreparedStatement in an IOS project…

Thanks for all of your help so far, guys! I’ll get there, slowly but surely…

Last one, guys. I promise!

I read through the entire thread I had mentioned in my last message and lo and behold: @Norman Palardy’s post (the last of the thread) says that every SQLExecute in the new framework is via a prepared statement that doesn’t have to be created or have bound values/types declared beforehand.

Wish I had seen that thread sooner :-(… Would have wasted a lot less of everyone’s time!

Anyway, thanks again for all of the patience and help! Xojo’s dev community is truly awesome!

So the example in the iOS framework docs is completely wrong. It looks like it was just copied from the old framework docs.