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"
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.
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
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.