DatabaseUpdate SQLite update method not working

[C87]

• Hello, I am updating an SQLite table with data from an input screen. The first method works fine. The second, which involves a lot less work, does not.
  I would prefer to use the second method as there are fewer possibilites of a typo, which isn't that important but as there are nearly 30 fields to update there is clearly far less work involved and easier to notice any field omissions etc.
  
  The second method doesn't throw an error and gives the impression it has completed but does not update the table. The SQL code line is fine and checks out in DBBrowser. Is this an issue in PB or have I got some syntax wrong?
  
  Many thanks, C87

Code: Select all

  ;SQLite  Database
     REPL$ = "UPDATE PWS10 SET "
     REPL$ = REPL$ + "GROUPBY= '" + fmGroupBy + "', "  
     REPL$ = REPL$ + "WEBSITE='" + fmWebsite + "', "    
     REPL$ = REPL$ + "EMAIL='" + fmEmail +"', "  
     REPL$ = REPL$ + "NAME= '" + fmName + "', "            
     REPL$ = REPL$ + "ACCTYPE='" + fmAccType + "'"
     REPL$ = REPL$ + " WHERE RECNO=?"
     ;
     If DatabaseUpdate(0,REPL$)   
          ;               
          MessageRequester("UPDATE COMPLETED","Your changes have been saved",#PB_MessageRequester_Ok)
          ;
      Else
           ;
           MessageRequester("ERROR UPDATING RECORD","Changes have not been saved, please try again."+Chr(10)+Chr(10)+"Error: "+ DatabaseError(), #PB_MessageRequester_Info)
      EndIf
        
;                ▲   ▲   ▲ the above works Ok

;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

;                ▼    ▼   ▼  the code below goes to UPDATE COMPLETED i.e., no error on update but the PWS10 table is not updated with the changes
     SetDatabaseLong(0,0,DBRECNO) 
     SetDatabaseString(0,1,fmGroupBy)
     SetDatabaseString(0,2,fmWebsite)
     SetDatabaseString(0,3,fmEmail)
     SetDatabaseString(0,4,fmName)
     SetDatabaseString(0,5,fmAccType)
     ;
     If DatabaseUpdate(0,"UPDATE PWS10 SET  GROUPBY=?, WEBSITE=?, EMAIL=?, NAME=?,ACCTYPE=?  WHERE RECNO=?" )  ;   this messages Saved but doesn't do the save
         ;               
         MessageRequester("UPDATE COMPLETED","Your changes have been saved",#PB_MessageRequester_Ok)
     Else
          ;
          MessageRequester("ERROR UPDATING RECORD","Changes have not been saved, please try again."+Chr(10)+Chr(10) + "Error: " +DatabaseError(), #PB_MessageRequester_Info)
      EndIf
     

[Kiffi]

i'm not sure, but please try this:

Code: Select all

SetDatabaseString(0,0,fmGroupBy)
SetDatabaseString(0,1,fmWebsite)
SetDatabaseString(0,2,fmEmail)
SetDatabaseString(0,3,fmName)
SetDatabaseString(0,4,fmAccType)
SetDatabaseLong  (0,5,DBRECNO)

Greetings ... Peter

[Fangbeast]

I have 2 questions.. How does sqlite update a record if there was none specified?

WHERE RECNO=?"

Also, if you are using pb 5.6, why not simplify the typing to: (5.6 can also use line continuation to do away with constantly typing in REPL$)

Code: Select all

REPL$ = "UPDATE PWS10 SET "
REPL$ + "GROUPBY= '" + fmGroupBy + "', " 
REPL$ + "WEBSITE='"   + fmWebsite + "', "   
REPL$ + "EMAIL='"       + fmEmail +"', " 
REPL$ + "NAME= '"      + fmName + "', "           
REPL$ + "ACCTYPE='"  + fmAccType + "'"
REPL$ + " WHERE RECNO=?"

For instance:

Code: Select all

REPL$ = "UPDATE PWS10 SET " + 
"GROUPBY= '"    + fmGroupBy + "', " + 
"WEBSITE='"     + fmWebsite + "', " + 
"EMAIL='"       + fmEmail   + "', " + 
"NAME= '"       + fmName    + "', " + 
"ACCTYPE='"     + fmAccType + "' " + 
"WHERE RECNO=?"

[spikey]

How does sqlite update a record if there was none specified?

SQLite is loosely typed. 'WHERE RECNO=?' is syntactically correct SQL, in isolation at least, so it will do its best to bend the supplied arguments to fit and execute it.

In this case RECNO appears to be a long so at a guess I'd say ? ends up as null or maybe zero, assuming no SetDatabase* gives it a different value. At execution I'd expect no rows actually to match the supplied data and no updates will take place. An update with no results is perfectly acceptable too, at least as far as the engine is concerned, so no error gets reported.

Personally I'd regard this as a program bug but the sample code shown isn't complete...

[Fangbeast]

How does sqlite update a record if there was none specified?

SQLite is loosely typed. 'WHERE RECNO=?' is syntactically correct SQL, in isolation at least, so it will do its best to bend the supplied arguments to fit and execute it.

In this case RECNO appears to be a long so at a guess I'd say ? ends up as null or maybe zero, assuming no SetDatabase* gives it a different value. At execution I'd expect no rows actually to match the supplied data and no updates will take place. An update with no results is perfectly acceptable too, at least as far as the engine is concerned, so no error gets reported.

Personally I'd regard this as a program bug but the sample code shown isn't complete...

Hmm, I understood that for a change. But it is doing my head in a bit. Mind you, having seen the results of DatabaseError(), I try to check if an update has taken place or not in my code as I fell into that trap many times. (The no update happened but valid sqlite)

Teach me more Master spikey!

[citystate]

if a record doesn't exist, Sqlite ignores an UPDATE - I prefer to use INSERT OR REPLACE (making sure that recno is a primary key)

Code: Select all

;                ▼    ▼   ▼  the code below goes to UPDATE COMPLETED i.e., no error on update but the PWS10 table is not updated with the changes
     SetDatabaseLong(0,0,DBRECNO) 
     SetDatabaseString(0,1,fmGroupBy)
     SetDatabaseString(0,2,fmWebsite)
     SetDatabaseString(0,3,fmEmail)
     SetDatabaseString(0,4,fmName)
     SetDatabaseString(0,5,fmAccType)
     ;
     If DatabaseUpdate(0,"INSERT OR REPLACE INTO PWS10(RECNO,GROUPBY,WEBSITE,EMAIL,NAME,ACCTYPE) VALUES(?,?,?,?,?,?)" )  ;   this messages Saved but doesn't do the save
         ;               
         MessageRequester("UPDATE COMPLETED","Your changes have been saved",#PB_MessageRequester_Ok)
     Else
          ;
          MessageRequester("ERROR UPDATING RECORD","Changes have not been saved, please try again."+Chr(10)+Chr(10) + "Error: " +DatabaseError(), #PB_MessageRequester_Info)
      EndIf
     

[#NULL]

AffectedDatabaseRows()

Returns the number of rows affected by the last DatabaseUpdate() operation.

[C87]

suggested changing the Index location numbers but they are correct as they are. DBRECNO is a Long memory var taken from the field RecNo on the 'current' record in the Table PWS10 where RecNo is a unique number that my system fils in when a new record is added
Its location at 0 is correct. I do not use the number SQLite generates. GROUPBY ihas to be 1, etc. It is confirmed if I Debug the results of the SetDatabaseString() SetDatabaseLong() functions.

RECNO=? : On the first example the RECNO=? works Ok. So why not on the other? Which begs the question if it works with RECNO=? why don't the rest get written?

I can certainly see the advantage of not doing REPL$ = REPL$ .... on each line but I read somewhere in Forums that PB didn't allow concatenating lines as you have shown. I'll certainly change to that.

Fangbeast suggested the following to reduce typing, which is better and works great.
REPL$ = "UPDATE PWS10 SET " +
"GROUPBY= '" + fmGroupBy + "', " +
"WEBSITE='" + fmWebsite + "', " +
"EMAIL='" + fmEmail + "', " +
"NAME= '" + fmName + "', " +
"ACCTYPE='" + fmAccType + "' " +
"WHERE RECNO=?"

Citystate: A record will always exist as I am preventing the user from getting to BOF or EOF. To get to EDIT the selected record it must exist and I don't want to allow a user to add another record when completing an edit. I'll only allow them to add a record after they click the [ADD] button.

#Null suggested using AffectedDatabaseRows() which I didn't know about AffectedDatabaseRows() and have incorporated it into the code :

Code: Select all

 Result = AffectedDatabaseRows(0)
 MessageRequester("UPDATE COMPLETED","Your changes have been saved  "+Chr(10)+"("+Str(DBRECNO)+" : " + Str(Result) + ")", #PB_MessageRequester_Ok)

This confirms the chosen record it should be updating and the number or rows updated. In my 1st example it returns both correctly eg 5 : 1
In the 2nd it returns 5 : 0 

It will now show me these values after UPDATE in the MsgRequester(). In every case the DBRECNO is always correct. The Result is 1 in the 1st example which works and 0 in the second, which doesn't. It is a huge step forward as I can now trap an update that may not happen by checking for a zero result. I'll fiddle about with the code later and using AffectedDatabaseRows() maybe locate why it isn't updating. In the meantime I've used Fangbeast's suggestion to at least reduce some typing by concatenating the lines. I wanted really to get away from typing all the +'" "'+ stuff. I'll probably end up writing a short program to write that code for me from a list of 30 field names and cut & paste it in in future applications. It will stick all those single and double speech marks in in a second!

As I say on my 1st example RECNO=? works fine but not on the second. In fact I am unable to amend just one field . If I sort it I'll post in on Forums.

Have to say many thanks to you all, most appreciated. C87

[spikey]

Its location at 0 is correct.

No, it's not. Kiffi was correct.
The statement index numbers in the SetDatabase* commands reflect the parameters as they appear in the statement being executed not the table schema (or whatever else you might be thinking they represent). See the parameter section of the help page https://www.purebasic.com/documentation ... elong.html

So in the statement:

Code: Select all

UPDATE PWS10 SET  GROUPBY=?, WEBSITE=?, EMAIL=?, NAME=?,ACCTYPE=?  WHERE RECNO=?

GROUPBY is the first parameter (index 0), WEBSITE is the second (index 1) ... RECNO is the sixth (index 5).

It is confirmed if I Debug the results of the SetDatabaseString() SetDatabaseLong() functions.

These functions don't set a return value.

[C87]

Yep! both Kiffi & Spikey are 100% correct. Sorry about that guys.

Code: Select all

code suggested by Kiffi:
SetDatabaseString(0,0,fmGroupBy)
SetDatabaseString(0,1,fmWebsite)
SetDatabaseString(0,2,fmEmail)
SetDatabaseString(0,3,fmName)
SetDatabaseString(0,4,fmAccType)
SetDatabaseLong  (0,5,DBRECNO)

Spikey wrote earlier - which was right!
Personally I'd regard this as a program bug but the sample code shown isn't complete...

(Sorry about that Spikey but I thought at the time ; hey, there's nothing wrong with my code Spikey) But there was!

Code: Select all

UPDATE PWS10 SET  GROUPBY=?, WEBSITE=?, EMAIL=?, NAME=?,ACCTYPE=?  WHERE RECNO=?

GROUPBY is the first parameter (index 0), WEBSITE is the second (index 1) ... RECNO is the sixth (index 5).

All confirmed as working code, following tests

Apologies for not testing Kiffi's code in the first place, regards C87

[Kiffi]

Apologies for not testing Kiffi's code in the first place

no problem, you're welcome!