Importing Data from SQL to FM -ODBC or SQL-Plugin

[Hassan Ali]

Hello all,

i am very new to the FM and i have v.4.1.

- To be very straight, i want to import data from SQL server to FM pro using ODBC which i was able to do but i am unable to pass dynamic sql i mean dynamic parameters while using ODBC dialog which has the title "specify ODBC sql query for <odbcname>", for intance i want to get records from a table having serial_no > x and i am not able to pass the parameter here.

- I have tried it using sql plugin where i am able to contruct the sql query dynamically, got connection to sql Db and executed the query using SQL Plugin commands like

Execute("SQL-Open","odbc|uname|pass") -to establish connection

Execute("SQL-execQuery","select query") -run a query on the open DB connection

but i am unable to fetch the data from the query results and unable to use the commands like

Execute("SQL-getRow","") -to get a row

Execute("SQL-getColumn","") -to get a column

please also guide me on how to pull data from sql server's different tables on one layout of FM.

can i pull data from more than 1 table on a single lay out?

these tables will be independent of each other.

this is very urgent...

waiting for the replies with some solutions...

thanks all in advance..

[Hassan Ali]

is there no body who can reply to this post...

i have been waiting for 3 days..

thanks

[Vaughan]

Be patient, we're waiting for your cheque to clear.

[Hassan Ali]

i am sorry, i did not get what you mean by "waiting for my cheque to clear"

[Anatoli]

Just a joke

You will probably wait forever, because:

a) nobody knows the answer

: FileMaker and ODBC is not really that much used. It is slow and usually doesn't work.

[Vaughan]

You get what you pay for. Which is why all of the free services I offer come with a 100% money-back guarantee, no questions asked.

[kenneth2k1]

That was funny. Check's in the mail!

By the way, I have NEVER been able to get FM to recognize an ODBC data source driver - Not from Excel, from Access, nothing. It pisses me off. But thats probably because my setup is wrong somewhere. This is the only aspect of FM that I have run into where following the manual verbatum has not worked. Plus it skips steps and is vague.

[SteveB]

From my understanding (limited), FM supports ODBC v1.0. Everybody else is working on v2.0 at least. As usual the're a dollar short and a day late. If they supported the 2.0 spec thoroughly, we could interface to PDAs through their ODBC conduits, and also other SQL compliant DBs.

Steve

[Hassan Ali]

may be my badluck,

is there any body who can help me out with the SQL-Plugin "External" commands

which i have given a couple of samples..

i am unable to fetch data using SQL-Plugin's commands,

if anybody is having some kind of script, which i am sure people are having here, since i have read in someposts (don't remember exactly), kindly

post it or mail me at [email protected]

Thank you very much one and all.

Hassan Ali.

[_ian]

What error messages are you getting with those commands? Are you using the scripts from the sample files?

Ian

Hassan Ali said:

- I have tried it using sql plugin where i am able to contruct the sql query dynamically, got connection to sql Db and executed the query using SQL Plugin commands like

Execute("SQL-Open","odbc|uname|pass") -to establish connection

Execute("SQL-execQuery","select query") -run a query on the open DB connection

but i am unable to fetch the data from the query results and unable to use the commands like

Execute("SQL-getRow","") -to get a row

Execute("SQL-getColumn","") -to get a column

[Hassan Ali]

No errors !! but i am unable to fetch the record after executing the query using "External("SQL-execQuery","select...")

after this i am not able to use the getRow,getColum or exportRows to read the recordset and insert it into the Filemaker.

my main aim is to fetch a set of new records from SQL server and insert it into FilemakerPro.

please guide me further.

Thank you very much one and all.

Hassan Ali.

[Hassan Ali]

I HAVE SUCCESSFULLY DONE WITH exportRows , there was some problem with my script.

NOW I HAVE GOT A new PROBLEM THAT IS, I AM FETCHING ONLY NEW RECORDS FROM THE DATABASE AND EXPORTING IT TO A TAB FILE USING THE exportRows command of SQL Plugin, BUT THE NEXT TIME I RUN IT FOR THE NEW RECORDS, THE exportRows command

is recreating the TAB FILE (EXPORT.TAB) WITH THE NEW RECORDS AND DELETING THE OLD ONES.

ACTUALLY I WANT TO APPEND THE NEW RECORDS INTO THE TAB FILE AND SHOW IT IN THE

FM FILE.

i don't know how do i do it.

there is one more command called appendRows in SQL-Plugin but it also overwrite the records..

can anyone helps me to find a solution ?

Thank you very much one and all.

Hassan Ali

[omiossec]

The easiest way to do that is not to import data from Sql Server, but export data from sql server to fmp using a DTS task.

[Jelte]

ok try this

i use the same plugin as you and i have no problems what so ever

what i do is i use a select statement to get the rows from the Dbase that i want

useing the

i set the field QUERY for instance with this.

"Select customer, custID, adres

where custID = '" & FIELDNAME & "'"

External("SQL-execQuery",QUERY&"|a|C:sql.tab")

Now i import the sql.tab in my filemaker dbase.

After changing the data i do an update

I set the field query with

"Update CustomerID with " & FIELDNAME & "Where customer name is piet"

External("SQL-execQuery",QUERY&|a|C:|sql.tab")

When someone makes a new record i just use an field where i enter if it was an imported record or a newly created record.

This system works fast and great. Making filemaker a client on a sql server.

Although i wouldnt use it in a place where you have to connect more then 1 computer using filemaker.

Good luck with this.

[DonH]

There is a not so elegant work around that I have used.

You use FM to create your query and export it to a .txt file. Then you use another method (ASP, CF, JAVA) to read the query from the file, execute it, save the results back to a tab delimited file, and then have FM import the results.