Automatic prepared statement problem

Databases
1

This code works, either with the commented out first statement that has field values hard coded or the second one that uses variables:

'var sql As String = "SELECT A.SerNo, A.Model, A.MeterID, A.LynxKey, B.Name, B.Port, B.MtrID, B.LynxKey FROM Meters A Left JOIN Units B ON A.MeterID = B.MtrID "+_
'“WHERE A.SerNo = 67530 AND A.MeterID <> 0 AND A.LynxKey = 8 AND B.LynxKey = 8”

var sql As String = "SELECT A.SerNo, A.Model, A.MeterID, A.LynxKey, B.Name, B.Port, B.MtrID, B.LynxKey FROM Meters A LEFT JOIN Units B ON A.MeterID = B.MtrID "+_
“WHERE A.SerNo = " + tfSerNo.Text + " AND A.MeterID <> " + str(recID) +” AND A.LynxKey = “+Session.LynxKey+” AND B.LynxKey = " + Session.LynxKey

var rs As RowSet = Session.db.SelectSQL(sql)

This code throws a database exception 1 - near “A”: syntax error

var sql As String = "SELECT A.SerNo, A.Model, A.MeterID, A.LynxKey, B.Name, B.Port, B.MtrID, B.LynxKey FROM Meters A LEFT JOIN Units B ON A.MeterID = B.MtrID "+_
“WHERE A.SerNo = ? AND A.MeterID <> ? AND A.LynxKey = ? AND B.LynxKey = ?”

var rs As RowSet = Session.db.SelectSQL(sql, tfSerNo.Text, recID, Session.LynxKey, Session.LynxKey)

At this time, there are no records in either of the two tables. All the variables are numeric. All are stored as strings except recID which is stored as Integer. I have retyped the bottom statement and run it numerous times with small changes, but I cannot get it to work as a prepared statement. I’m starting to wonder about the reliability of the new automatic prepared statements.

2

Should you be using Meters A everywhere in the select instead of A? because your FROM clause looks for Meters A. Or perhaps Meters is superfluous and it should be A everywhere.

3

< > is not valid SQL AFAIK.

Try !=

4

Nope, you can use both SQL Not Equal operators <> and != to do inequality test between two expressions. Both operators give the same output. The only difference is that ‘<>’ is in line with the ISO standard while ‘!=’ does not follow ISO standard. You should use <> operator as it follows the ISO standard.

https://blog.sqlauthority.com/2013/07/08/sql-difference-between-and-operator-used-for-not-equal-to-operation/

5

Well I’ll be darned. I don’t remember any info about < > being valid when I was learning SQL 25 years ago.

1 Like
6

You know that these two aren’t equal right?

The prepared statement is going to put quotes around the value of tfSerNo.Text

7

There’s a difference between the “recID” parameter in the “variables version” vs. the prepared statement.

In the former, recID is change to a string:

str(recID)

In the latter, the integer value of “recID” is used as is vs. being converted to a string:

var rs As RowSet = Session.db.SelectSQL(sql, tfSerNo.Text, recID, Session.LynxKey, Session.LynxKey)

This might be causing the issue?

8

I have changed <> to != with no change.
I changed

WHERE A.SerNo = " + tfSerNo.Text + " AND

to

WHERE A.SerNo = ‘" + tfSerNo.Text + "’ AND

also without change.

9

Anthony Dellos

There’s a difference between the “recID” parameter in the “variables version” vs. the prepared statement.

In the former, recID is change to a string:

str(recID)

In the latter, the integer value of “recID” is used as is vs. being converted to a string:

var rs As RowSet = Session.db.SelectSQL(sql, tfSerNo.Text, recID, Session.LynxKey, Session.LynxKey)

This might be causing the issue?

Isn’t this what the new automated prepared statement is supposed to do? I was under the impression that it sets the BindType from the type of variable passed to it.

10

There appear to be multiple problems with the automatic prepared statement, ExecuteSQL. In another place, I successfully write a record. I commit it then get its row ID. When I try to save another record in a different table, it throws a database error that says “cannot commit - no transaction active” which makes no sense to me at all.

11

Dean,
What database are you using? If it’s one that Valentina Studio works with, get a copy and play with your query there. It handles prepared statements. Very helpful in dealing with sql queries.

Also, when I have a complex query, I put the sql in a constant. That way once I get it working in Valentina Studio, I can simply paste it in the Xojo constant; and off I go.

Hope this helps.

-Bob

Happy New Year

12

Bob,

Right now I am using SQLite, but it needs to also work with MySQL. This is a web app that will run on a small appliance, probably a Raspberry Pi, read electric submeters and send the data to the cloud. The cloud version of the same program will let the user configure the system and get reports when not on the LAN with the Pi. Originally, they were two programs, but I thought that the new automatic prepared statements would let me run the same program on both the Pi and a VPS. Looks like that may not happen if I can’t get it to work. Most of it works, but I have two areas where it doesn’t. One does not need the prepared statement, but the other does.

I usually work with SQLite and SQLIte Manager that lets me do pretty much what you do. Except I usually work backwards. I get the sql string out of the debugger and and paste it into the manager and run it. It quickly tells me what the errors are. If its really complicated, I do what you do. It doesn’t work so well with prepared statements. Since most of the old API 1.0 sql statements that I have converted to API 2.0 seem to work, I am thinking there is a problem with Xojo in the way it handles things. One area that is suspect is that all my tables have a field named LynxKey which is the primary key in the MySQL database for the particular Pi module. All my queries include LynxKey for every table and I am wondering if that is getting things screwed up. But the one where I try to write a new record and the the error message is that there is no transaction to commit is really weird. No idea about that one.

13

Then perhaps the “tfSerNo.ToText” value is the issue? You’re still coercing it to be a string.

I can’t see what bind types you’re selecting (you didn’t include this in your code example)

I’m simply pointing out there are variances in how you’re doing things beyond the usage of the prepared statements.

For the record, I find xojo’s built-in prepared statements difficult to debug, so much so I wrote a custom “wrapper” prepared statements class so I could actually see/control what the passed values are; preview the generated SQL; etc…

In the end, the wrapper class does still use the various Xojo prepared statement classes, which seem to work very well if everything is “just so”; I just find them to be opaque, and therefore very difficult to debug.

14

… FROM Meters A … is the same as …Meters AS A…

15

The only difference I can see is that the original version will produce

WHERE A.SerNo = 67530

whereas the prepared statement will produce

WHERE A.SerNo = "67530"

If you put tfSerNo.Text into an integer variable and use that in the prepared statement, does it make a difference. It shouldn’t, as quotes are optional around integer fields, but it may be worth testing.

16

Anthony Dellos

Then perhaps the “tfSerNo.ToText” value is the issue? You’re still coercing it to be a string.

I can’t see what bind types you’re selecting (you didn’t include this in your code example)

The Xojo ExecuteSQL command is supposed to figure out the bind types automatically and apply them. I have not tried to select a bind type. I don’t think that is even coming into it as the two database table do not have any records in them yet.

17

@Dean_Davidge I didn’t realize you were using the new SelectSQL method. Mea culpa.

I haven’t used these new methods yet, so I can’t comment on their reliability. I did notice the SQLite Database has more than just the “?” parameter, fyi. See here:

https://documentation.xojo.com/api/databases/database.html#database-selectsql

https://www.sqlite.org/lang_expr.html#varparam

but I don’t think these apply to your use case.

Considering the Database.SQLSelect method seems to take values as a variant, I now agree it shouldn’t matter what you pass, but I’d still drop the “.Text” here:

tfSerNo.Text

It might be causing an API2 compatibility issue (Text was part of the “New Api” (xojo.core) vs. API2 and might be misbehaving with Database.SQLSelect which is an API2 method)

Good luck!
Anthony

18

tfSerNo.Text is the value of a WebTextField. Xojo 2020r2.1 uses .Text instead of .Value and is described as a string in the LR.

I have looked at the SQLite docs and want to try to keep the “?” parameter so the same code will work with a MySQL database also.

19

Time to start simplifying the statement. Back stuff out until it works, then add stuff back in to see what breaks.

3 Likes
20

What version of Xojo are you using?

I know you’ve said that you’ve retyped the select but for my peace of mind please select the entire statement over multiple lines, right click and click Clean invisible ascii characters.

If that doesn’t do it, replace all your parameters on the selectsql line with hard coded value and see if that makes any difference. If not, do as Tim suggest above and start removing sql and params until you find the problem as everything looks fine in isolation but something isn’t right.

Side note, make sure you’re not mixing old and new api calls, if you have an error using an api1 call and the api2 call is fine then it’ll raise the error from the api1 call on the api2 call. <https://xojo.com/issue/58909> apparently fixed this for postgresql but I just checked and its still in there for sqlite on 2020r2.1. Bug report anyone? I doubt this’ll be happening in your case though as you’re replacing code blocks and it goes back to not raising the error.