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..

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

i have been waiting for 3 days..

thanks

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

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

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.

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.

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.

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

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.

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

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.

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

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.

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.

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.