SQL Functions doesn't work

Other
#1 
require "sqlite3" db = sqlite3.open( system.pathForFile( "database.sqlite", system.ResourceDirectory ) ) db:exec( "CREATE TABLE IF NOT EXISTS db( id INTEGER PRIMARY KEY, name String, audio STRING, score INT )" ) function newSQLData() if not( getSQLData( "user" ) ) then db:exec( "INSERT INTO db( name, audio, score ) VALUES( 'user', 'on', '0' )" ) end end function setSQLData( row, value ) if ( getSQLData( "user" ) ) then db:exec( "UPDATE db SET '"..row.."' ='"..value.."', WHERE name = 'user'" ) end end function getSQLData( row ) if ( getSQLData( "user" ) ) then local value = db:exec( "SELECT '"..row.."' FROM db WHERE name = 'user'" ) return value end end

This crap doesn’t work. I don’t know whats wrong…

It should check if someones already registeres and if not insert the data…

#2

File should be named *.db

Your syntax is a bit wrong because you are using SQL instead SQLite - there is no Int or String.

But main reason is function order! Your new and set function begins with call to get function which is unknown for them so the statements aren’t executed.

#3

thanks alot! so any solution?

#4

Eee… declare get before new and set? lol

#5

So far so good.
Now there is a problem…
If I call newSQLData() it prints both options to console and insert.
So it ignores the check if the user is already in database?
What is wrong… ?

EDIT:

If table isn’t present, it doesn’t create it at first time.
But if I restart the project, table gets created…
 

require "sqlite3" db = sqlite3.open( system.pathForFile( "database.sql", system.ResourceDirectory ) )    db:exec( "CREATE TABLE IF NOT EXISTS db( id INTEGER PRIMARY KEY, name String, audio STRING, score INT )" ) function setSQLData( row, value )        db:exec( "UPDATE db SET '"..row.."' ='"..value.."', WHERE name = 'user'" ) end function getSQLData( row )     local value = db:exec( "SELECT '"..row.."' FROM db WHERE name = 'user'" ) if ( value ) then     return value else     return false     end end function newSQLData() if ( getSQLData( "name" ) ~= "user" ) then     db:exec( "INSERT INTO db( name, audio, score ) VALUES( 'user', 'on', '0' )" )     print( "newSQLData() called INSERT" ) else     return false     end     print( "newSQLData() called FALSE" ) end
#6

So Forums, any advice?

#7

Does the SampleCode/Storage/SQLite sample app work for you?

#8

yes it does.

actually code:

-- database functions -- require "sqlite3" db = sqlite3.open( system.pathForFile( "database.sql", system.ResourceDirectory ) ) db:exec( "CREATE TABLE IF NOT EXISTS db( id INTEGER PRIMARY KEY, name String, audio STRING, score INT )" ) function setSQLData( data, value ) db:exec( "UPDATE db SET '"..row.."' ='"..value.."', WHERE name = 'user'" ) end function getSQLData( data ) for row in db:nrows( "SELECT \* FROM db WHERE name = 'user'" ) do if ( row.data ) then return row.data else return false end end end function newSQLData() if ( getSQLData( "name" ) ~= "user" ) then db:exec( "INSERT INTO db( name, audio, score ) VALUES( 'user', 'on', '0' )" ) print( "newSQLData() called INSERT" ) else return false end print( "newSQLData() called FALSE" ) end -- end --
#9

You need to do some basic debugging.  Are there errors in your console log? If you don’t know how to read it, see this tutorial:  http://coronalabs.com/blog/2013/07/09/tutorial-basic-debugging/

Put in some print statements.  Try to see what’s going on.  Study the differences in what you’re doing and what the sample app is doing and try to find differences.   Unfortunately, I’ve not used SQLite enough to know if what you’re doing is right or not.  But its hard to tell what’s going on because you’ve not posted enough code.  Based on what is there, you’re opening and creating the database then defining some functions that never get called.

Rob

#10

Thanks for response.

So no error is given to console.

However, database should start with function newSQLData().

It prints both options, so I think it’s up to the getSQLData( … ) function.

I don’t know what’s wrong with this function…

Greetings,

Bambenek

#11

I’d recommend putting in some prints and see what’s going on.  Print the values you are passing in.  Print out the whole query string.  If you’re on a Mac, you can run sqlite from the command line and try the queries to make sure sqlite isn’t choking on something.

Rob

#12

As Rob mentioned, you need to post more code so we can see how you’re calling your functions - or you’ll need to look at debugging the code more.

For example, what is actually being returned in your “row.data” object in getSQLData()? From the SQL structure, it looks like you’d actually be returning a lua table with three columns: user, audio, and score. Instead of returning row.data, why don’t you just return true and check for that instead?

#13

File should be named *.db

Your syntax is a bit wrong because you are using SQL instead SQLite - there is no Int or String.

But main reason is function order! Your new and set function begins with call to get function which is unknown for them so the statements aren’t executed.

#14

thanks alot! so any solution?

#15

Eee… declare get before new and set? lol

#16

So far so good.
Now there is a problem…
If I call newSQLData() it prints both options to console and insert.
So it ignores the check if the user is already in database?
What is wrong… ?

EDIT:

If table isn’t present, it doesn’t create it at first time.
But if I restart the project, table gets created…
 

require "sqlite3" db = sqlite3.open( system.pathForFile( "database.sql", system.ResourceDirectory ) )    db:exec( "CREATE TABLE IF NOT EXISTS db( id INTEGER PRIMARY KEY, name String, audio STRING, score INT )" ) function setSQLData( row, value )        db:exec( "UPDATE db SET '"..row.."' ='"..value.."', WHERE name = 'user'" ) end function getSQLData( row )     local value = db:exec( "SELECT '"..row.."' FROM db WHERE name = 'user'" ) if ( value ) then     return value else     return false     end end function newSQLData() if ( getSQLData( "name" ) ~= "user" ) then     db:exec( "INSERT INTO db( name, audio, score ) VALUES( 'user', 'on', '0' )" )     print( "newSQLData() called INSERT" ) else     return false     end     print( "newSQLData() called FALSE" ) end
#17

So Forums, any advice?

#18

Does the SampleCode/Storage/SQLite sample app work for you?

#19

yes it does.

actually code:

-- database functions -- require "sqlite3" db = sqlite3.open( system.pathForFile( "database.sql", system.ResourceDirectory ) ) db:exec( "CREATE TABLE IF NOT EXISTS db( id INTEGER PRIMARY KEY, name String, audio STRING, score INT )" ) function setSQLData( data, value ) db:exec( "UPDATE db SET '"..row.."' ='"..value.."', WHERE name = 'user'" ) end function getSQLData( data ) for row in db:nrows( "SELECT \* FROM db WHERE name = 'user'" ) do if ( row.data ) then return row.data else return false end end end function newSQLData() if ( getSQLData( "name" ) ~= "user" ) then db:exec( "INSERT INTO db( name, audio, score ) VALUES( 'user', 'on', '0' )" ) print( "newSQLData() called INSERT" ) else return false end print( "newSQLData() called FALSE" ) end -- end --
#20

You need to do some basic debugging.  Are there errors in your console log? If you don’t know how to read it, see this tutorial:  http://coronalabs.com/blog/2013/07/09/tutorial-basic-debugging/

Put in some print statements.  Try to see what’s going on.  Study the differences in what you’re doing and what the sample app is doing and try to find differences.   Unfortunately, I’ve not used SQLite enough to know if what you’re doing is right or not.  But its hard to tell what’s going on because you’ve not posted enough code.  Based on what is there, you’re opening and creating the database then defining some functions that never get called.

Rob