Solved: Using field based data for SQLite Insert?

[bmcgonag]

I have my application successfully creating a storage folder and sqlite database, as well as the necessary table. I am now trying to enter data from the user into the table. I have a variable CNStorageLocation that holds a path selected by the user. I am checking to make sure the variable actually has the path and it does...but my INSERT statement must have something wrong with it, because the path is never actually inserted into my table.

Here's the code.

Code: Select all

# add the user entered storeage location from Settings into the main database
on addMainLocToDB
   put getDBaseID() into tDBaseID
   answer "Selected Storage Location for main DB is " & CNStorageLocation
   
   put "INSERT into main_store_path (main_path) VALUES (CNStorageLocation)" into tSQL
   
   revExecuteSQL tDBaseID, tSQL
end addMainLocToDB

I don't get any errors when I run this, and when I just put the SQL into a sqlite application (replacing the variable with a path) it successfully adds the path to the table.

Any help is greatly appreciated.

[SparkOut]

If you

Code: Select all

answer tSQL

before the revExecuteSQL line, what do you get? Do you see how the literal string "CNStorageLocation" is being used? You need to get the variable to be resolved by concatenating the string literal with the variable as in:

Code: Select all

put "INSERT into main_store_path (main_path) VALUES (" & CNStorageLocation & ")" into tSQL

if you answer tSQL again you should see the difference in the insert query.

You may also need to check the scope of the CNStorageLocation variable, to ensure it can be accessed from within your handler (declared as a script local or global)
HTH

[bmcgonag]

Thanks so much!

Yes, I had the CNStorageLocation variable set as Global, and it was coming back with the correct information in my handler. Thanks for pointing out that I was putting the variable in as a string literal. That was the issue.

I modified that line as you suggested, then found one other issue. It needed single quotes around the variable as well. Here's the final version of that line.

Code: Select all

put "INSERT into main_store_path (main_path) VALUES ('" & CNStorageLocation & "')" into tSQL

[Ultravibe]

Hi guys!
I have a global problem with LiveCode and SQLite working together:
1) I put the data that i entered in text field into database. This data contains russian charcters. But when i check same database in SQLite expert application, instead of russian characters i see some "??????" or something else, but not original text that i entered. Promblems with encoding text?
2) I put the data into database using SQLite Expert application. This data is also contains russian charcters. When i check in livecode application by database query there are also "strange" characters (like "?????" or smthg else)

How it can be fixed?
P.S. Russian characters (((((((

[bn]

Hi Ultravibe,

I am neither a SQL-expert nor an Unicode expert.

Have a look at this discussion. It looks like the problem is with Unicode and how to solve it.

http://runtime-revolution.278305.n4.nab ... 88182.html

Maybe one of the Unicode/SQL experts chimes in.

Kind regards
Bernd

[Ultravibe]

bn, thank you!
That post gives me exact info about how to fix the problem with encoding!!!!!!!