MYSQL syntax error

[maverickalex]

Hi i wonder of someone can identify the error here, i think it is around constructing the Mysql, The connection to the database is fine.

Code: Select all

  put "xxxxxxxxxx" into tDatabaseAddress
    put "xxxxxxxx" into tDatabaseName
    put "xxxxxxxx" into tDatabaseUser
    put "xxxxxxxx" into tDatabasePassword
    
    -- connect to the database
    put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult
    
    -- check if it worked and display an error message if it didn't
    -- & set the connection ID global
    if tResult is a number then
        put tResult into gConnectionID
        answer info "Connected to the database." & cr & "Connection ID = " & gConnectionID
    else
        put empty into gConnectionID
        answer error "Unable to connect to the database:" & cr & tResult
     end if
     
     --send data
     global gConnectionID
    if gConnectionID is not a number then
        answer error "Please connect to the database first."
        exit to top
    end if
    
 -- table name is php_pireps with 8 fields
    put "phpvms_pireps" into tTableName
    put "callsign, flightnum,depicao,arricao,flighttime,fuelused,comments" into tFields
    put char 1 to 2 of  "aircraft" into tAircraft


    
    -- construct the SQL 
    put "INSERT INTO " & tTableName & " (" & tFields & ") ("& tAircraft &")" into tSQL -- poss errors here
    
    -- send the SQL to the database, 
    revExecuteSQL gConnectionID, tSQL, 
    
    -- check the result and display the data or an error message
    if the result is a number then
        answer info "Success ! Your Pirep has been sent!"
    else
        answer error "There was a problem adding the record to the database:" & cr & the result
    end if

i get this error
compilation error at line 42 (Commands: bad parameter), char 33

Many thanks
Alex

[bangkok]

You have several issues here, both with SQL and with Livecode.

First advice : keep it simple.

What's the point to put your table name into a variable... and then use this variable to build your SQL query ?

Instead : put "INSERT INTO " & tTableName & "
Use : put "INSERT INTO phpvms_pireps"

Same with your columns names: put "callsign, flightnum,depicao,arricao,flighttime,fuelused,comments" into tFields
USe : put "INSERT INTO phpvms_pireps (callsign, flightnum,depicao,arricao,flighttime,fuelused,comments)"

Second :you have a big problem with put char 1 to 2 of "aircraft" into tAircraft
What's the point ?
aircraft is a constant or a field (with text that can be changed ?) With your SQL query it looks like it's a field !

Look at your structure :
put "INSERT INTO phpvms_pireps (callsign, flightnum,depicao,arricao,flighttime,fuelused,comments) ("& tAircraft &")" into tSQL

Basically you want SQL to insert the value "ai" [which is char 1 to 2 of the string "aircraft"] into 7 columns !

It can't work.

Instead you need to have something like :
-- take the values you want to insert from a serie of fields in LiveCode
put field "callsign" into tCallsign
put field "flightnum" into tFlightnum
......
put "INSERT INTO phpvms_pireps (callsign, flightnum,depicao,arricao,flighttime,fuelused,comments) ("& tCallsign &","&"flightnum& ..............)

Eventually the query will look like :

Code: Select all

INSERT INTO phpvms_pireps (callsign, flightnum,depicao,arricao,flighttime,fuelused,comments) ("AB","AF931","19:55","21:30","10:00","5645658","NICE FLIGHT")

This is good SQL.

Anyway. Other trap : beware of data type... It can be tricky with SQL. Check the datatype and the lenght of all your columns, and use LiveCode accordingly (what happens if your user inserts a comment 50 chars long into a SQL column of 30 chars ? Time format, number format [for the fuel used] etc.)

[maverickalex]

I was following the lesson on connecting to mysql in the lessons tutorial, your way seems simpler to follow.
I'll give that a try. thanks Bangkok

The put chars 1 to 2 of "aircraft" should have read field "aircraft" basically i have queried my database phpvms_aircraft to display the aircraft name to a user. however when submitting to the database the aircraft has a id number (as there are a number of the same aircraft type) i never expect to have more than 99 aircraft so thats why the 1 to 2 came about.
In rellation to the last part, the only parts of the app the user can add to is the comments, so i'll restrict the chars in the table

[bangkok]

The put chars 1 to 2 of "aircraft" should have read field "aircraft" basically i have queried my database phpvms_aircraft to display the aircraft name to a user. however when submitting to the database the aircraft has a id number (as there are a number of the same aircraft type) i never expect to have more than 99 aircraft so thats why the 1 to 2 came about.

Okay. So first, you query and put all the aircraft names into one field, right ?

And then, you want to make an insert with one aircraft name selected, but using its ID, right ?

A few tips :
-in your SELECT query add the aircraft ID like :
SELECT aircraftname,aircraftid from aircrafttable

that will give you a list, separated with commas and CR :
B747,101
B737,102
A380,103

put this list in your field (give to your field the attributes : locktext and list behavior)

And then for your INSERT query you can do :

put item 2 of the selectedtext of field "aircraft" into aircraftID --- that will put "102" [item number 2 of line 2] if the user has selected [highlited] the second line.

Anyway. Don't give up. SQL with LiveCode as a frontend is really a perfect solution.

[maverickalex]

OK i changed things around but keep getting a syntax error.



here is the app with the collected info to send to the db



My code now looks like so.

Code: Select all

 -- table name is php_pireps with 8 fields
    put item 1 of the selectedtext of field "aircraftused" into tAircraft
    put the HilitedText of field "flightnum" into tFlightnum
    put field "depicao" into tDepicao
    put field "arricao" into tArricao
    put field "flighttime" into tflighttime
    put field "fuelused" into tfuelused
    put field "comments" into tComments
    

     


    
    -- construct the SQL 
    put "INSERT INTO phpvms_test ("& tAircraft &")("& tFlightnum &")("& tDepicao &")(" & tArricao &")(" & tflighttime &")(" & tfuelused &")("& tComments &")"into tSQL

where could i be going wrong?

[bangkok]

where could i be going wrong?

Looking at your error message, I would say that you put (value)(value1) instead of (value1,value2)

Anyway.

The easiest way : put your SQL query in a field, just before you send it to the SQL server, and then paste it here on the forum.

We will be able to check your whole SQL insert query, and spot the problems.

[maverickalex]

I'm scratching around now.

Code: Select all

 put "INSERT INTO phpvms_test (aircraft,flightnum,depicao,arricao,flighttime,fuelused,comments)("& tAircraft & "," & tFlightnum & "," & tDepicao & ","  & tArricao & "," & tflighttime & "," & tfuelused & "," & tComments &")" into tSQL

then send the query

Code: Select all

revExecuteSQL gConnectionID, tSQL

i still get the same error

[bangkok]

Please, send the query generated by your script, not the script itself.

It's the only way.

Because, the error message is certain (coming from MySQL) : the query you send is wrong.

I can already tell you that if you have a column with CHAR datatype, you have to put the string you insert between single quotes etc (which you don't do, for the comment columns for instance).

Anyway. Post the query generated, and I will debug it for you.

[maverickalex]

Forgive my ignorance, but how can i show the generated query?

[BvG]

Code: Select all

put tSQL

then copy paste the contents of the message box into the forum.

[maverickalex]

Code: Select all

put tSQL

then copy paste the contents of the message box into the forum.

sorry but where do i put that?

[BvG]

into your script, instead of, or before executing the sql query

[maverickalex]

this is what the message box looks like,



so looking at that it seems the issue is with the first two variables?

i changed the script slightly to this.

Code: Select all

put char 1 to 2 of the HilitedText of field "aircraftused" into tAircraft
   put char 1 to 4 of the HilitedText of field "flightnum" into tFlightnum
   put field "depicao" into tDepicao
   put field "arricao" into tArricao
   put field "flighttime" into tflighttime
   put field "fuelused" into tfuelused
   put field "comments" into tComments
   
   
   -- construct the SQL 
   
   put "INSERT INTO phpvms_test ("& tAircraft & "," & tFlightnum & "," & tDepicao & ","  & tArricao & "," & tflighttime & "," & tfuelused & "," & tComments &")" into tSQL

now it seems to send the correct query.

INSERT INTO phpvms_test (31,111A,KWST,EGLL,00:04,19309.2734,hello)

but it still get the syntax error

[BvG]

First, you need to quote your data. use single quote to do it: '
Second, you can't just insert into a database directly, you need to tell it where to put it. So you need to specify which columns to insert into.
so the output could for example be be:

Code: Select all

INSERT INTO phpvms_test  (column1,column2,column3,column4,column6,column5) VALUES ('31','111A','KWST','EGLL','00:04','19309.2734','hello')

[maverickalex]

SUCCESS! well partial.
this

Code: Select all

 put "INSERT INTO phpvms_test (aircraft,flightnum,depicao,arricao,flighttime,fuelused,comments) VALUES ("& 'tAircraft' & "," & 'tFlightnum' & "," & 'tDepicao' & ","  & 'tArricao' & "," & 'tflighttime' & "," & 'tfuelused' & "," & 'tComments' &")" into tSQL

successfully sends to the database, However all that appears in the database are zero's that are sent??