Mysql database

[arunkumar]

Hi,

When i try to retrieve table from MySql database after creating all the connections in database connection and selected the " Load a table form resource " from the events tab for the table , and when i run the form i get this error

Code: Select all

 fetchTable command issued on a non-resource backed table 130

. How can i retrieve a table from external MySql database. FYI, The columns from the external MySql database has been imported into the tables field.

[Jon]

The SQL is configured in a Database Resource which also contains resource fields. In the form, the table is linked with the resource and the table's fields are mapped to the Database Resource fields. The message is telling you that this link (table <> resource) is missing.

[arunkumar]

Hi,

I am not able to update my external MySQL database from my form , I have created a update button and FPL script to update . When I click the update button I get the following error

SQL error updating database source DATA update TEST.data set what=&&{what}, [this is]=&&{this is}, data=&&{data}, id=&&{id} - 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 '[this is]='ASD', data='ASDF', id=9' at line 1

[Jon]

Much better if you can to not have column names with spaces in them. Also, clicking the Use Prepared Statements option in the Database Resource may help.

[arunkumar]

I created a database with column names without space as you have mentioned, Now I am able to update the table but when I click add row and fill the rows in the table and click update, Rest of the table data in the database is also replaced with the last updated data showing the whole database repeating the same row values for the whole table.
when I give a primary key to the ID column and add data into the table in the form and i click update I get the following error,

SQL error updating database source SPACE1 update test.space set column3=&&{column3}, column2=&&{column2}, column1=&&{column1}, id=&&{id} - Duplicate entry '4' for key 'PRIMARY'

I have not entered a duplicate value for the ID ,Now how can i make the form create a new row in the database instead of replacing the whole table with with the latest updated datea when I click the update button.

[Jon]

Updatetable works like this:

If one or more columns in the Database Resource are marked as Unique Key, they will be used for the WHERE clause of the update statement. However, if you do this, then these primary key columns cannot be changed by the user i.e. they must be display only. If not you stand the risk of duplicate key or other errors. If the Unique Key column is in fact not unique then updatetable can update multiple rows in the database,

If no columns are marked as Unique Key, then all table columns are used in the where clause.

You can turn on the debug option in the Database Resource to see the SQL.