iOS Variables and Sqlite Queries Help!

[JoshAK]

I have a simple sqlite query. I have been able to query specific data or Static data, but I'm having trouble querying variables. I'm not new to SQL queries, but I am new to cocoa. Can someone give me a simple example of querying a variable? The following is my query:

const char *sql = "select id, name from field where name = 'variable' ";
sqlite3_stmt *selectStatement;

Thanks For the help

Okay maybe no one understands what I need. I'll try to explain this better. If I were to write an SQL query with a Variable it would look like this:

$sql ="SELECT Name FROM `TableName` WHERE id = '".$ID10."'";

$sql = SQL Variable
SELECT = What To Select
Name = Row Name In The Table
FROM = Where To Select From
'TableName' = Is The Name Of The Table
Where id = Where The id is
'".$ID10.'" = variable

The last variable is where I'm having trouble. Can anyone help?

 

[JefRH1]

hmmm

Are you talking about declaring a variable in SQL and using it in your statement?

MS SQL example:
Declare @name as varchar(15)

set name = 'Jeff'

Select * from table where name = @name

If so, I'm not sure yet how to do this in sqlite, but the work around would be to use code to pull the variable info and then us it in another query with the '?'.

Select * from table where name = ?

This is in the sqlite books example that apple provides.

 

[kainjow]

You can use NSString to build the SQL:

NSString *name = // something here
NSString *sqlStr = [NSString stringWithFormat:@"select id, name from field where name = '%@'", name];
const char *sql = [sqlStr UTF8String];

 

[caldwelljason]

binding variables

concatenating SQL strings together is usually a bad idea, especially is there's any user data in the query. It opens up the possibilty of a SQL injection attack and is also less performant, because you are compiling the query again each time you execute it, rather than just once.

I think the best approach in sqlite is to use binding. Basically, you put a '?' in your query where your variable goes, then bind those parameters in order.

For example, "select firstName, lastName from contacts where payGrade = ?"

Then you would use sqlite_bind_int() to supply the value for the '?'. (I am not near my computer; so I may not have the method name exactly correct. But it's something like that...) There is a binding method for each datatype you can bind (ints, strings, etc.)

Hope that gets you on the right track. I can only type so much on my phone, great as it is

 

[JoshAK]

concatenating SQL strings together is usually a bad idea, especially is there's any user data in the query. It opens up the possibilty of a SQL injection attack and is also less performant, because you are compiling the query again each time you execute it, rather than just once.

I think the best approach in sqlite is to use binding. Basically, you put a '?' in your query where your variable goes, then bind those parameters in order.

For example, "select firstName, lastName from contacts where payGrade = ?"

Then you would use sqlite_bind_int() to supply the value for the '?'. (I am not near my computer; so I may not have the method name exactly correct. But it's something like that...) There is a binding method for each datatype you can bind (ints, strings, etc.)

Hope that gets you on the right track. I can only type so much on my phone, great as it is

I have tried to approach my queries as you say. I'm still having trouble. Do you think you could give me a more detailed example the next time you are at your computer. I've look at the SQLiteBooks example but I'm still not sure where I'm going wrong. Thanks for the help!

 

[caldwelljason]

Sample

First, I declare this in the implementation of the class:

static sqlite3_stmt *delete_question_statement = nil;


Then, I do the following in my delete statement. We first prepare the statement. This way I only do this once, though I may use it repeatedly. Then we bind the parameters. In this case there is one. I could bind another parameter just by adding another ? to my statement and doing the binding for it where I am currently binding the ID. Remember that you must bind the parameters in the order that they appear in the SQL statement.

Then I execute the query by using sqlite3_step. This returns a success code that I will check later to see if I need to report an error to the user. Finally, I reset the statement. This doesn't close it. (I'll show you that in a second.) But it commits the changes to the database and releases some resources, preparing the statement to be used again.

if (sql_statement == nil)
{
static char *sql = "DELETE FROM myTable WHERE ID = ?";
if (sqlite3_prepare_v2(db, sql, -1, & sql_statement, NULL) != SQLITE_OK)
{
NSString *msg = [NSString stringWithCString:sqlite3_errmsg(db)];
NSLog(msg);
}
}

sqlite3_bind_int(sql_statement, 1, ID);

// Execute the query.
int success = sqlite3_step(sql_statement);

// Reset the query for the next use.
sqlite3_reset(sql_statement);

Finally, when my application closes, I need to remember to finalize the statement to make sure all loose ends are tied up and all resources are released:

if (sql_statement) sqlite3_finalize(sql_statement);

Hope that helps!

 

[JoshAK]

Thanks that awesome!