SQLite database SQL

audio2 · Post by **audio2** » Tue Sep 09, 2008 1:26 am

Hi
I have moved from VB6 to purebasic and am trying to write the contents of a editorgadget to a SQLite database the procedure I am using works , but when the editorgadget contains indented formated PB code (I am writing a code library) it does not insert into the database.
Also if I input the same code into the SQLite database externally the code saves in the database but is ignored when I load up in PB
I have been using PB for a week
I feel I am making a basic error so I hope someone can help.
Below is the procedure i am using to create the query to insert into the database...

Code: Select all

Procedure PMRecordInsert()
                           
                           
InsertCAT = "'" + GetGadgetText(#ComboBoxCATEGORY) + "',"
InsertDESC = "'" + GetGadgetText(#StringDESCRIPTION) + "',"
InsertCODE = "'" + GetGadgetText(#EditorCODE) + "'"
;Debug InsertCODE
                                                                                        
UseSQLiteDatabase()    
OpenDatabase(#Database, SQLiteDatabaseName$, User$, Password$)

PMinsertString = "insert into PBCodeData values(null," + InsertCAT + InsertDESC + InsertCODE + ")"
DatabaseUpdate(#Database, PMinsertString)
CloseDatabase(#Database)
ClearGadgetItemList(#ListIconSUMMARY) 
 ;Debug PMinsertString
PMLoadDB()

      EndProcedure

Advice greatly appreciated....

audio2

[edit by Rings, set codetags and a usefull topic]

citystate · Post by **citystate** » Tue Sep 09, 2008 1:32 am

perhaps if you replace your indentation code with a set number of spaces instead of the TAB character CHR(10) - PB uses the TAB to seperate columns for a few of its gadgets.

bear in mind, I may be talking out of my hat (as usual)

hope it helps

Rook Zimbabwe · Post by **Rook Zimbabwe** » Tue Sep 09, 2008 5:14 am

This is probably where the error lies!

PMinsertString = "insert into PBCodeData values(null," + InsertCAT + InsertDESC + InsertCODE + ")"

let me crack some code out of my POS program... Hang 1 sec

I use this to update my DBase you have to specify the Column Titles beforehand...

Code: Select all

INSERT INTO DAYTICKET ([CHECKNUMBER], [SERVERID], [TABLE], [ITEM], [COST], [COG],[TAXES],[ASTTYPE],[AST],[STATUS], [TIMER]) VALUES ('"+ticketnumber$+"', '"+empid$+"', '"+tablenumber$+"', '"+item$+"', '"+cost$+"', '"+cog$+"', '"+taxes$+"','"+asttype$+"','"+ast$+"','"+status$+"', '"+dater$+"');"; ***

OK that is a long line... but I write a great many things to the DBase... Note that each [] holds a column name INSIDE the parenthesis... ALSO note the ' " + variable$ + " ' portion!

Give it a shot. Write down your column names; mine happen to be in order just because I like it that way!, and go for it... Try adding 1 or two columns first... then you can get ambitious!

audio2 · Post by **audio2** » Tue Sep 09, 2008 11:51 am

Sorry for not being specific in the title of my post

I have tried the above suggestions and the result is the same as my original code, thanks for trying
Having spent hours working on this problem I am now getting nearer to a solution

the text that i am trying to write to the Sqlite database contains a ' character

For example the line of text below in a Editorgadget writes into the database fine

red = "" + blah(#BlahBlahBlah) + ","

This line of text does not

red = "'" + blah(#BlahBlahBlah) + "',"

notice the ' between the exclamation marks

So I believe the ' character in the line of text is bugging out the SQL query

Anyone else had this problem?

Great Forum

Thanks
Audio2

srod · Post by **srod** » Tue Sep 09, 2008 12:16 pm

Works fine here. I've embedded tab characters, line feeds... within a variable and then written to an SQLite database no problem.

Here's the code I just used :

Code: Select all

a$="Jones"
b$ = "Paula"
c$ = "'" + a$ + "', '" + b$ + "'"
cmd$ = "Insert Into customers Values ('001', " + c$ + ")"

The only thing I can think of is use of double single quotes! SQLite will treat a double instance of '' as a single quote to be written to the database - not as a string terminator. Kind of like an escape sequence.

audio2 · Post by **audio2** » Tue Sep 09, 2008 4:31 pm

Thanks srod

I tried adding a single quote to my single quote and it indeed inserts the text as required, when it loads the field it ignores the extra quotes and displays the original text without my additional quote,BUT if I edit the text I have to add the additional quotes again to save it .
either I am missing something or SQLite is not for me.
The upshot of this is you can't save text into a SQLite database if it contains the ' character, which would make it useless at saving a database full of SQLite queries.
that can't be right

Thanks again for your help.

Audio2

Post by **Kiffi** » Tue Sep 09, 2008 4:36 pm

if textvalues contains a ' character, you have to double them before
inserting into the database:

Code: Select all

MyTextValue = ReplaceString(MyTextValue, "'", "''")

(as well as in VB6)

Greetings ... Kiffi

audio2 · Post by **audio2** » Tue Sep 09, 2008 6:58 pm

Kiffi

You may well be getting my nomination for resident Forum Genius

Tried your suggestion and changed my procedure accordingly

Code: Select all

            Procedure PMRecordInsert()
                                                      
InsertCAT = GetGadgetText(#ComboBoxCATEGORY) 
InsertDESC = GetGadgetText(#StringDESCRIPTION) 
InsertCODE = GetGadgetText(#EditorCODE) 
                                                                                
UseSQLiteDatabase()    
OpenDatabase(#Database, SQLiteDatabaseName$, User$, Password$)

InsertCODE = ReplaceString(InsertCODE, "'", "''")

PMinsertString = "insert into PBCodeData values(null ,"+ "'"+InsertCAT +"','"+ InsertDESC+"','" + InsertCODE + "')"
Debug  PMinsertString
DatabaseUpdate(#Database, PMinsertString)
CloseDatabase(#Database)
ClearGadgetItemList(#ListIconSUMMARY) 
PMLoadDB()

      EndProcedure

Never had to to anything like this with VB6 all my queries where embedded in an access DB

Been stuck on this for 2 frustrating days, but I learnt a lot

Thanks from Liverpool England

Audio2

srod · Post by **srod** » Tue Sep 09, 2008 7:08 pm

But Access DB's don't use the double quote business!

So it was the quotes after all then! What a guess master srod! hehe.

You can't go wrong with SQLite; even if with Purebasic you have to think in a slightly different way than may have been the case when using VB.

audio2 · Post by **audio2** » Wed Sep 10, 2008 12:17 am

Thats true

I was getting frustrated that inputing a simple quote character into a field would screw up the SQL query string, but thinking about it, these are the anomolies that have to be overcome in order to create compact, efficient, fast apps with no dependencies.
Thats why I am trying this software as opposed to the Visual studio 6 software i normally use.
VB6 is still great software but Purebasic appears to have more controllable "latent" power.

thanks for your help srod

Audio2