MySQL and FileMaker Pro 8 Syntax

[Jalz]

Hi Guys,

I'm doing a little bit of testing here regarding FileMaker and SQL based interaction. I've got a MySQL system uploaded onto a web server and I've created a DSN.

I've basically got 3 fields in each database (MySQL (table called employees) and FM (table called TestSQL));

name; Text;

dob; Date;

age; Int(11);

I wanted to test out the different field types and how both systems handle them.

I've created an Import via ODBC that maps the fields and imports the data from MySQL to FM. This was very easy with the SQL build wizard.

Unfortunately, the Execute SQL command (which I presume pushes the data out out of FM into a SQL backend). I cant figure out how to enter data from FM table to MySQL. Anyone kind enough to string along a query for me or tell me where I'm going wrong with the one below.

INSERT INTO employees(name,dob,age) Values (SQLTest::name & "," & SQLTest::dob & "," & SQLTest::age)

Edited November 3, 2006 by Guest

[Jalz]

Im getting closer!

Im using the following as a calculated text:

"INSERT INTO employees(name,dob,age) VALUES (' " &

SQLTest::name &

" '," &

SQLTest::dob & "," &

SQLTest::age & ")"

The first record seems to pushing through to the webserver. However, my date field doesn't seem to be uploading - do I need to do a conversion or something?

Secondly, this execute sql command seems to be uploading only one record, the one I'm viewing. If I wanted to upload a foundset, is it recommended that I create a loop to gather the string of SQL text in say a global field on variable and then execute sql command based on this global field? or is there a better way of uploading a foundset by just using SQL?

Any help much appreciated as always....this is good fun so far!

[Wim Decorte]

Execute SQL can be used to "export" data but you have to build the whole syntax to do that. It's not really what that command is for since it executes in the context of the record where you are. You need to use the Export script step to push your found set across. But you can use the Execute SQL if you build the whole syntax in the context of the record where you are.

[Jalz]

Thanks Wim,

I've figured out how to push the date now - had to format it the way MySQL was set up. This is the command I am using:

"INSERT INTO employees(name,dob,age) VALUES (' " &

SQLTest::name &

" ','" &

Year(SQLTest::dob) & "/" & Month(SQLTest::dob) & "/" & Day(SQLTest::dob)

& "'," &

SQLTest::age & ")"

I think I am going to explore your suggestion regarding using the export script step, to push records to MySQL. How can I export to ODBC source though - is this at all possible?.... or do you mean export it to an xml file and then manually import it to a MySQL table?

Thanks

Jalz

Edited November 3, 2006 by Guest

[Othni]

I am having a syntax problem also trying to export to MS SQL. I am not clear about how to reference the filemaker table and fields. Also how to separate the field names in the value portion of the INSERT INTO statement.

I am using Filemaker 6.

I will appreciate any help.

[Jalz]

Hi Othni,

I dont use FMP 6, so Im not too familar whether you have Execute SQL command.

Im a very beginner, so I'm definately not the right person to ask, but Ive managed to send data from an FM database into a MySQL database by using the following syntax

INSERT INTO employees(MysqlFieldName) VALUES (FileMakerFieldName)

Hope that kinda starts you off.

[Stuart Taylor]

This is so off topic i should be shot ... but

I've always wondered why it is correct English to say:

"A" FileMaker Database

but when abreviated you have to say

"An" FM Database instead of "A" FM Database

I know it just sounds wrong but why?

Should have paid more attention in English classes i suppose.

[Othni]

Thank you for answering. I do have the command Execute SQL.

But I noticed some "&", quotes, single quotes used which I wanted to figure out. The sample in the help file in version 8 is a little bit better about that than version 6.

[tgilders]

When a word starts with a vowel SOUND, it's correct to use "an", if not, you use "a"

"FM" - sounds like it starts with a short "e"

"FileMaker" sounds like it starts with an "f"

[Basso Tim]

I've been working on the same problem... I have not solved it yet. I have connected to my MySQL database using Actual Technologies' Driver and that seems to be working fine, but when I try to UPDATE the MySQL database from FileMaker it returns a MySQL syntax error. Any help would be appreciated.

I know that I can export as a CSV file and import into my MySQL database, but I would much rather have a convenient script!