Page 1 of 1
SQLite database SQL
Posted: Tue Sep 09, 2008 1:26 am
by audio2
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]
Posted: Tue Sep 09, 2008 1:32 am
by citystate
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
Posted: Tue Sep 09, 2008 5:14 am
by Rook Zimbabwe
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!
Posted: Tue Sep 09, 2008 11:51 am
by audio2
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
Posted: Tue Sep 09, 2008 12:16 pm
by srod
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.
Posted: Tue Sep 09, 2008 4:31 pm
by audio2
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
Posted: Tue Sep 09, 2008 4:36 pm
by Kiffi
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
Posted: Tue Sep 09, 2008 6:58 pm
by audio2
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
Posted: Tue Sep 09, 2008 7:08 pm
by srod
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.
Posted: Wed Sep 10, 2008 12:17 am
by audio2
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