Problem saving data to SQLite Database

Just starting out? Need help? Post your questions and find answers here.
User avatar
Columbo
Enthusiast
Enthusiast
Posts: 303
Joined: Wed Sep 10, 2014 7:17 am
Location: Ontario Canada
Contact:

Problem saving data to SQLite Database

Post by Columbo »

I have put together a small app to allow me to enter data into a database but for some reason I am having a problem getting data inserted into a SQLite database. I have a database called ‘manual.db’ and it has a table called ‘command’ which contains 4 columns as follows:

record – Integer Auto Increment
category CHAR (2)
cmd CHAR (50)
syntax CHAR (2000)

For the query I have tried,

Code: Select all

result = DatabaseUpdate(#dbaseID, "INSERT INTO command (category, cmd, syntax) VALUES ('"+ dbcat + "','"+ dbcmd +"', '"+ dbsyntax + "')")
And I have also tried,

Code: Select all

result = DatabaseUpdate(#dbaseID, "INSERT INTO command (category, cmd, syntax) VALUES (?, ?, ?);")
I don’t see anything wrong with the database query but I am obviously missing something but I don’t know what it is.

Here is my code:

Code: Select all

;-------------------------------------------------------
;  Add Data to PB Manual Database
;-------------------------------------------------------

UseSQLiteDatabase()

;------------[ Enumeration ]------------
Enumeration
  #mainWindow
  #dbaseID
  ;----[ Buttons ]----
  #submitBT
  ;----[ Header & Footer ]----
  #header
  #footer
  ;----[ Fonts ]----
  #headerFont
  #footerFont
  ;----[ Labels ]----
  #categoryLBL
  #commandLBL
  #syntaxLBL
  ;----[ Gadgets ]---- 
  #categoryFLD
  #commandFLD
  #syntaxFLD
EndEnumeration

;--------[ Global Variables ]---------
Global dbcat.s
Global dbcmd.s
Global dbsyntax.s

;--------[ Load Fonts ]---------
  LoadFont(#headerFont, "Arial", 10)
  LoadFont(#footerFont, "Arial", 9)

 Procedure  setupMainScreen()
   ;------------[ Header ]------------
  TextGadget(#header, 0, 0, 780, 40, header$, #SS_CENTERIMAGE)
  SetGadgetFont(#header, FontID(#headerFont))
  SetGadgetColor(#header, #PB_Gadget_BackColor, RGB(4,43,97))
  SetGadgetColor(#header, #PB_Gadget_FrontColor, RGB(124,173,221))
  
  ;------------[ Footer ]------------
  TextGadget(#footer, 0, 478, 780, 20, footer$, #SS_CENTERIMAGE)
  SetGadgetFont(#footer, FontID(#footerFont))
  SetGadgetColor(#footer, #PB_Gadget_BackColor, RGB(4,43,97))
  SetGadgetColor(#footer, #PB_Gadget_FrontColor, RGB(124,173,221))
  TextGadget(#categoryLBL, 205, 100, 55, 20, "Category: ")
  SetGadgetColor(#categoryLBL, #PB_Gadget_BackColor, RGB(245,245,245))
  TextGadget(#commandLBL, 200, 160, 55, 20, "Command: ")
  SetGadgetColor(#commandLBL, #PB_Gadget_BackColor, RGB(245,245,245))
  TextGadget(#syntaxLBL, 200, 220, 55, 20, "Syntax: ")
  SetGadgetColor(#syntaxLBL, #PB_Gadget_BackColor, RGB(245,245,245))
  StringGadget(#categoryFLD, 260, 97, 100, 20, "", #PB_Text_Border)
  SetGadgetColor(#categoryFLD, #PB_Gadget_BackColor, RGB(235,235,235))
  StringGadget(#commandFLD, 260, 158, 250, 20, "", #PB_Text_Border)
  SetGadgetColor(#commandFLD, #PB_Gadget_BackColor, RGB(235,235,235))
  EditorGadget(#syntaxFLD, 260, 217, 450, 200, #PB_Editor_WordWrap)
  SetGadgetColor(#syntaxFLD, #PB_Gadget_BackColor, RGB(235,235,235))
  ButtonGadget(#submitBT, 260, 430, 110, 20, "Submit")
  SetActiveGadget(#categoryFLD)
EndProcedure

Procedure saveData()
  dbcat = GetGadgetText(#categoryFLD)
  dbcmd = GetGadgetText(#commandFLD)
  dbsyntax = GetGadgetText(#syntaxFLD)
    
   result = DatabaseUpdate(#dbaseID, "INSERT INTO command (category, cmd, syntax) VALUES ('"+ dbcat + "','"+ dbcmd +"', '"+ dbsyntax + "')")
  ;result = DatabaseUpdate(#dbaseID, "INSERT INTO command (category, cmd, syntax) VALUES (?, ?, ?);")
  If result = 0
    MessageRequester("Error","Could not save data!")
  EndIf
  FinishDatabaseQuery(#dbaseID)       ;Disconnects from database but does not close it.
EndProcedure

Procedure clearGadgets()
  SetGadgetText(#categoryFLD, "")
  SetGadgetText(#commandFLD, "")
  SetGadgetText(#syntaxFLD, "")
EndProcedure

;-------------------[ Open the database & Main Window ]--------------------
  If OpenDatabase(#dbaseID, "manual.db", "", "")          ;Open the database    
   wFlags = #PB_Window_MinimizeGadget | #PB_Window_MaximizeGadget | #PB_Window_SizeGadget |#PB_Window_ScreenCentered ;Window flags
   
  OpenWindow(#mainWindow, 0, 0, 780, 498, "", wFlags)
  SetWindowColor(#mainWindow, RGB(245,245,245))
  
  setupMainScreen()
  
    ;--------[ Wait for window event ]--------
  Repeat
    Select WaitWindowEvent()      ;Wait for an event
        
      Case #PB_Event_CloseWindow  ;Close window if "X" is clicked.
        run = 1                   ;Set run flag to 1
        
      Case #PB_Event_Menu       
        Select EventMenu() 
              
        EndSelect  
        
      Case #PB_Event_Gadget      ;Check for gadget event
        Select EventGadget()
            
      ;--------[ Buttons ]--------      
          Case #submitBT
            saveData()
            clearGadgets()
            
        EndSelect
     EndSelect
  Until run = 1    ;If run = 1 close the program
  CloseDatabase(#dbaseID)    ;close the database.
  EndIf
                             
Perhaps a new set of eyes can see what the problem is.
http://www.oldtimeradiotoday.com - Listen to or download classic old time radio broadcasts.
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: Problem saving data to SQLite Database

Post by infratec »

Hm ...
this works:

Code: Select all

EnableExplicit

;-------------------------------------------------------
;  Add Data to PB Manual Database
;-------------------------------------------------------

UseSQLiteDatabase()

;------------[ Enumeration ]------------
Enumeration
  #mainWindow
  #dbaseID
  ;----[ Buttons ]----
  #submitBT
  ;----[ Header & Footer ]----
  #header
  #footer
  ;----[ Fonts ]----
  #headerFont
  #footerFont
  ;----[ Labels ]----
  #categoryLBL
  #commandLBL
  #syntaxLBL
  ;----[ Gadgets ]----
  #categoryFLD
  #commandFLD
  #syntaxFLD
EndEnumeration

;--------[ Global Variables ]---------
Global dbcat.s
Global dbcmd.s
Global dbsyntax.s

;--------[ Load Fonts ]---------
LoadFont(#headerFont, "Arial", 10)
LoadFont(#footerFont, "Arial", 9)

Procedure  setupMainScreen(header$, footer$)
  ;------------[ Header ]------------
  TextGadget(#header, 0, 0, 780, 40, header$, #SS_CENTERIMAGE)
  SetGadgetFont(#header, FontID(#headerFont))
  SetGadgetColor(#header, #PB_Gadget_BackColor, RGB(4,43,97))
  SetGadgetColor(#header, #PB_Gadget_FrontColor, RGB(124,173,221))
  
  ;------------[ Footer ]------------
  TextGadget(#footer, 0, 478, 780, 20, footer$, #SS_CENTERIMAGE)
  SetGadgetFont(#footer, FontID(#footerFont))
  SetGadgetColor(#footer, #PB_Gadget_BackColor, RGB(4,43,97))
  SetGadgetColor(#footer, #PB_Gadget_FrontColor, RGB(124,173,221))
  TextGadget(#categoryLBL, 205, 100, 55, 20, "Category: ")
  SetGadgetColor(#categoryLBL, #PB_Gadget_BackColor, RGB(245,245,245))
  TextGadget(#commandLBL, 200, 160, 55, 20, "Command: ")
  SetGadgetColor(#commandLBL, #PB_Gadget_BackColor, RGB(245,245,245))
  TextGadget(#syntaxLBL, 200, 220, 55, 20, "Syntax: ")
  SetGadgetColor(#syntaxLBL, #PB_Gadget_BackColor, RGB(245,245,245))
  StringGadget(#categoryFLD, 260, 97, 100, 20, "", #PB_Text_Border)
  SetGadgetColor(#categoryFLD, #PB_Gadget_BackColor, RGB(235,235,235))
  StringGadget(#commandFLD, 260, 158, 250, 20, "", #PB_Text_Border)
  SetGadgetColor(#commandFLD, #PB_Gadget_BackColor, RGB(235,235,235))
  EditorGadget(#syntaxFLD, 260, 217, 450, 200, #PB_Editor_WordWrap)
  SetGadgetColor(#syntaxFLD, #PB_Gadget_BackColor, RGB(235,235,235))
  ButtonGadget(#submitBT, 260, 430, 110, 20, "Submit")
  SetActiveGadget(#categoryFLD)
EndProcedure

Procedure saveData()
  
  Protected result.i
  
  dbcat = GetGadgetText(#categoryFLD)
  dbcmd = GetGadgetText(#commandFLD)
  dbsyntax = GetGadgetText(#syntaxFLD)
  
  Debug "INSERT INTO command (category, cmd, syntax) VALUES ('"+ dbcat + "','"+ dbcmd +"', '"+ dbsyntax + "')"
  
  result = DatabaseUpdate(#dbaseID, "INSERT INTO command (category, cmd, syntax) VALUES ('"+ dbcat + "','"+ dbcmd +"', '"+ dbsyntax + "')")
  ;SetDatabaseString(#dbaseID, 0, dbcat)
  ;SetDatabaseString(#dbaseID, 1, dbcmd)
  ;SetDatabaseString(#dbaseID, 2, dbsyntax)
  ;result = DatabaseUpdate(#dbaseID, "INSERT INTO command (category, cmd, syntax) VALUES (?, ?, ?);")
  If result = 0
    MessageRequester("Error","Could not save data!" + #LF$ + DatabaseError())
  EndIf
  ;FinishDatabaseQuery(#dbaseID)       ;Disconnects from database but does not close it.
EndProcedure

Procedure clearGadgets()
  SetGadgetText(#categoryFLD, "")
  SetGadgetText(#commandFLD, "")
  SetGadgetText(#syntaxFLD, "")
EndProcedure


Define wFlags.i, run.i

;-------------------[ Open the database & Main Window ]--------------------
If OpenDatabase(#dbaseID, ":memory:", "", "")          ;Open the database
  
  If DatabaseUpdate(#dbaseID, "CREATE TABLE command (record INTEGER PRIMARY KEY AUTOINCREMENT, category CHAR(2), cmd CHAR(50), syntax CHAR(2000))") = 0
    Debug DatabaseError()
  EndIf
  
  wFlags = #PB_Window_MinimizeGadget | #PB_Window_MaximizeGadget | #PB_Window_SizeGadget |#PB_Window_ScreenCentered ;Window flags
  
  OpenWindow(#mainWindow, 0, 0, 780, 498, "", wFlags)
  SetWindowColor(#mainWindow, RGB(245,245,245))
  
  setupMainScreen("", "")
  
  ;--------[ Wait for window event ]--------
  Repeat
    Select WaitWindowEvent()      ;Wait for an event
        
      Case #PB_Event_CloseWindow  ;Close window if "X" is clicked.
        run = 1                   ;Set run flag to 1
        
      Case #PB_Event_Menu       
        Select EventMenu()
            
        EndSelect 
        
      Case #PB_Event_Gadget      ;Check for gadget event
        Select EventGadget()
            
            ;--------[ Buttons ]--------     
          Case #submitBT
            saveData()
            clearGadgets()
            
        EndSelect
    EndSelect
  Until run = 1    ;If run = 1 close the program
  CloseDatabase(#dbaseID)    ;close the database.
EndIf
I don't know how often I already said: use EnableExplicit

And why should FinishDatabaseQuery() fits DatabaseUpdate()?
And a message without a reason makes no sense too.
User avatar
Columbo
Enthusiast
Enthusiast
Posts: 303
Joined: Wed Sep 10, 2014 7:17 am
Location: Ontario Canada
Contact:

Re: Problem saving data to SQLite Database

Post by Columbo »

I tried your code and I still get an error. Here is the error.

Code: Select all

Could not save data!
near 's'; syntax error.
---------------------------------------------------------------------------------------
INSERT INTO command (category, cmd, syntax) VALUES ('A','AbortHTTP', 'AbortHTTP()

Syntax

    AbortHTTP(HttpConnection)

Description

    Aborts the progress of the specified asynchronous download, started either with ReceiveHTTPFile() or ReceiveHTTPMemory(). It's also usable for HTTPRequest() and HTTPRequestMemory() (if #PB_HTTP_Asynchronous was used). 

Parameters

    HttpConnection 	The HTTP connection to abort.

Return value

    None. 

Remarks

    The value #PB_HTTP_Aborted will be returned by HTTPProgress(). FinishHTTP() has to be called once the download has been aborted. 

See Also

    HTTPProgress(), FinishHTTP() 

Supported OS

    All')

I added EnableExplicit to my code and I got no error message however, it inserted a blank record into the database. It didn't save the data into the table columns except for record which is Auto Incremented.
http://www.oldtimeradiotoday.com - Listen to or download classic old time radio broadcasts.
Bitblazer
Enthusiast
Enthusiast
Posts: 733
Joined: Mon Apr 10, 2017 6:17 pm
Location: Germany
Contact:

Re: Problem saving data to SQLite Database

Post by Bitblazer »

Columbo wrote:Other than using EnableExplicit, I would like to find out what is causing my code to not save to the database?
I haven't looked further after seeing this

Code: Select all

  dbsyntax = GetGadgetText(#syntaxFLD)
   
   result = DatabaseUpdate(#dbaseID, "INSERT INTO command (category, cmd, syntax) VALUES ('"+ dbcat + "','"+ dbcmd +"', '"+ dbsyntax + "')")
You need to parse and encapsulate all parameters from gadgets before passing them into any kind of API call like that. Otherwise you open your application up to all kind of SQL injection exploits or errors. Consider somebody entering ' into the gadget field.

After parsing and encapsulating, i usually evaluate if the result makes sense. Something like an empty parameter might be a problem you need to deal with :)
webpage - discord chat links -> purebasic GPT4All
User avatar
Columbo
Enthusiast
Enthusiast
Posts: 303
Joined: Wed Sep 10, 2014 7:17 am
Location: Ontario Canada
Contact:

Re: Problem saving data to SQLite Database

Post by Columbo »

@Bitblazer

The database columns are record, category, cmd and syntax. In the database query I did not include the 'record' column because that is auto incremented.
http://www.oldtimeradiotoday.com - Listen to or download classic old time radio broadcasts.
Bitblazer
Enthusiast
Enthusiast
Posts: 733
Joined: Mon Apr 10, 2017 6:17 pm
Location: Germany
Contact:

Re: Problem saving data to SQLite Database

Post by Bitblazer »

Columbo wrote:@Bitblazer

The database columns are record, category, cmd and syntax. In the database query I did not include the 'record' column because that is auto incremented.
My point was - what would happen if somebody entered "'; drop table xyz;'" into your gadget. But it probably doesn't have to do with your original problem.
webpage - discord chat links -> purebasic GPT4All
User avatar
TI-994A
Addict
Addict
Posts: 2512
Joined: Sat Feb 19, 2011 3:47 am
Location: Singapore
Contact:

Re: Problem saving data to SQLite Database

Post by TI-994A »

Columbo wrote:...having a problem getting data inserted into a SQLite database....
Hi John. Please try this:

Code: Select all

Procedure saveData()
  
  dbcat = GetGadgetText(#categoryFLD)
  dbcmd = GetGadgetText(#commandFLD)
  dbsyntax = GetGadgetText(#syntaxFLD)
  
  SetDatabaseString(#dbaseID, 0, dbcat)
  SetDatabaseString(#dbaseID, 1, dbcmd)
  SetDatabaseString(#dbaseID, 2, dbsyntax)
  
  result = DatabaseUpdate(#dbaseID, "INSERT INTO command " +
                                    "(category, cmd, syntax) " +
                                    "VALUES (?, ?, ?);")  
  
  If result  ; check that it's been inserted
    
    result = DatabaseQuery(#dbaseID, "SELECT * FROM command")
    
    If result
      
      ClearDebugOutput()
      
      While NextDatabaseRow(#dbaseID)
        Debug GetDatabaseString(#dbaseID, 0)      
        Debug GetDatabaseString(#dbaseID, 1)      
        Debug GetDatabaseString(#dbaseID, 2)      
        Debug GetDatabaseString(#dbaseID, 3)      
      Wend
      
    EndIf
    
  Else
    
    MessageRequester("Error", "Could not save data!")
    
  EndIf
  
EndProcedure
Texas Instruments TI-99/4A Home Computer: the first home computer with a 16bit processor, crammed into an 8bit architecture. Great hardware - Poor design - Wonderful BASIC engine. And it could talk too! Please visit my YouTube Channel :D
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: Problem saving data to SQLite Database

Post by infratec »

Just tested my code again: it works.

You may run into an other problem:
If you have a ' in your data, you have to escape it with 2 single ' like:

Code: Select all

dbsyntax = ReplaceString(dbsyntax, "'", "''")
User avatar
TI-994A
Addict
Addict
Posts: 2512
Joined: Sat Feb 19, 2011 3:47 am
Location: Singapore
Contact:

Re: Problem saving data to SQLite Database

Post by TI-994A »

infratec wrote:If you have a ' in your data, you have to escape it...
Precisely. The database binding functions handle these scenarios quite efficiently. In addition to the inherent security aspects, of course.
Texas Instruments TI-99/4A Home Computer: the first home computer with a 16bit processor, crammed into an 8bit architecture. Great hardware - Poor design - Wonderful BASIC engine. And it could talk too! Please visit my YouTube Channel :D
User avatar
Fangbeast
PureBasic Protozoa
PureBasic Protozoa
Posts: 4747
Joined: Fri Apr 25, 2003 3:08 pm
Location: Not Sydney!!! (Bad water, no goats)

Re: Problem saving data to SQLite Database

Post by Fangbeast »

infratec wrote:Just tested my code again: it works.

You may run into an other problem:
If you have a ' in your data, you have to escape it with 2 single ' like:

Code: Select all

dbsyntax = ReplaceString(dbsyntax, "'", "''")
If he's not going to use the variable names in this procedure, he may as well just shorten the procedure a bit as below. And also, SetDatabaseString takes care of the sinqle quote problem, I enter a lot of data in my programs and it works fine. I think Spikey taught me that one.

Code: Select all

Procedure saveData()
  SetDatabaseString(#dbaseID, 0, GetGadgetText(#categoryFLD))
  SetDatabaseString(#dbaseID, 1, GetGadgetText(#commandFLD))
  SetDatabaseString(#dbaseID, 3, GetGadgetText(#syntaxFLD))
 
  result = DatabaseUpdate(#dbaseID, "INSERT INTO command "     +
                                    "(category, cmd, syntax) " +
                                    "VALUES (?, ?, ?);") 
  If result  ; check that it's been inserted
    result = DatabaseQuery(#dbaseID, "SELECT * FROM command")
    If result
      ClearDebugOutput()
      While NextDatabaseRow(#dbaseID)
        Debug GetDatabaseString(#dbaseID, 0)     
        Debug GetDatabaseString(#dbaseID, 1)     
        Debug GetDatabaseString(#dbaseID, 2)     
        Debug GetDatabaseString(#dbaseID, 3)     
      Wend
    EndIf
  Else
    MessageRequester("Error", "Could not save data!")
  EndIf
EndProcedure
Amateur Radio, D-STAR/VK3HAF
ebs
Enthusiast
Enthusiast
Posts: 530
Joined: Fri Apr 25, 2003 11:08 pm

Re: Problem saving data to SQLite Database

Post by ebs »

Your problem is with the word "It's" at the beginning of the second sentence under the Description.

Since it contains an apostrophe (single quote), that terminates the string value for the syntax parameter.

You can either use the SetDatabaseString() statements to avoid this issue, or create your own procedure to double any single quotes, i.e. change ' to ''.
User avatar
Columbo
Enthusiast
Enthusiast
Posts: 303
Joined: Wed Sep 10, 2014 7:17 am
Location: Ontario Canada
Contact:

Re: Problem saving data to SQLite Database

Post by Columbo »

Adding SetDatabaseString() did the trick. Thanks to all for your suggestions and example code. Much appreciated. :D

Cheers!
http://www.oldtimeradiotoday.com - Listen to or download classic old time radio broadcasts.
Post Reply