update database

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
haribo
Posts: 23
Joined: Fri Jun 29, 2007 8:04 pm

update database

Post by haribo » Thu Jul 19, 2007 8:27 pm

hi,
little problem,
RR tell "unrecognized token" :evil:

i test to update my database, nearly the same to insert into

Code: Select all

on mouseUp
  global gConId
  
  put field "Name" into tName
  put field "Vorname" into tVorname
  put field "Strasse" into tStrasse
  put field "Hausnr" into tHausnr
  put field "PLZ" into tPLZ
  put field "Ort" into tOrt
  put field "Tel1" into tTel1
  put field "Tel2" into tTel2
  put field "Tel3" into tTel3
  put field "Gebdatum" into tGebdatum
  put field "Klasse" into tKlasse
  put field "Geschl" into tGeschl
  put field "Name2" into tName2
  put field "Name3" into tName3
  put field "Konf" into tKonf
  put field "Staatsang" into tStaatsang
  put field "Email" into tEmail
  
  local tSQL
put "UPDATE Schueler (Name, Vorname, Strasse, Hausnr, PLZ, Ort, Gebdatum, Tel1, Tel2, Tel3, Geschl, Konf, Staatsang, Klasse, Nachname2, Nachname3, Email) Values ( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15: :16, :17)" , "tName", "tVorname", "tStrasse", "tHausnr", "tPLZ", "tOrt", "tGebdatum", "tTel2", "tTel2", "tTel3", "tGeschl", "tKonf", "tStaatsang", "tKlasse", "tName2", "tName3", "tEmail" into tSQL 
revExecuteSQL gConID, tSQL
put the result into tResult

on runmode " unrecognized token ":",
in all buttons for save it works,

whats that

Ralle

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5150
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Post by Mark » Thu Jul 19, 2007 9:25 pm

Ralle,

It looks like you are missing a comma between :15 and :16 while you have a colon too many.

Mark
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

haribo
Posts: 23
Joined: Fri Jun 29, 2007 8:04 pm

Post by haribo » Thu Jul 19, 2007 10:20 pm

hi,
add the comma, dont work, the same message

Janschenkel
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 977
Joined: Sat Apr 08, 2006 7:47 am
Location: Aalst, Belgium
Contact:

Post by Janschenkel » Thu Jul 19, 2007 11:52 pm

Hi Ralle,

Try the following:

Code: Select all

on mouseUp 
  global gConId 
  
  put field "Name" into tName 
  put field "Vorname" into tVorname 
  put field "Strasse" into tStrasse 
  put field "Hausnr" into tHausnr 
  put field "PLZ" into tPLZ 
  put field "Ort" into tOrt 
  put field "Tel1" into tTel1 
  put field "Tel2" into tTel2 
  put field "Tel3" into tTel3 
  put field "Gebdatum" into tGebdatum 
  put field "Klasse" into tKlasse 
  put field "Geschl" into tGeschl 
  put field "Name2" into tName2 
  put field "Name3" into tName3 
  put field "Konf" into tKonf 
  put field "Staatsang" into tStaatsang 
  put field "Email" into tEmail 
  
  local tSQL 
put "UPDATE Schueler (Name, Vorname, Strasse, Hausnr, PLZ, Ort, Gebdatum, Tel1, Tel2, Tel3, Geschl, Konf, Staatsang, Klasse, Nachname2, Nachname3, Email) Values ( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17)" into tSQL 
revExecuteSQL gConID, tSQL, "tName", "tVorname", "tStrasse", "tHausnr", "tPLZ", "tOrt", "tGebdatum", "tTel2", "tTel2", "tTel3", "tGeschl", "tKonf", "tStaatsang", "tKlasse", "tName2", "tName3", "tEmail"
put the result into tResult
end mouseUp
A 'put' command won't do the subsituttion for you - instead, you need to append the variables list to the revExecuteSQL command.

Hop this helped,

Jan Schenkel.
Quartam Reports & PDF Library for LiveCode
www.quartam.com

haribo
Posts: 23
Joined: Fri Jun 29, 2007 8:04 pm

Post by haribo » Fri Jul 20, 2007 11:36 pm

i update the code,
"" naer "(" syntax error ""
i have change my own code, no c + p,
ralle

Janschenkel
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 977
Joined: Sat Apr 08, 2006 7:47 am
Location: Aalst, Belgium
Contact:

Post by Janschenkel » Mon Jul 23, 2007 7:38 am

Hi Ralle,

I suspect you're missing a quote somewhere in the actual version of your script. Revolution does not allow you to divide strings over multiple lines, unless you close the string and use the ampersand and continuation characters:

Code: Select all

put "UPDATE Schueler (Name, Vorname, Strasse, Hausnr, " & \
"PLZ, Ort, Gebdatum, Tel1, Tel2, Tel3, Gesch1" & \
"... don't forget to include the rest ..." & \
", :15, :16, :17)" into tSQL
Jan Schenkel.
Quartam Reports & PDF Library for LiveCode
www.quartam.com

haribo
Posts: 23
Joined: Fri Jun 29, 2007 8:04 pm

Post by haribo » Mon Jul 23, 2007 7:35 pm

Hi Jan,
the line is not the problem, i dont know the howto and have it in 1 line :oops:

my way, i have many editfields for name, vorname, ......., below of this fields i an listfield, i pick one line of listfield, this appears in the editfields, i change some text of fields, click update > error, i think this is a little problem with the schuelerId, the app dont know in which line to put the data.
the update is only for 1 person
Ralle

haribo
Posts: 23
Joined: Fri Jun 29, 2007 8:04 pm

Post by haribo » Tue Jul 24, 2007 6:42 pm

hi,
then i try this from sqlite documentation
[/code]
revExecuteSQL gConID, tSQL,"UPDATE Schueler Set" &name = "tName", Vorname=tVorname, Strasse = tStrasse, Hausnr = tHausnr, PLZ = tPLZ, Ort = tOrt, Gebdatum = tGebdatum, Tel1 = tTel1, Tel2 = tTel2 , Tel3 = tTel3, Geschl = tGeschl, Konf = tKonf, Staatsang = tStaatsang, Klasse = tKlasse, Nachname2 = tName2, Nachname3 = tName3, Email = tEmail &SchuelerID = "tNr"

Code: Select all

dont work, result = 0
without the & = error in scripteditor, hint "=" , when i try " Where" before SchuelerID, error in runtime

Ralle

Janschenkel
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 977
Joined: Sat Apr 08, 2006 7:47 am
Location: Aalst, Belgium
Contact:

Post by Janschenkel » Wed Aug 01, 2007 6:45 pm

Hi Haribo,

Let's take another look at the syntax:

Code: Select all

revExecuteSQL databaseID, SQLStatement [,{variablesList | arrayName}]
Obviously, you have the first part working, but the trouble is in the SQL statement and the variables list.

There are three ways of executing UPDATE statements in your database.

1. Concatenating everything yourself into a single query, foregoing the variables list or array name syntax:

Code: Select all

put "UPDATE Schueler SET" && \
    "name = '" & tName & "'," && \
    "vorname = '" & tVorName & "'," && \
    "strasse = '" & tStrasse & "'," && \
    /* ... add lines for each pair of columns and variables ... */
    "email = '" & tEmail & "'" && \
    "WHERE SchuelerID = " & tNr \
    into tSQLstatement
revExecuteSQL gConID, tSQLstatement
put the result into tResult
if tResult is not a number then answer error tResult
Note the apostrophe characters around string columns.

2. Using the variables list, you insert the :1, :2, ..., :n placeholders into your query as follows:

Code: Select all

put "UPDATE Schueler SET" && \
    "name = :1," && \
    "vorname = :2," && \
    "strasse = :3," && \
    /* ... add lines for each pair of columns and variables ... */
    "email = :99" && \
    "WHERE SchuelerID = :100" \
    into tSQLstatement
revExecuteSQL gConID, tSQLstatement, \
    "tName", "tVorname", "tStrasse", \
    /* ... add the other variable names, surrounded with quotes ... */
    "tEmail", "tNr"
put the result into tResult
if tResult is not a number then answer error tResult
Note that there are no apostrophes needed here, as Revolution will add these automatically where needed.

3. Using the array approach, you collect the data into an array, using numeric keys, and then again insert the :1, :2, ..., :n placeholders into your query as follows:

Code: Select all

put field "name" into tArray[1]
put field "vorname" into tArray[2]
put field "strasse" into tArray[3]
/* ... add lines for each onscreen field ... */
put field "email" into tArray[99]
put field "nr" into tArray[100]
put "UPDATE Schueler SET" && \
    "name = :1," && \
    "vorname = :2," && \
    "strasse = :3," && \
    /* ... add lines for each pair of columns and variables ... */
    "email = :99" && \
    "WHERE SchuelerID = :100" \
    into tSQLstatement
revExecuteSQL gConID, tSQLstatement, "tArray"
put the result into tResult
if tResult is not a number then answer error tResult
Note that there are no apostrophes needed here, as Revolution will add these automatically where needed.

Naturally, the :99 and :100 are there for you to replace with the correct sequential numbers ;-)

Of course, you won't be able to UPDATE a record if there's no Schueler record with the provided SchuelerID - at that point, the result will be the number zero, as no database records were affected.

Hope this helped,

Jan Schenkel.
Quartam Reports & PDF Library for LiveCode
www.quartam.com

Immanuel
Posts: 6
Joined: Mon Aug 10, 2020 4:52 pm

Re: update database

Post by Immanuel » Thu Sep 03, 2020 9:09 am

Hello community,

please can someone explain this to me?

this is not working:

Code: Select all

   put "UPDATE user SET" && \## Ds geht hier nicht
         "vorname = :1," && \
         "nachname = :2," && \
         "user = :3," &&  \
         "passwort = :4" && \
         "dat = :5" &&  \
         "rolle = :6" &&  \
         "WHERE id = :7"  \
         into tSQL2
   put tArray[6]
   
   revExecuteSQL connID, tSQL2 , "tArray"
the error message is:
near "dat": syntax error

but this one already works:

Code: Select all

  put "UPDATE user SET  vorname='"&tArray[1]&"', nachname='"&tArray[2]&"', user='"&tArray[3]&"', passwort='"&tArray[4]&"', dat='"&tArray[5]&"', rolle='"&tArray[6]&"'  WHERE id = '"&tArray[7]&"'" into tSQL
  revExecuteSQL connID, tSQL
 

dunbarx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9567
Joined: Wed May 06, 2009 2:28 pm
Location: New York, NY

Re: update database

Post by dunbarx » Thu Sep 03, 2020 4:53 pm

Hi.

The upper part of your snippet (with all the soft returns) compiles for me without issue. I cannot test the command "revExecuteSQL..." because I did not set anything up to do so.

Craig

bwmilby
Posts: 438
Joined: Wed Jun 07, 2017 5:37 am
Location: Henrico, VA
Contact:

Re: update database

Post by bwmilby » Fri Sep 04, 2020 12:01 am

I think the comma is missing after the 4 and 5.
Brian Milby

Script Tracker https://github.com/bwmilby/scriptTracker

dunbarx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9567
Joined: Wed May 06, 2009 2:28 pm
Location: New York, NY

Re: update database

Post by dunbarx » Fri Sep 04, 2020 5:33 am

Brian.

The missing comma does seem to be missing in building a proper string for later use in the "revExecuteSQL" command, but why would that throw a compile error?

Craig

Immanuel
Posts: 6
Joined: Mon Aug 10, 2020 4:52 pm

Re: update database

Post by Immanuel » Fri Sep 04, 2020 7:05 am

Thanks guys, yes of course it was the comma at 4 and 5.
I had missed it completely.

Immanuel

Post Reply

Return to “Databases”