creating a mysql table

[Da_Elf]

I want to use this mysql code with livecode

Code: Select all

SET sql_notes = 0;
CREATE TABLE IF NOT EXISTS user(personid int(4) NOT NULL auto_increment, personname varchar(255), PRIMARY KEY(personid) );
SET sql_notes = 1; 

i get an error in my sqlcode even though this sql code works perfectly when inside my mysql editor when i do it like this though

Code: Select all

on mouseUp
   addTable
end mouseUp

on addTable
  global gConnectionID
  put "SET sql_notes = 0;" &return into sqlCode
  put "CREATE TABLE IF NOT EXISTS user(personid int(4) NOT NULL auto_increment, personname varchar(255), PRIMARY KEY(personid) );" &return after sqlCode
  put "SET sql_notes = 1;" after sqlCode

   revExecuteSQL gConnectionID, sqlCode
   if the result is a number then
      answer info "New table added."
   else
      answer error "There was a problem adding the table to the database:" & cr & the result
   end if
end addTable

[phaworth]

The dictionary has a somewhat cryptic instruction: " (Do not include a semicolon at the end of the SQLStatement.)". Don't know if that means at the end of every statement in a multi-statement block or just the last one, but it's probably the key to your error.
Pete

[Da_Elf]

the error im getting seems to complain about line2 which is the create table line. line one has a semicolon and it seems to be fine with that. ive tried removing all semicolons then just the one at the end. i still get the same error saying to check my mysql code

[phaworth]

Weird. Only thing I can suggest is leave all the semicolons in except the last one.

I did notice the CREATE TABALE listed in the error message sin;t the same as the one you listed in your code snippet?

Pete

[Da_Elf]

i had already made a table called "users" so i could continue with other stuff. the code is exactly the same except im trying to create a table called bob just for trial purposes. Ive already tried leaving all semicolons except the last. none of it seemed to work

[Da_Elf]

ok figured it out. i couldnt do all at the same time. so i did line one. then sent the SQL code, then line two then sent the SQL code ...... etc. it works now. phew