Is it possible to use @variables with MySQL Selects? It's not working

I use to write mysql querys with TablePlus app and when I’m sure that the query is ok I copy it to xojo as a string variable to run with db.SelectSQL(query).

The problem is that I can’t specify a query with more than one line separated by a semicolon like

"SET @var1="xyz"; SELECT @var1"

it doesn’t work and I guess it has to do with the fact that the queries are one line only, but I can’t get the following to work either:

"SELECT @id:=217 as idFac, total from Facs where id=@id"

and I don’t understand why.

Can someone help me?

Would using the built-in prepared statement syntax work?

var idnumber as integer = 127
selectSQL("select $1, total from Facs where id=$1", idnumber)

It works with

var idnumber as integer = 127
SelectSQL("SELECT total from liquidaciones where idliquidacion=?", idnumber)

but I need to reuse that variable inside a little more complex query

I’m not sure how the complex query changes things for you, but
one messy but viable solution is to use a parameter table in a subquery.

Create a table called variables, with an integer field called num and a string field called str
Add one row to it only.

Then, for your queries:
“Update variables set num = 127”
followed by

“SELECT total from liquidaciones where idliquidacion=(select num from variables)”

and you can use (select num from variables) wherever you wanted the id to be used.
The str field lets you do the same thing with string values.

Thanks Jeff. It’s a little messy as you say but it’s a solution indeed although I guess in terms of performance it’s not the best.
What I don’t understand is where the problem or limitation is for Xojo with the sintax:

"SELECT @id:=217 as idFac, total from Facs where id=@id"

Is it in the plugin MySQLCommunityServer?

The query I need involve the union of several subqueries where in each one the specified @id variable must be used so if I specify @id in each one I don’t have to pass dozens of parameters that are always the same.

I would expect that you could use

total from Facs where id = ( select num from variables)  
followed by  (stuff from other tables **where othertableid = facs.id**)

if you filter on one table, that table can be the key for all the others, can’t it?

Yes, it’s clear, some subqueries can run that way, other can not, thank you.

Main question is if is it possible tu use @variables with MySQLCommunityServer plugin. I can’t find this documented and I’m never quite sure if a correct query made in phpMyAdmin or TablePlus will work in Xojo.

I can’t get that to work, too.
Maybe Xojo internally interprets a command starting with SET... to be a SQL-Execute, and therefore doesn’t populate the rowset of the later Select. Dunno…

Anyway…

As of MySQL 3.23.6, you can assign a value returned by a SELECT statement to a variable, then refer to the variable later in your mysql session.

This seems to work: Execute the statements one after another (not multiple ones in a single call) in the same connection/session.

Var db As New MySQLCommunityServer
db.Host = "127.0.0.1"
db.UserName = "xxx"
db.Password = "yyy"
db.Port = 3306

Call db.Connect

db.ExecuteSQL("SET @x = 987;")
Var rs As RowSet = db.SelectSQL("SELECT @x as test;")

Var test As String = rs.Column("test").StringValue
Break
2 Likes

To me this SQL statement doesn’t make much sense. I don’t see why a variable is needed as it’s meant to be probably simply: SELECT id as idFac, total from Facs where id=217

And I honestly haven’t used variables much.

What I think is happening here: The database server will only “execute” the @id:=217 when filling the column value of a row. But it doesn’t come to that since the query (which most likely is interpreted first) is where id=@id - and at that time @id is not yet set.

This works:

Var db As New MySQLCommunityServer
db.Host = "127.0.0.1"
db.UserName = "xxx"
db.Password = "yyy"
db.Port = 3306
db.DatabaseName = "mysql"

db.ExecuteSQL("SET @host = 'localhost';")
Var rs As RowSet =  = db.SelectSQL("SELECT Host, User FROM user WHERE Host=@host")

Thanks Jurg, I’ll try this. Intersting to know I can use mysql variables like that, never used variables in different calls within the same session, it makes sense.

That SQL statement was the simplest example I could think of, the query I need to write using variables involves unions of subqueries to different tables and if I can write them referencing variables in the same db it makes it much easier for me to pass them to Xojo.

Thanks to all.