SQL OO v7 Query bug.

Well, I was making a currency system, and I ran into an issue where I couldn’t get data, but I could set data.

But everyones currency amount is 10 in the database.

[lua]
function Realm.Currency.Get( player )
local ret = 0
local cquery = RealmDatabase:query( “SELECT amount FROM currencies WHERE SteamID = “” … player:SteamID( ) … “”;” );
cquery.onError = print;
cquery.onSuccess = print;
cquery.onData = function( query, data )
ret = data;
end;
cquery:start( );
return ret;
end;
[/lua]

Help would be appreciated :v:

Shouldn’t you be using data.amount? A query should be returning an associative table.

It’s probably because cquery.onData gets called when the data is received, which may not be right after calling cquery.start.

I’ve tryed changing it to data.amount, but it’s still not working. Does anyone have a suggestions as to what I should do to get the value?

Try returning on data instead of setting it to a variable (following Nevec’s suggestion).

You can’t get it straight away if the sql connection is threaded. If the database cannot change outside of your script, you can query it only once when the player joins. Then update the value whenever you need locally and send it to the database.

I changed my function to
[lua]
function Realm.Currency.Get( player )
local ret = 0
local cquery = RealmDatabase:query( “SELECT amount FROM currencies WHERE SteamID = “” … player:SteamID( ) … “”;” );
cquery.onError = print;
cquery.onSuccess = print;
cquery.onData = function( query, data )
return data.amount
end;
cquery:start( );
end;
[/lua]
and it printed a blank line when I did my print test. :pseudo:

It appears to not be getting the data. The only reason it was printing 0 earlier was due to you setting ret, and now when you are directly returning the value, there is nothing to print.

Double check your database, and make sure you have the query correct.

Not even :V is printed. Something is very wrong here… however, my database is connected.

Oh, how could I miss this… Remove the semicolon in your query.

It still isn’t printing anything.

[lua]function Realm.Currency.Get( player, callback )
local ret = 0
local cquery = RealmDatabase:query( “SELECT amount FROM currencies WHERE SteamID = “” … player:SteamID( ) … “”;” );
cquery.onError = print;
cquery.onSuccess = print;
cquery.onData = function( query, data )
callback(data.amount)
end
cquery:start( );
end;[/lua]

instead of doing this:
[lua]RealmDatabase:query( “SELECT amount FROM currencies WHERE SteamID = “” … player:SteamID( ) … “”;” )[/lua]
You should do this:
[lua]RealmDatabase:query( “SELECT amount FROM currencies WHERE SteamID = " … sql.SQLStr(player:SteamID())…”;" )[/lua]

It won’t make much difference this time, but the first method is sensitive for injection. People won’t be able to inject with steam IDs unless they can change their steam ID’s to a custom string, but if you use the first method for other things it might be dangerous.

It’s still not working :smith: ( I did a test with print as the callback )

Thank you Falco.

If SteamID is supposed to be stored as a string, try this:

[lua]function Realm.Currency.Get( player, callback )

local ret = 0  
local cquery = RealmDatabase:query( "SELECT amount FROM currencies WHERE SteamID = '" .. player:SteamID( ) .. "';" );  
    cquery.onError = print;  
    cquery.onSuccess = print;  
    cquery.onData = function( query, data )  
        callback(data.amount)  
    end
cquery:start( );  

end; [/lua]

Also, make sure to escape the query if you have to.

The odd thing is, I can set currencies completely fine, I just can’t get them.

When are you trying to run this query? Immediately after the player has joined the game? If so, delay it (timer) a bit and see if it works then.

Also, make it print your query and SteamID; use the query on your database to see if thats where the problem lies.

I’m running this through RCon to test it, however, I plan on running it on PlayerInitialSpawn and setting Networked variables according to the players currency amount. I changed my code to:

[lua]
function Realm.Currency.Get( player, callback )
local ret = 0
local cquery = RealmDatabase:query( "SELECT cur_amount FROM fretta WHERE SteamID = " … sql.SQLStr( player:SteamID( ) ) );
cquery.onError = print;
cquery.onSuccess = print;
cquery.onData = function( query, data )
callback( data.amount )
end
cquery:start( );
end;
[/lua]
However, it still doesn’t want to work :v:

Don’t just escape the SteamID() part, escape the whole query. Changes are you need the quotes and that’s where it messed up.

[lua]
local myQuery = sql.SQLStr(“SELECT cur_amount FROM fretta WHERE SteamID = '” … player:SteamID( ) … “’” )
local cquery = RealmDatabase:query( myQuery );
[/lua]

Also, print myQuery to the console so you see any obvious errors.

Heres myQuery, printed.

It looks completely fine to me.