A little SQL Error ... And I fail to identify it!

[MasterchiefJB]

Hi again,


I am really sorry to ask you this and I feel really ashamed.
I think I just good to tired of writing code (writing Lifecode actually is easy and not brain cosuming compared with C++) but I looked at this piece of code
for 1 hour. It gives me an SQL Syntax error and I fail to identify it.

Maybe someone could tell me what I don´t see.
Basically this is just the sql code to insert a record into a table called mod.

This is the code to gather the required information:

Code: Select all

 put "mod" into tTableName
    put "modname, moddescription, modtype, uploaddate" into tFields
    put fld "ModName" into tModName
    put fld "ModDescription" into tModDescription
    put fld "ModType" into tModType
    put the short date into tUploadDate    -- this is nonsensical but gives some variation to the data

And this is the code to insert the SQL code (it´s also the code that gives the error!)

Code: Select all

   -- construct the SQL - the :1, :2 & :3 placeholders in the SQL will be filled by variables in the revExecuteSQL line
    put "INSERT INTO " & tTableName & " (" & tFields & ") VALUES (:1, :2, :3, :4)" into tSQL
    
    -- send the SQL to the database, filling in the placeholders with data from variables
    revExecuteSQL gConnectionID, tSQL, "tModName", "tModDescription", "tModType", "tUploadDate"

I appreciate any help fixing this issue!

Kind Regards,
Masterchief

[bangkok]

First, what is the SQL error message you got ?

you should try an easier script, instead of using variables :

Code: Select all

 put "INSERT INTO mod (modname,uploaddate) VALUES ("toto","2010-12-25") into tSQL
revExecuteSQL gConnectionID, tSQL

Then I see a second source of problem : the date format you're using.

With LiveCode :

Code: Select all

put the short date

will make : "12/27/10"

and this is not going to work with a date format or timestamp column in SQL....

You should write : "2010-12-25" or "2010-12-25 00:00:01" if your SQL column is a timestamp.

[MasterchiefJB]

Thank you very much for your help bangkok!

I tried your suggested short version

With this code:

Code: Select all

 put "INSERT INTO mod (modname,uploaddate) VALUES (toto,2010-12-25)" into tSQL
revExecuteSQL gConnectionID, tSQL

And that´s what I get back:

There was a problem adding the record to the database:
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to
use near 'mod (modname,uploaddate) VALUES (toto,2010-12-25)'
at line 1

I have to admit that I had to change your code by removing the ""s around toto and 2010-12-25
otherwise Revolution would have complained about bad syntax aswell.

The original code:

Code: Select all

put "INSERT INTO mod (modname,uploaddate) VALUES ("toto","2010-12-25") into tSQL

I don´t have a clue where to find the source of this problem.

[splash21]

Try single quotes round the values;

put "INSERT INTO mod (modname,uploaddate) VALUES ('toto','2010-12-25')" into tSQL

[bangkok]

Try single quotes round the values;

put "INSERT INTO mod (modname,uploaddate) VALUES ('toto','2010-12-25')" into tSQL

Yes, I'm sorry. I typed too fast.

You have to use single quotes indeed within the SQL query.

And don't forget to check the exact data format of your column "uploaddate" with a SQL editor (date format or timestamp format).

[MasterchiefJB]

Thanks for the advice! Unfortunately it doesn´t solve the error.

This is the code:

put "INSERT INTO mod (modname,uploaddate) VALUES ('toto','2010-12-25')" into tSQL

And the error still stays the same, only thing that changed the error also displays the single quotes around toto and the date.

I checked the uploaddate column in the table:Currently it says date but I tried it with timestamp (ít doesn´t change the error msg).

[splash21]

What database are you using? Many (like MySQL) have a function 'MOD' (modulo).
Try using `mod` for the table name with reverse quotes if it's MySQL.

[MasterchiefJB]

I don´t think I understood your suggestion but I changed the table name of my MySQL database to mods and I am still getting the same error.

Could you give me an example how to use the reserve quotes.
I guess they should be used like this:

Code: Select all

put "INSERT INTO `mods` (Mod Name,Upload Date) VALUES ('toto','2010-12-25')" into tSQL
revExecuteSQL gConnectionID, tSQL

Unfortunately it results in the same error.

[splash21]

Here are my results straight from the MySQL command line;

Code: Select all

mysql> create table mods(modName varchar(16) not null, primary key(modName), updateDate date not null);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO mods (modName, updateDate) VALUES('toto', '2010-12-25');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM mods;
+---------+------------+
| modName | updateDate |
+---------+------------+
| toto    | 2010-12-25 | 
+---------+------------+
1 row in set (0.00 sec)

create table `mod`(modName varchar(16) not null, primary key(modName), updateDate date not null);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `mod` (modName, updateDate) VALUES('toto', '2010-12-25');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM `mod`;
+---------+------------+
| modName | updateDate |
+---------+------------+
| toto    | 2010-12-25 | 
+---------+------------+
1 row in set (0.00 sec)

Syntax is OK - could there be a small typo anywhere in your code?

[interactbooks]

I noticed you have spaces in your field name i.e. "Mod Name" instead of "ModName". I am not very familiar with mySQL but with SQL Server you would need to enclose the field name with square brackets [Mod Name]. You can try that or just make your field names a single word which might improve the readability of the SQL statements anyway.

[bangkok]

Here are my results straight from the MySQL command line;

Code: Select all

mysql> SELECT * FROM mods;
+---------+------------+
| modName | updateDate |
+---------+------------+
| toto    | 2010-12-25 | 
+---------+------------+
1 row in set (0.00 sec)

Syntax is OK - could there be a small typo anywhere in your code?

So, we are making progress ! Your SQL table is normal. And your insert is okay.

So now it should work within your LiveCode script :

Code: Select all

put "INSERT INTO mods (modname,uploaddate) VALUES ('hello','2010-12-26')" into tSQL
revExecuteSQL gConnectionID, tSQL
put the result into tResult
answer tResult

[MasterchiefJB]

Well I think we solved it by now!
This is what I did:
1. I deleted all the spaces in the field names i.e. "ModName" instead of "Mod Name"
2. I added bangkok´s code

Code: Select all

put "INSERT INTO mods (ModName,UploadDate) VALUES (toto','2010-12-25)" into tSQL
revExecuteSQL gConnectionID, tSQL
put the result into tResult
answer tResult

3. I recieve the following messages:
First Message:

1

Second Message:

Code: Select all

There was a problem adding the records to the database:

Well the second message is caused by this check script:

Code: Select all

 
    -- check the result and display the data or an error message
    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

So I don´t know why I get the error message as the result is a number!??
Edit: I solved this little error by using this code

Code: Select all

if tResult is a number then

instead of

Code: Select all

if the result is a number then

so now I get the message: "New record added."

4. Most Important: The record is successfully added to the database! I can see it in my mysql console!


Little question by the side:
What would I have to do to display every record of a row into a "Scrolling List Field"?
- I tried to archieve this by using the Inspector -> Database -> Link -> Column: ModName. But with this method only the first entry is shown. I would like every entry to be displayed.

[bangkok]

Well I think we solved it by now!
3. I recieve the following messages:
First Message:

1

Second Message:

Code: Select all

There was a problem adding the records to the database:

This is perfectly normal.

Don't forget : your column ModName is the primary key. It means you can not create 2 records with the same value ("toto") in ModName column.

[MasterchiefJB]

Thanks for the advice!

I changed the primary key to a column called ID which will auto increase each time a record is added, so now everything works as it should!

Thank you very much for your help! Everybody! After two days I was able to get my app working and now I am almost at a point to release it!


One thing I would like to know:
How could I create a scrolling list field which lists all the records of a specified column and if the user selects one record the other fields are automatically filled with the corresponding information of the record.
I.E: I have a record with Mod name, Mod Description, Mod Version, Upload Date
I would like to list all Mod names in a field and if the user selects a mod name I would like the corresponding mod description, version and upload date to be displayed in other fields.

[bangkok]

I.E: I have a record with Mod name, Mod Description, Mod Version, Upload Date
I would like to list all Mod names in a field and if the user selects a mod name I would like the corresponding mod description, version and upload date to be displayed in other fields.

Something like that.