Problem writing to 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 writing to database

Post by Columbo »

I am trying to write some data into a database, which I have done before, but for some reason I am having a problem this time. It is probably something quite simple but I just don’t see it. Here is the problem.

I have a ListView populated with all of the tables in the database. When a table is selected in the ListView the name of the table is stored in a variable called tablename. This is working fine!

There are only 2 fields in the program and they are:

Code: Select all


  EditorGadget(#codeName, 200,100,480,20, #PB_Editor_WordWrap)
  EditorGadget(#codeExample, 200,140,1000,330, #PB_Editor_WordWrap)

When these fields are filled in and the submit button is pressed it goes to the routine to write the data to the selected table in the database.

The structure of the tables is:

record , INTEGER, Autoincrement
codename, CHAR, 100
code, CHAR, 3000

The write routine is as follows:

Code: Select all


;---------------------------------
; Update Database
;--------------------------------- 
 Procedure writeDatabase()
  name.s = GetGadgetText(#codeName)
  code.s = GetGadgetText(#codeExample)
     
  query.s = "INSERT INTO " + tablename + "(codename, code) VALUES('"+ name +"','"+ code +"')"
  result = DatabaseUpdate(#dbaseID, query)
  Debug result
EndProcedure

The weird thing is that if I enter “My Test” into the #codeName field and “This is a test.” Into the #codeExample field, it writes the data to the table with no problem and debugging result gives me a 1.

However, if I enter “My Program” into the #codeName field and I enter the following code into the #codeExample field:

Code: Select all


;---------------------------------
; Update Database
;--------------------------------- 
 Procedure writeDatabase()
  name.s = GetGadgetText(#codeName)
  code.s = GetGadgetText(#codeExample)
     
  query.s = "INSERT INTO " + tablename + "(codename, code) VALUES('"+ name +"','"+ code +"')"
  result = DatabaseUpdate(#dbaseID, query)
  Debug result
EndProcedure

Then it does not write to the database table and debug gives me a 0.

It seems as though it will write ok if there is only 2 or 3 short lines of data in the #codeExample field but if there as more, then it does not write.

Any thoughts?
http://www.oldtimeradiotoday.com - Listen to or download classic old time radio broadcasts.
User avatar
skywalk
Addict
Addict
Posts: 3972
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: Problem writing to database

Post by skywalk »

Debug your sql query string and verify it is accepted by your db with an external browser.
Ex. If SQLite, use DB Browser for SQLite.
Also, use Debug DatabaseError().
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
User avatar
Columbo
Enthusiast
Enthusiast
Posts: 303
Joined: Wed Sep 10, 2014 7:17 am
Location: Ontario Canada
Contact:

Re: Problem writing to database

Post by Columbo »

Thank you for your reply. I ran the test again twice. The first time I used the following:

In the #codeName field I entered: "My Test" (without the quotes).
In the #code field I entered: "This is a test." (again without the quotes).

The data was written to the database successfully and using Debug DatabaseError(), the debug window was blank.

Then I tried the test again and in the #codeName field I entered: "My Test"
In the #code field I entered:

Code: Select all

;---------------------------------
; Update Database
;---------------------------------
 Procedure writeDatabase()
  name.s = GetGadgetText(#codeName)
  code.s = GetGadgetText(#codeExample)
     
  query.s = "INSERT INTO " + tablename + "(codename, code) VALUES('"+ name +"','"+ code +"')"
  result = DatabaseUpdate(#dbaseID, query)
  Debug result
EndProcedure
[code]

Using [b]Debug DatabaseError()[/b], the debug window gives me [b]"near '"+ tablename +"' : syntax error"[/b].

Why does it write ok the first time and in the second try it gives a syntax error?
http://www.oldtimeradiotoday.com - Listen to or download classic old time radio broadcasts.
User avatar
skywalk
Addict
Addict
Posts: 3972
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: Problem writing to database

Post by skywalk »

You did not show your failed query string?
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
User avatar
Columbo
Enthusiast
Enthusiast
Posts: 303
Joined: Wed Sep 10, 2014 7:17 am
Location: Ontario Canada
Contact:

Re: Problem writing to database

Post by Columbo »

The query string is: query.s = "INSERT INTO " + tablename + "(codename, code) VALUES('"+ name +"','"+ code +"')"

I just tried another test and in the #code field, instead of entering a code snippet, I entered 3 pages of a text that I copied from an MS Word document.
When I pressed the submit button it wrote everything ok and using Debug DatabaseError() the Debug window was blank.

I then tried a third test and used a code snippet from a different program that I know works ok.

Code: Select all


Procedure writeDatabase()
   If tablename = "2DDrawing"
      tablename = "[2DDrawing]"                 
   EndIf
    
  maxRec + 1
  func.s = GetGadgetText(#Function)
  syn.s = GetGadgetText(#Syntax)
  descrip.s = GetGadgetText(#Description)
  param.s = GetGadgetText(#Parameters)  
  retV.s = GetGadgetText(#ReturnValue)
  rem.s = GetGadgetText(#Remarks)
  examp.s = GetGadgetText(#example)
  see.s = GetGadgetText(#Seealso)
  
  query.s = "INSERT INTO " + tablename + " VALUES ('" + maxRec + "', '"+ func + "', '" + syn + "', '" + descrip + "', '" + param + "', '" + retV + "','" + rem +"', '" + examp + "', '" + see + "')"
  result = DatabaseUpdate(#dbaseID, query)
          
EndProcedure


When I run this, the Debug DatabaseError() says: "near +maxRec+ : syntax error". If I delete the '"maxRec + 1"' from the query string and run it again, it flags the error as: near ""+ func + "": syntax error. It seems that the (+) sign with the variables may have something to do with it.
http://www.oldtimeradiotoday.com - Listen to or download classic old time radio broadcasts.
User avatar
skywalk
Addict
Addict
Posts: 3972
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: Problem writing to database

Post by skywalk »

Columbo wrote:The query string is: query.s = "INSERT INTO " + tablename + "(codename, code) VALUES('"+ name +"','"+ code +"')"
This is not the actual query string? This is your code BEFORE the db sees it. Please use a Debug query and inspect good and bad attempts to write to the db.
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
User avatar
spikey
Enthusiast
Enthusiast
Posts: 581
Joined: Wed Sep 22, 2010 1:17 pm
Location: United Kingdom

Re: Problem writing to database

Post by spikey »

I suspect that the query engine is getting hung up on the fact that your data contains valid SQL keywords and delimiters such as , and '.

The problem is that the overall statement that arrives at the engine appears to be a compound SQL statement - but not one which is validly formed; as a consquence the query is rejected with a syntax error. The 'near' clause flags up the point at which the query engine balked - but this doesn't actually mean that this is the point at which the syntax went off the rails...

This is why the syntax error seems to shift position each time you remove an offending token.

For example suppose I run a query:-

Code: Select all

"INSERT INTO " + table + " VALUES(1,2,3)"
and I set 'table' to be 'INSERT'.

The query engine receives:-

Code: Select all

INSERT INTO INSERT VALUES(1,2,3)
Ooops...

My first attempt to resolve this would be a parameterized query.
See the examples at the bottom of this page:
http://www.purebasic.com/documentation/ ... pdate.html
Last edited by spikey on Sun Aug 20, 2017 7:10 pm, edited 1 time in total.
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: Problem writing to database

Post by infratec »

Hi,

you need to esacpe a single qoute (') with two single quotes ('')

Code: Select all

code = ReplaceString(code, "'", "''")
Bernd
User avatar
skywalk
Addict
Addict
Posts: 3972
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: Problem writing to database

Post by skywalk »

If only he printed the query string... :oops:
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
User avatar
Columbo
Enthusiast
Enthusiast
Posts: 303
Joined: Wed Sep 10, 2014 7:17 am
Location: Ontario Canada
Contact:

Re: Problem writing to database

Post by Columbo »

skywalk wrote:
Columbo wrote:The query string is: query.s = "INSERT INTO " + tablename + "(codename, code) VALUES('"+ name +"','"+ code +"')"
This is not the actual query string? This is your code BEFORE the db sees it. Please use a Debug query and inspect good and bad attempts to write to the db.
How do you use a Debug query?
http://www.oldtimeradiotoday.com - Listen to or download classic old time radio broadcasts.
User avatar
skywalk
Addict
Addict
Posts: 3972
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: Problem writing to database

Post by skywalk »

Just put a Debug query BEFORE passing query to the dB? query is the variable name you chose...
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
User avatar
Columbo
Enthusiast
Enthusiast
Posts: 303
Joined: Wed Sep 10, 2014 7:17 am
Location: Ontario Canada
Contact:

Re: Problem writing to database

Post by Columbo »

Don't know if I did it right but here is the code that I used in my program with the Debug query and Debug DatabaseError().

Code: Select all


Procedure writeDatabase()
  maxRec + 1
  name.s = GetGadgetText(#codeName)
  code.s = GetGadgetText(#codeExample)
     
  query.s = "INSERT INTO " + tablename + "(codename, code) VALUES('"+ name +"','"+ code +"')"
  Debug query
  result = DatabaseUpdate(#dbaseID, query)
  
 Debug DatabaseError()
  
 EndProcedure

And here is the output from the Debug window:

Code: Select all


INSERT INTO Database(codename, code) VALUES(MyTest, 
;---------------------------------
; Update Database
;--------------------------------- 
 Procedure writeDatabase()
   If tablename = "2DDrawing"
      tablename = "[2DDrawing]"                 
   EndIf
    
  maxRec + 1
  func.s = GetGadgetText(#Function)
  syn.s = GetGadgetText(#Syntax)
  descrip.s = GetGadgetText(#Description)
  param.s = GetGadgetText(#Parameters)  
  retV.s = GetGadgetText(#ReturnValue)
  rem.s = GetGadgetText(#Remarks)
  examp.s = GetGadgetText(#example)
  see.s = GetGadgetText(#Seealso)
  
  query.s = "INSERT INTO " + tablename + " VALUES ('" + maxRec + "', '"+ func + "', '" + syn + "', '" + descrip + "', '" + param + "', '" + retV + "','" + rem +"', '" + examp + "', '" + see + "')"
  result = DatabaseUpdate(#dbaseID, query)
          
EndProcedure)

near ";": syntax error

http://www.oldtimeradiotoday.com - Listen to or download classic old time radio broadcasts.
User avatar
skywalk
Addict
Addict
Posts: 3972
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: Problem writing to database

Post by skywalk »

I'm sorry, but your debug output has way more stuff than just query.s and databaseerror(). :shock:

If I assume this query.s =
"INSERT INTO Database(codename, code) VALUES(MyTest, "
;
Then, you have an incomplete SQL statement.
The db sees a ';' on the next line and terminates.
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
User avatar
Columbo
Enthusiast
Enthusiast
Posts: 303
Joined: Wed Sep 10, 2014 7:17 am
Location: Ontario Canada
Contact:

Re: Problem writing to database

Post by Columbo »

Yes, the variable tablename is the variable that holds the name of the table which in this case is "Database". The variable name.s holds the text that was entered into the #codeName field and the variable code.s holds the text that was entered into the #codeExample field.

So, when the line query.s = "INSERT INTO " + tablename + "(codename, code) VALUES('"+ name +"','"+ code +"')" is debugged, the output window shows "INSERT INTO Database(codename, code) VALUES(MyTest, ", with the "MyTest" being the value of the variable name.s, and then it shows the value of the variable code.s which is the code snippet which starts with ";-----------------------------" and is followed by the rest of the code snippet text.
Then after that, it shows the out put of the DatabaseError() which is "near ";": syntax error".
http://www.oldtimeradiotoday.com - Listen to or download classic old time radio broadcasts.
User avatar
Bisonte
Addict
Addict
Posts: 1226
Joined: Tue Oct 09, 2007 2:15 am

Re: Problem writing to database

Post by Bisonte »

If you want, that someone can help : Post a full working code... no "snippets" from it.

I think your string handling at the query is wrong.

Try this :

Code: Select all

query.s = "INSERT INTO " + tablename + "(codename, code) VALUES(?, ?)"

SetDatabaseString(#dbaseID, 0, name)
SetDatabaseString(#dbaseID, 1, code)

Debug query

If DatabaseUpdate(#dbaseID, query)
  Debug "Ok"
Else
  Debug DatabaseError()
EndIf
and by the way ... a table named Database is not a good idea. Some words are reserved by the database system (like text, integer etc.). I don't know if "database" is one of the words... but maybe...

Edit: And another thing is ... : How many tables you have in your database with the columns codename and code ?
a variable for a table that couldn't change is also no good idea (or more work to do ;) )
PureBasic 6.04 LTS (Windows x86/x64) | Windows10 Pro x64 | Asus TUF X570 Gaming Plus | R9 5900X | 64GB RAM | GeForce RTX 3080 TI iChill X4 | HAF XF Evo | build by vannicom​​
English is not my native language... (I often use DeepL to translate my texts.)
Post Reply