SQL Issue

Games
#1

Hi,

I created a table called horses using Lita, and put in a record etc. all fine

When I try to insert a record via corona, it’s not going in…

[lua]local testStr = “INSERT INTO horses VALUES (NULL, ‘Cowboys Revenge’, 0, 20, 16, 8, 20, 1, false, false, true ,true);”
print(testStr)
db:exec(testStr)

for row in db:nrows(“SELECT * FROM horses”) do
local text = row.horsesid…" "…row.name
print(text)
end[/lua]

Now the output I get is

INSERT INTO horses VALUES (NULL, 'Cowboys Revenge', 0, 20, 16, 8, 20, 1, false, false, true ,true); 10 Medley 11 Faraway Bouncer

So you can see that I can access the horses table, but it just contains the two already there, “Cowboys Revenge” has not been added

Now if I copy and paste the SQL into Lita, it adds the record no problem.
Any idea why I can add it fine with Lita, but the same SQL statement doesn’t add from Corona?

Thanks [import]uid: 38562 topic_id: 11964 reply_id: 311964[/import]

#2

I don’t see any database module in the API. What are you using?

If the SQL stmt is correct, it’s likely a connection problem. Your program is not finding and/or logging in successfully to the DB.

[import]uid: 58455 topic_id: 11964 reply_id: 43628[/import]

#3

You can use SQLite, there example code in the sample code folder under storage

I didn’t put the code in the example above where I connect to the db.
But surely the fact that the SELECT SQL that I run just after my INSERT attempt returns the result (without my new row) means that I am connected and the INSERT statement should work?

Really not sure whats happening, as I am connected or else the SELECT would not work
And the INSERT statment works if I run it in Lita, so it’s syntax is ok. [import]uid: 38562 topic_id: 11964 reply_id: 43630[/import]

#4

Is your code connecting to the the DB with the same credentials you’re using in Lita? Some DB users only have read permissions, others read/write.

Also, are any errors thrown/returned on the insert? [import]uid: 58455 topic_id: 11964 reply_id: 43634[/import]

#5

It may be because you are not specifying the columns you wish to Insert into in your Insert statement, e.g.

Insert Into horses (Column1, Column2, Column3) Select Null, 'Cowboy', 'Another Value' [import]uid: 26769 topic_id: 11964 reply_id: 43661[/import]

#6

I have uploaded a zip file containing the files… would be grateful if anyone could have a look.

edit: link removed

Thanks [import]uid: 38562 topic_id: 11964 reply_id: 43763[/import]

#7

whoops, realised I was passing in the boolean values as true / false

chnaged this to a 1 or a 0 and it works fine.

Thanks to all who had a look [import]uid: 38562 topic_id: 11964 reply_id: 43884[/import]