How to use Variables in MySQL queries?

[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

[Karol]

Oh yeah!
This issue slipped in in Ventuz 3.7.0.
Will be fixed for upcoming Ventuz 4.4.0.

Cheers
Karol

[schub]

Ok, good to know :D
Thanks

Greets,
schub

[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

[schub]

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

[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