SQLite problems

[rockleg]

im having a problem with SQLite, i'm connecting to the database but the table is not created

local db = dbConnect('sqlite', 'accounts.db') 
dbQuery(db, 'CREATE TABLE IF NOT EXISTS accounts') 

[JR10]

You haven't specified any columns for the table. The basic syntax is this:

CREATE TABLE IF NOT EXISTS accounts (column1 TEXT, column2 INTEGER) 

Also, don't use dbQuery when you don't need to return anything.

[rockleg]

like this?

dbExec(db, 'CREATE TABLE IF NOT EXISTS accounts (account TEXT)') 

[JR10]

Yes. Use /debugscript 3 to check for any errors/warnings in your scripts.

You can also use /debugdb 2 to set the logging level which could help you identify database problems in the future.

[rockleg]

whats the difference between dbQuery and dbExec?

[JR10]

dbExec returns a boolean value, mostly true unless the connection is incorrect.

dbQuery returns a query handle which can be polled using dbPoll to get the returned data. It's mostly used with SELECT.

[rockleg]

are there any SQLite tutorials that i should read?

[JR10]

I made one some time ago: viewtopic.php?f=148&t=38203

[rockleg]

how can i use dbPoll

[JR10]

You can check the wiki page of any function for the syntax and a basic example.

local qh = dbQuery('SELECT * FROM accounts') 
local results = dbPoll(qh, -1) 

-1 means that it will wait for the database to return the results. You can also use callbacks which is better because it doesn't freeze the server till the data is returned.

[rockleg]

am i using callbacks right?

local qh = dbQuery(callback, db, 'SELECT * FROM accounts') 
  
function callback(qh) 
 local results = dbPoll(qh, -1) 
end 

[JR10]

You don't need to store the query handle returned from dbQuery since you're using callbacks. You don't need to use -1 as the timeout since the data will surely be ready, you can just use 0.

[rockleg]

is there a way to return the results without callbacks and without freezing the server?

[JR10]

You mean from a function? One way to do that would be coroutines.

viewtopic.php?f=91&t=86184&p=779671&hilit=coroutine#p779671

viewtopic.php?f=91&t=92076&p=828272&hilit=coroutine#p828272