Importing from Oracle

[PaulG]

I'm a bit rusty on FM but I've been given a project to do in FM 6 and I need a bit of help. The some of the data I need is in an Oracle database. I'm on a win2k platform. I've set up the DSN and that al works great. For a basic test I used the query

SELECT "WO"."CL_ID", "WO"."JOBDESC"

FROM "WO"

WHERE "WO"."WONUM" = '1000'

and that works fine.

What I want to do is to type a number in a field on my form called WO_NUmber and then click on a button to get the data for that work order. I can't figure out how to reference the FM field on my form in the SQL statement. I tried

WHERE "WO"."WONUM" = '"WO_NUmber"' and

WHERE "WO"."WONUM" = '["WO_NUmber"]' and

WHERE "WO"."WONUM" = '[getfield "WO_NUmber"]'

any help on this would be greatly appreciated.

Regards

Paul Geving

[email protected]

[LaRetta]

Hi Paul,

I believe you will need use an FM Calculation (text) which would utilize user-entered global WO_NUmber. Script will look something like ( untested )

# Create a global text called [color:black]g_WO_number. Then script ( fired when on target layout ):

# Ask the User for the WO Number

Show Custom Dialog [ OK ; Cancel ; "Enter WO_Number" ]

... input field > Specify ... would be g_WO_number

If [ Get ( LastMessageChoice ) = 2 ]

Halt Script

Set Error Capture [ On ]

Else If [ IsEmpty ( g_WO_Numer ]

Show Message [ OK ; "Pay attention silly - add a WO number"

Halt Script

Else

Import [ no dialog ; DSN ; driver ; SQL Text : ( see calculation formula below )

... here you will need to first time enter the query manually. Once mapped, exit the script and go back in and change it to the FileMaker calculation ... I know, we shouldn't need to do this and, once you point to the calculation, you will no longer be able to see the map - it sucks. But FileMaker won't store a map to an FM calculation based upon a SQL query. Hopefully they will in the future. The calculation text would be similar to this below( note that, since you are entering the SQL query into an FM calculation, you must escape out all the quotes and also surround it in quotes). You will have to play around to get it right but it will look something like:

"SELECT "WO"."CL_ID", "WO"."JOBDESC"

FROM "WO"

WHERE "WO"."WONUM" = " & [color:black]g_WO_number [color:gray]& "

You can also make it much easier by selecting ALL fields and then, during the import map, only select the fields you wish to actually import. I don't notice any speed difference between these two approaches. Your calculation would be simpler as:

"SELECT *

FROM "WO"

WHERE "WO"."WONUM" = " & [color:black]g_WO_number [color:gray]& "

Because you must meet FM specs for valid calculation, it complicates the process. But it also allows dynamic use of a FileMaker field value and that is the ONLY way of handling it. Unfortunately, SQL queries don't allow anything but SQL fields or FM calculation. This really limits unless you take advantage of FileMaker calculations.

UPDATE: Note that SQL Server is much nicer to work with than 10g because SQL Server syntax is simpler ( less single and double quotes ).

Double-Update: REMOVE THE GREY. Not only was it incorrect but the text already ended in quote (before the field).

Edited June 12, 2008 by Guest

[PaulG]

Thank you. I will give it a try in the morning. I don't have a choice on the DB as a matter of fact I'm doing this in NYC, the Oracle server is in Montreal and the FM server is in LA. It's amazing I had so little problem just connecting to the servers.

Regards

Paul Geving

[email protected]

[LaRetta]

Hi Paul, this morning I re-read the calculation. Please note removal of last quote ( portion in grey ) in my prior post.

[PaulG]

I had some time today to try what was suggested and was not successful at all. I tried to cut and paste the code snippet directly into the SQL window and my FM 6 did not like it at all. When I removed the slashes it seemed to almost work. I was wondering if there is a way to see what FM is translating the SQL string in before it sends it. With VB or C++ I would just pipe the string to a message box before it was transmitted but there doesn't seem to be anyway to do that with FM6. I think I'm close because I get the dialog box asking me to map the fields but I get no record of any kind back. Any help on this would be appreciated.

Regards

Paul Geving

[email protected]