Page 1 of 1
How to use Variables in MySQL queries?
Posted: 21 May 2014, 12:34
by schub
Hi,
Im experimenting with databases in Ventuz, most of the stuff I tried works fine, but theres one major problem:
I cant use variables in a query.
In the example scene you guys created a variable "@Clubs" and used that in the query.
When i try to add another variable with a @-prefix i get this error
Without @-prefix the variable is just ignored.
Is this a bug in the Script Editor to not allow @ for SQL or am I doing something wrong?
Greets
Schub
Re: How to use Variables in MySQL queries?
Posted: 22 May 2014, 12:34
by Karol
Oh yeah!
This issue slipped in in Ventuz 3.7.0.
Will be fixed for upcoming Ventuz 4.4.0.
Cheers
Karol
Re: How to use Variables in MySQL queries?
Posted: 22 May 2014, 17:04
by schub
Ok, good to know :D
Thanks
Greets,
schub
Re: How to use Variables in MySQL queries?
Posted: 23 May 2014, 09:34
by Götz_B
In the meantime just use variables without a leading @.
Should work if you choose a name that's not used in the database table already.
Cheers,
Götz
Re: How to use Variables in MySQL queries?
Posted: 26 May 2014, 14:32
by schub
Götz_B wrote:In the meantime just use variables without a leading @.
Should work if you choose a name that's not used in the database table already.
Sadly, it does not work for me :(
I created a variable "x" as Integer in the inputs of the query node, then when I use the following query, the error event flashes and no data is written to the database
Code: Select all
UPDATE testdb.debug SET testvalue = x WHERE debug.id = 1;
If I include the quotation marks, like phpMyAdmin uses in its querys, data is written, but instead of the value of "x" it's always changed to "0"
Code: Select all
UPDATE testdb.debug SET testvalue = 'x' WHERE debug.id = 1;
When the value is hardcoded like this, it works fine
Code: Select all
UPDATE testdb.debug SET testvalue = 1234 WHERE debug.id = 1;
At the moment my workaround is based on the "convert to text" node, which lets me edit the query string without opening the editor.
But I dont know how efficient this is, and I'm bound to the numeric variables provided by "convert to text"
Greets,
schub
Re: How to use Variables in MySQL queries?
Posted: 13 Jun 2014, 15:08
by schub
Sorry I have to bring this up again, but the problem persists.
After the 4.04 update its possible to create the @variables, but they will not be used in the query.
The entries will be changed to '0' or I get an error message, depending if I used the 'UPDATE' command or 'INSERT INTO'
The only way to use an @variable is to 'SET' it first in the query like this:
Code: Select all
SET @variable=1234;
UPDATE testdb.debug SET testvalue = @variable WHERE id = 1;
This proves that variables are supported by the MySQL server, but again, if the variable values need to be hardcoded into the query they're useless for Ventuz integration.
Can someone post an example how to use Custom Model
Input with a MySQL query?
Or is this not intented at all, and we're only supposed to use the Custom Model
Output of the query node?
Greets
Schub