SQL Lite: assign variable to column

Games
#1

I am using SQL Lite… and all seems to work well in my app to a point. I am updating the table of my database, and as long as I assign an actual number or string to the ‘columns’ (i.e… data fields), it works fine. But, I have a variable that gets changed during my app, and when the app gets ready to close, this variable is what needs to get saved to the database.

here is my sql code for updating the table:

[[UPDATE gameData SET playerName = ‘Frank’, credits = totalCredits WHERE id=1 ;]]
‘totalCredits’ is a variable used throughout my app, and assigning it to the database column ‘credits’ causes the update to fail. The update just doesn’t occur, and my simulator/terminal posts no errors. However, if I assign a hard-value to that column (credits), the update occurs as it should.

How does one update the sql table using variables??? Is a cast required, or some sql symbol needed to preceed a variable? Did some searching on line for sql tutorials, but never saw this explained.

Thanks [import]uid: 148857 topic_id: 30907 reply_id: 330907[/import]

#2

Try
[lua]“UPDATE gameData SET playerName = ‘Frank’, credits = “…totalCredits…” WHERE id=1 ;”[/lua]

Not sure what’s with the double bracket thing people use and I am too lazy to research it. The above is closer to what I use with c#/python/everything else(without getting into parameters, anyway). Also, I’m assuming that totalCredits would be an int value so you don’t need single quotes around it.

[import]uid: 147305 topic_id: 30907 reply_id: 123605[/import]

#3

@burdershank

Thanks! I tried it with the double quotes and without… with the double quotes saves the variable name and the dots as a string … without the quotes the update fails to take effect. Your input made sense, and was worth trying, but it didn’t work.

Thanks for you input!
[import]uid: 148857 topic_id: 30907 reply_id: 123608[/import]

#4

If the above sql statement doesn’t work then you have something else going on that is making the sql statement fail. This isn’t uncharted territory you are going into… pretty much every app that uses a database has to use runtime variables in it’s queries.

Typically what I do when troubleshooting my sql statements with Corona is i print the statement to console so I get the exact command being sent to the database. Once I have that I open my DB in a sqlite database browser and try running the command. You can generally figure out the problem from there. [import]uid: 147305 topic_id: 30907 reply_id: 123610[/import]

#5

@budershank

I will try your suggestion.

Thanks [import]uid: 148857 topic_id: 30907 reply_id: 123613[/import]

#6

@budershank , or anyone who may know

I have tried your suggestions, as well as searched internet for hours for any sample of assigning a variable to a sql table field, doing an update. As much as there is out there on sql tutorials and as common this action would be I cannot see any examples or explanation. EVERY example and tutorial on sql lite for UPDATE is the same … either a literal string or a literal numeric is assigned. How can I assign a ‘variable’ to a sql record field???, There should be a example of this on Corona, yet the only examples shown for UPDATE are assigning literal strings or numerics.

Anyone?
Thanks for any help possible. [import]uid: 148857 topic_id: 30907 reply_id: 123833[/import]

#7

Try
[lua]“UPDATE gameData SET playerName = ‘Frank’, credits = “…totalCredits…” WHERE id=1 ;”[/lua]

Not sure what’s with the double bracket thing people use and I am too lazy to research it. The above is closer to what I use with c#/python/everything else(without getting into parameters, anyway). Also, I’m assuming that totalCredits would be an int value so you don’t need single quotes around it.

[import]uid: 147305 topic_id: 30907 reply_id: 123605[/import]

#8

@burdershank

Thanks! I tried it with the double quotes and without… with the double quotes saves the variable name and the dots as a string … without the quotes the update fails to take effect. Your input made sense, and was worth trying, but it didn’t work.

Thanks for you input!
[import]uid: 148857 topic_id: 30907 reply_id: 123608[/import]

#9

If the above sql statement doesn’t work then you have something else going on that is making the sql statement fail. This isn’t uncharted territory you are going into… pretty much every app that uses a database has to use runtime variables in it’s queries.

Typically what I do when troubleshooting my sql statements with Corona is i print the statement to console so I get the exact command being sent to the database. Once I have that I open my DB in a sqlite database browser and try running the command. You can generally figure out the problem from there. [import]uid: 147305 topic_id: 30907 reply_id: 123610[/import]

#10

@budershank

I will try your suggestion.

Thanks [import]uid: 148857 topic_id: 30907 reply_id: 123613[/import]

#11

@budershank , or anyone who may know

I have tried your suggestions, as well as searched internet for hours for any sample of assigning a variable to a sql table field, doing an update. As much as there is out there on sql tutorials and as common this action would be I cannot see any examples or explanation. EVERY example and tutorial on sql lite for UPDATE is the same … either a literal string or a literal numeric is assigned. How can I assign a ‘variable’ to a sql record field???, There should be a example of this on Corona, yet the only examples shown for UPDATE are assigning literal strings or numerics.

Anyone?
Thanks for any help possible. [import]uid: 148857 topic_id: 30907 reply_id: 123833[/import]

#12

Did you solve it? I have same issue.

#13

Did you solve it? I have same issue.