Jump to content

ODBC Select query from MySQL


MattPage
 Share

This topic is 5689 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 & "'"

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5689 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.