SQL Select with variables

Hi,

with the code below I receive the field elevation of the german airport “Berlin Schoenefeld” (ICAO-Code = EDDB) from a sqlite database.

Dim dbFile As FolderItem
Dim db As New SQLiteDatabase
dbFile = GetFolderItem(“airports_database.sqlite”)
db.DatabaseFile = dbFile
If db.Connect Then
Dim rs As RecordSet
rs = db.SQLSelect("SELECT intElevation_ft FROM tblAirports WHERE txtAirport = ‘EDDB’ ")
If rs <> Nil Then
txtInputFieldElevation.text = str(rs.Field(“intElevation_ft”).Value)
rs.close
Else
MsgBox("The database couldn’t be opened. Error: " + db.ErrorMessage)
End If
end if

With the code below I assign the ICAO-Code EDDB to the string inputDepartureAirport and ensure that the input has 4 uppercase letters.

Dim inputDepartureAirport as String
txtDepartureAirport.LimitText=4
inputDepartureAirport = txtDepartureAirport.text.Uppercase
TextField1.text = inputDepartureAirport
(<— this is just to check that the input has 4 uppercase letters) …

But if I combine my SQLSelect statement with the String inputDepartureAirport I dont receive the field elevation from the database.

Dim dbFile As FolderItem
Dim db As New SQLiteDatabase
dbFile = GetFolderItem(“airports_database.sqlite”)
db.DatabaseFile = dbFile
If db.Connect Then
Dim rs As RecordSet
rs = db.SQLSelect("SELECT intElevation_ft FROM tblAirports WHERE txtAirport = “+inputDepartureAirport+” ")
If rs <> Nil Then
txtInputFieldElevation.text = str(rs.Field(“intElevation_ft”).Value)
rs.close
Else
MsgBox("The database couldn’t be opened. Error: " + db.ErrorMessage)
End If
end if

Where is my mistake?

Thanks in advance and best regards,
Gerson Nerger

You didn’t quote the data supplied by your variable, but you really should be using a Prepared Statement for this type of query.

dim ps as PreparedSQLStatement = _
    db.Prepare( "SELECT intElevation_ft FROM tblAirports WHERE txtAirport = ?" )
ps.BindType 0, SQLitePreparedStatement.SQLITE_TEXT
dim rs as RecordSet = ps.SQLSelect( inputDepartureAirport )

Also, please use the code tags rather than italics to post code. It makes it easier for us.

Hi Kem,

thank you for your reply. Unfortunately I get an error message:

wdwMain.ComputeFieldElevation, line 8
This method doesn’t return a value
Dim rs As RecordSet = ps.SQLSelect(inputDepartureAirport)

I changed my code to this:

Dim dbFile As FolderItem Dim db As New SQLiteDatabase dbFile = GetFolderItem("airports_database.sqlite") db.DatabaseFile = dbFile If db.Connect Then dim ps as PreparedSQLStatement = db.Prepare("SELECT intElevation_ft FROM tblAirports WHERE txtAirport = ?") ps.BindType 0, SQLitePreparedStatement.SQLITE_TEXT Dim rs As RecordSet = ps.SQLSelect(inputDepartureAirport) If rs <> Nil Then txtInputFieldElevation.text = str(rs.Field("intElevation_ft").Value) rs.close Else MsgBox("The database couldn't be opened. Error: " + db.ErrorMessage) End If end if

I don’t know if this is your problem, but the LR for PreparedSQLStatement indicates that "instead use the appropriate PreparedStatement class for the database you are using: in this case SQLitePreparedStatement so

dim ps as SQLitePreparedStatement 

What happens if you change

Dim rs As RecordSet = ps.SQLSelect(inputDepartureAirport)

to

Dim rs As RecordSet = ps.SQLSelect("EDDB")

From your original post, I think this:

rs = db.SQLSelect("SELECT intElevation_ft FROM tblAirports WHERE txtAirport = "+inputDepartureAirport+" ")

should be:

rs = db.SQLSelect("SELECT intElevation_ft FROM tblAirports WHERE txtAirport = '"+inputDepartureAirport+"' ")

Hi Alberto,

thank you for your reply.

Changing

Dim rs As RecordSet = ps.SQLSelect(inputDepartureAirport)

to

Dim rs As RecordSet = ps.SQLSelect("EDDB")

works but it does not help: I want that the SQL Statement gets the ICAO-Code from the String-variable “inputDepartureAirport”.

I checked different codes with direct inputs of the string to search:

rs = db.SQLSelect("SELECT intElevation_ft FROM tblAirports WHERE txtAirport = 'EDDB' ") worked

rs = db.SQLSelect("SELECT intElevation_ft FROM tblAirports WHERE txtAirport like 'eddb' ") worked

But if I want to use the variable instead it does not work anymore. I wonder if it has to do with case sensitivity …

The code

rs = db.SQLSelect("SELECT intElevation_ft FROM tblAirports WHERE txtAirport = '"+inputDepartureAirport+"' ")

does not work. It brings the same error message:

“This method doesn’t return a value”

Add a breakpoint at the beginning of the code you want to follow, run and follow step by step (line by line) your code: you may found from where the error lies.

OK … finally I found a solution that works. Instead of using the variable I use the textfield - for the input - directly …

Dim dbFile As FolderItem Dim db As New SQLiteDatabase dbFile = GetFolderItem("airports_database.sqlite") db.DatabaseFile = dbFile If db.Connect Then dim ps as PreparedSQLStatement ps = SQLitePreparedStatement(db.Prepare( "SELECT intElevation_ft FROM tblAirports WHERE txtAirport LIKE ?" )) ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT) ps.Bind(0, txtDepartureAirport.text) dim rs as RecordSet = ps.SQLSelect If rs <> Nil Then txtInputFieldElevation.text = str(rs.Field("intElevation_ft").Value) rs.close Else MsgBox("The database couldn't be opened. Error: " + db.ErrorMessage) End If end if

Isn’t that better:

   txtInputFieldElevation.text = rs.Field("intElevation_ft").StringValue

[quote=395757:@Emile Schwarz]Isn’t that better:

   txtInputFieldElevation.text = rs.Field("intElevation_ft").StringValue

Not necessarily. If you want control over how that number is formatted he should definitely use str or format.

Gerson, my guess is that your code have a problem with inputDepartureAirport, do you think it could be Nil when you trying to use it?

I usually put a Break point or use MsgBox to see if I have the value I want, so you may use MsgBox inputDepartureAirport just before

Dim rs As RecordSet = ps.SQLSelect(inputDepartureAirport)

Edit: Thank you Emile, I have ‘lots’ to learn

No, use System.DebugLog(String) instead.

http://documentation.xojo.com/index.php/System.DebugLog