ODBC Select query from MySQL

[MattPage]

Hi all,

Simple question I hope.

Setup Filemaker Server 8 with ODBC connection to our online web server with MySQL. I can INSERT into MySQL directly from a script on FM ... very handy!

But, what happens to the data from a SELECT query using the standard Execute SQL script step?

Is it possible to feed the results into variables.

I know about the import from ODBC options but need some more control over the data. I guess importing everything into a temp database and then work from there would work, but not ideal.

Thanks

Matt

[mattley]

I know about the import from ODBC options but need some more control over the data. I guess importing everything into a temp database and then work from there would work, but not ideal.

Why not ideal, that's how I do it, and it works well for me.

[NYPoke]

Not only is it not Ideal, it is a pain in the arse. I'm not happy about this.

How on earth can the FM Designers allow us to run a Select Statement & not get our @#!#!#% results.

Another related question. Can we do the reverse?

SELECT "DialerNotes"."NoteId", "DialerNotes"."Note", "DialerNotes"."NoteDateTime"

FROM "DialerNotes"

WHERE "DialerNotes"."NoteId" > ???FM_ID??

Can I put one of my FM fields (???FM_ID??) into the Select Where clause?

If not, then I'm just stumped as to why they put SQL in the Scripting.

Edited February 21, 2006 by Guest

[MattPage]

Not only is it not Ideal, it is a pain in the arse. I'm not happy about this.

How on earth can the FM Designers allow us to run a Select Statement & not get our @#!#!#% results.

Another related question. Can we do the reverse?

SELECT "DialerNotes"."NoteId", "DialerNotes"."Note", "DialerNotes"."NoteDateTime"

FROM "DialerNotes"

WHERE "DialerNotes"."NoteId" > ???FM_ID??

Can I put one of my FM fields (???FM_ID??) into the Select Where clause?

If not, then I'm just stumped as to why they put SQL in the Scripting.

I think the select issue actually makes sense, it is not much use getting raw data back unless it is in some sort of table.

You can put your FM fields into the query, you can specify the query to be a calculation. It seems like quite a powerful feature.

[NYPoke]

I would be thrilled with just putting my FM Data into the Where Clause.

Can it be done in Version 5.5/6?

If so, what is the syntax?

I'm doubting that 5.5/6 will handle it, but if they do, I would greatly appreciate help with the syntax.

[NYPoke]

Any examples of Version 8 using FM Fields in a Where Clause would be helpful as well. I'm looking, but don't see anything obvious in the V8 Calculations either.

[MattPage]

Not sure about ver 5 or 6. You will need a MySQL ODBC driver (if you are connecting to a MySQL database) - Google for mysql odbc

There should be a script step called Execute SQL , select the ODBC connection and enter a few details such as username and password.

Then you can enter a plain query or use a calculation, one of mine looks like this...

Execute SQL



"INSERT INTO TO_Events 

VALUES(

'" &  Events::event_id & "',

'" &  Escape_MySQL_Characters ( Events::event_title ) & "',

'" &  Escape_MySQL_Characters ( Events::event_tagLine ) & "',

'" &  fmDate_to_MySQL_Timestamp8 ( Events::event_startDate ) & "',

'" &  fmDate_to_MySQL_Timestamp8 ( Events::event_endDate ) & "',

'" &  Events::event_minimumAge & "',

'" &  Events::event_website & "',

'" &  Events::event_contactName & "',

'" &  Events::event_contactAddress & "',

'" &  Events::event_contactEmail & "',

'" &  Events::event_contactWeb & "',

'" &  Events::event_contactTelephone & "',

'" &  Events::event_contactFax & "',

'" &  Events::event_contactTicketLine & "',

'" &  Events::event_venueName & "',

'" &  Events::event_venueAddress & "',

'" &  Events::event_venueEmail & "',

'" &  Events::event_venueWeb & "',

'" &  Events::event_venueTelephone & "',

'" &  Escape_MySQL_Characters ( Events::event_termsAndConditions ) & "',

'" &  Escape_MySQL_Characters ( Events::event_description ) & "',

'" &  Events::event_sourceCodes & "',

'" &  Events::event_offerCodes & "',

'" &  fmDate_to_MySQL_Timestamp8 ( Events::event_ticketNextDispatchDate ) & "',

'" &  fmDate_to_MySQL_Timestamp8 ( Events::event_ticketOfficeOpens ) & "',

'" &  fmDate_to_MySQL_Timestamp8 ( Events::event_ticketOfficeCloses ) & "',

'" &  fmDate_to_MySQL_Timestamp8 ( Events::event_ticketLastPostDate ) & "',

'" &  Events::event_ticketBuyOnDoor & "',

'" &  Events::event_ticketAllowCollectOnDoor & "',

'" &  Events::event_ticketAllowGiftPurchase & "',

'" &  Events::event_allowInvoice & "',

'" &  Events::event_ticketUrl & "',

'" &  Events::event_staffPasscode & "',

'" &  Events::event_billingCurrency & "',

'" &  Events::event_holdUpdate & "'

)"

If you are doing a SELECT then you must be doing an Import Records script step, you select the ODBC connection as the source and you can use the query builder to make the SQL...

"SELECT * 

  FROM TO_Orders 

  WHERE order_complete='1' 

  AND order_eventID = '" & Events::event_id & "'"

[NYPoke]

Okay, we are getting closer (Thank You). I am connecting to SQL Server & have a Select Query working.

In your Query, does "Events::event_id" refer to a FileMaker File named Events & a field within that file event_id?

That is the syntax I am missing - how to reference my own data.

[MattPage]

Yes thats right. I have a Filemaker file / table called Events with a field called event_id ... etc

You can either type them in or select from the table/field list in the calculation dialog box.

To make things easier (maybe) I also use the same field names for the SQL database, then on an import you can do 'matching field names'

Edited February 22, 2006 by Guest

[NYPoke]

Okay, I gots it. I could certainly use the Select Statement to bring data into FM, but I can live without it.

I didn't get the the Calculation angle at first, so if you don't either, you build the SQL Statement in a Calculation. That way, you can pull values from your FM database & place them in the SQL String. The Field Names never make it into the SQL Statement, but the values do.

Originally, I thought that we executed the SQL in a Calculation - which just didn't make sense.

For the lost souls that run across this post, here is an example in FM 6, connecting to a SQL Server Database.

Here is the Calculation, that produces a Text Result:

"UPDATE DialerNotes " &

"SET DialerNotes.Note = 'I Updated This From FileMaker'" &

"WHERE DialerNotes.NoteId = " & DialerId

Dialer Notes is the SQL Server Table

Note is a field in the SQL Server Table

NoteId is a unique id in the SQL Server Table

DailerId is a field in my FM file

You then use this Calculation in an Execute SQL Script Step.

[NYPoke]

Major thanks to MattPage for helping me understand.

[MattPage]

Glad I could be of help. :beer: