Syntax error with multiple requests

[wanamoa]

Hello,

i have a little problem with my program.
The answer advises me to check the correct syntax up to my mysql version, but i caught the result and try to inject it with another soft (sequel) and the request is correct ! So i think i mistake with Livecode syntax.
Here my code :

Code: Select all

on mouseUp
   local timport, tPasse
   global gConnectionID
   lock screen
   -- Les champs sont délimités par un point-virgule
   set itemDel to ";"
   
   --Sélection du fichier d'importation des profs
   answer file " Sélectionner le fichier :"
         if the result is not "cancel" then
            put URL("file:" & it) into timport
            put empty into tSQL
            replace "'" with "" in timport
            replace "ç" with "c" in timport
            put 1 into tPasse
            repeat for each line tline in timport
               put toUpper(item 1 of tline) into tNom
               put toUpper(item 2 of tline) into tPrenom
               put toUpper(item 6 of tline) into tClassePP
               put item 3 of tline into tLogin
               put toUpper(item 4 of tline) into tMdp
               put toUpper(item 5 of tline) into tMatiere
                  --if tPasse >1 then
                  put "INSERT INTO profs(ID, nom_prof, prenom_prof, login_prof, passe_prof, matiere_prof, pp_classe)" & \
                        " VALUES(NULL,'"&tNom&"', '"&tPrenom&"', '"&tLogin&"','"&tMdp&"', '"&tMatiere&"', '"&tClassePP&"');" & cr after tSQL
                  --else
                        --put "INSERT INTO profs(ID, nom_prof, prenom_prof, login_prof, passe_prof, matiere_prof, pp_classe)" & \
                        --" VALUES(NULL,'"&tNom&"', '"&tPrenom&"', '"&tLogin&"','"&tMdp&"', '"&tMatiere&"', '"&tClassePP&"');" & cr into tSQL
                        put 2 into tPasse
                        --end if
                     end repeat
                  end if
                  
                  --On éxécute la requête
                  put tSQL
                  revExecuteSQL gConnectionID , tSQL

If i try with "into" and not "after", it works...


Here the request :

Code: Select all

INSERT INTO profs(ID, nom_prof, prenom_prof, login_prof, passe_prof, matiere_prof, pp_classe) VALUES(NULL,'TOUI', 'PATRIC', 'touip','xxxxxxx', 'FRANCAIS', '');
INSERT INTO profs(ID, nom_prof, prenom_prof, login_prof, passe_prof, matiere_prof, pp_classe) VALUES(NULL,'PICHETI', 'JENICHEL', 'pichetj','xxyxxyx', 'ATELIER', '4A');
...(etc)

So i need your help .
Thanks.

[Mark]

Hi,

"Into" instead of "after" where? Which line?

Mark

[wanamoa]

Hello Mark,

If i try "into" instead of "after", it works but i have just one insertion (one line). Here :

Code: Select all

--if tPasse >1 then
                  put "INSERT INTO profs(ID, nom_prof, prenom_prof, login_prof, passe_prof, matiere_prof, pp_classe)" & \
                        " VALUES(NULL,'"&tNom&"', '"&tPrenom&"', '"&tLogin&"','"&tMdp&"', '"&tMatiere&"', '"&tClassePP&"');" & cr after tSQL

So something goes wrong with multiple requests ; but if i copy the request (using put the result) and paste in Sequel, there is no problem.
Thanks for your help.

[Mark]

Hi,

I understand it. I have always thought that multiple SQL requests were impossible, but recently read that it is possible to do this. This isn't mentioned in the release notes of 5.5.4 and 6.1 and thus I would still assume that multiple requests are impossible (sorry if this is confusing, I just want to be thorough).

Multiple requests aren't really necessary, because you make the connection only once and as long as the connection is open, communication between LiveCode and the database engine is pretty fast. If you want to make your query more efficient, try rewriting it. For example

Code: Select all

INSERT INTO profs SELECT 1 as ID,'DUVAL' as nom_prof UNION SELECT 2,'REVILLON' UNION SELECT 3,'VANOOSTEN'

It is alright to make your queries really, really long. Especially MySQL is good at optimising long queries; SQLite not so much, I don't expect that to be a problem in this case as long as you insert less than 500 records at once.

Kind regards,

Mark

[wanamoa]

Thanks Mark.

If i well understand you, you advise me to :
- read a line of my file,
- execute the relative MYSQL query,
- read the next file, etc.

I will try. However, i have used this piece of code (multiple query) with success in a SQlite database. But i have caught your notice about the treatment speed between LC and Mysql .
Hope all my sentences are understandable !

Regards.

[EDIT : indeed, it works and it is really very fast ! But for my personal curiosity, i would be interested to know why my multiple request does not work when i put it in LC].

[Mark]

Hi,

Actually, I gave you an example to show how to do it all at once, with a modified query. You ready your entire file, use that data to produce a query like the one in my example, and execute the query.

Multiple requests don't work because the programmer decided to do it that way

Kind regards,

Mark

[wanamoa]

Hi Mark,

Ok, but to do the job your way, i cannot use variables and loops, can I ?
When you said :

Multiple requests don't work because the programmer decided to do it that way

... do you mean my way is not correct and there is a syntax error ? But where ?

I'm sorry for all these questions but i do not catch all subtleties of english (which makes 2 comprehension difficulties : LC and english !)

[Mark]

Hi,

Did you look at my SQLite example and do you understand it?

Kind regards,

Mark

[wanamoa]

Hi,

Did you look at my SQLite example and do you understand it?

Mark

Do you refer to your example code in previous posts ...?

Regards.

[Mark]

yes (the numimum number of characters I need to enter is 10 and that is why I add this, but the answer is just "yes")

[wanamoa]

I shaked my brain and I have tried this :

Code: Select all

--Sélection du fichier d'importation des profs
   answer file " Sélectionner le fichier :"
   if the result is not "cancel" then
      put URL("file:" & it) into timport
      put "INSERT INTO profs" into tSQL
      replace "'" with "" in timport
      replace "ç" with "c" in timport
      put 1 into tPasse
      repeat for each line tline in timport
         put toUpper(item 1 of tline) into tNom
         put toUpper(item 2 of tline) into tPrenom
         put toUpper(item 6 of tline) into tClassePP
         put item 3 of tline into tLogin
         put toUpper(item 4 of tline) into tMdp
         put toUpper(item 5 of tline) into tMatiere

-- union or not union
         if tPasse >1 then
            put " UNION SELECT NULL as ID, '"&tNom&"' as nom_prof, '"&tPrenom&"' as prenom_prof, '"&tLogin&"' as login_prof, '"&tMdp&"' as passe_prof, '"&tMatiere&"' as matiere_prof, '"&tClassePP&"' as pp_classe" after tSQL
         else
            put " SELECT NULL as ID, '"&tNom&"' as nom_prof, '"&tPrenom&"' as prenom_prof, '"&tLogin&"' as login_prof, '"&tMdp&"' as passe_prof, '"&tMatiere&"' as matiere_prof, '"&tClassePP&"' as pp_classe" after tSQL
            put 2 into tPasse
            end if
      end repeat
   end if
   
   
   --On éxécute la requête
   put tSQL
   revExecuteSQL gConnectionID , tSQL
   
   if the result is a number then
      answer info "New record added."
   else
      answer error "There was a problem adding the record to the database:" & cr & the result
   end if
   unlock screen
end mouseUp

And it works ! I guess this is what you lead me towards.
Thanks a lot : now, i know that i can build a long but simple request or execute many short requests one by one with no difference of speed for my amount of data (is it clear ?).

Best regards.

[Mark]

Hi,

Exactly. You've understood it

I'm not exactly sure how this affects speed. I can imagine that in some cases it is faster to create one very long query while perhaps in other cases it is faster to have several short queries.

Kind regards,

Mark

[Simon]

Hi Mark,
Today I did my first forays into data grids and mySQL and I'm very pleased with the results.
DG loaded by mySQL with images. LC is the best!
Now I think I have the same question but not sure. on mouseDoubleUp >> dgHilitedIndex I load editable fields with the info (i saw there is another way to do this but for now I can handle this much). I can edit the fields and then send them up to the database, but I'm pretty sure it's inefficient. I'm doing it this way:

Code: Select all

  put "UPDATE " & tTableName && "SET first_name=" &quote& tFirstName &quote && "WHERE  id=" & gID into tSQL
   revExecuteSQL gConnectionID, tSQL, "tFirstName", "tLastName", "tMob", "tAddress","gImageData"
   
   put "UPDATE " & tTableName && "SET last_name=" &quote& tLastName &quote && "WHERE  id=" & gID into tSQL
   revExecuteSQL gConnectionID, tSQL, "tFirstName", "tLastName", "tMob", "tAddress","gImageData"
etc.

It all works but can I put the changes in 1 line and just use revExecuteSQL once?

Thanks,
Simon

[Mark]

Hi Simon,

If your table has a key, you can do

Code: Select all

REPLACE INTO table VALUES ('id1','val1','val2',...)  VALUES ('id2','val1','val2',...) VALUES ('id3','val1','val2',...) ....

Kind rehgards,

Mark

[dave_probertGA6e24]

Maybe try this:

Code: Select all

put "UPDATE " & tTableName && "SET first_name=" &quote& tFirstName &quote & comma & last_name=" &quote& tLastName &quote && "WHERE  id=" & gID into tSQL
revExecuteSQL gConnectionID, tSQL, "tFirstName", "tLastName", "tMob", "tAddress","gImageData"

I 'think' it should work with Rev - it's certainly correct SQL for mySQL and SQLite. Basically comma-separated lists of field=value are ok. Unless I'm missing the problem completely!

put "UPDATE " & tTableName && "SET first_name=" &quote& tFirstName &quote && "WHERE id=" & gID into tSQL
revExecuteSQL gConnectionID, tSQL, "tFirstName", "tLastName", "tMob", "tAddress","gImageData"

put "UPDATE " & tTableName && "SET last_name=" &quote& tLastName &quote && "WHERE id=" & gID into tSQL
revExecuteSQL gConnectionID, tSQL, "tFirstName", "tLastName", "tMob", "tAddress","gImageData"

Cheers,
Dave