import from SQL Server

[LVA]

I am working on a solution to pull data out of SQL Server database into FM v6.

I would like to make a script that will be activated automaticaly everyday using a plugin. The problem is that as it's going to be automatic I have to keep in a global field the last importing date so that when the script is activated again it doesn't import the same records. I know that programing in other languages it is possible to keep the last importing date in a variable and the query would be something like this :

SELECT * from customers where date > '"lastimport"'

lastimport would be the variable.

I can't find a way using FM to make an importing ODBC script use a variable.

Can someone help me ?

[Garry Claridge]

Check the use of Global fields as variables. Use the "SetField()" Script-step.

Hope this helps.

Garry

[LVA]

What I don't understand is how to use this global field in the Select statement. The query builder only shows the colums or values to be entered. How can I make the query builder have a global field in it ?

[JerrySalem]

the querry should be build in a text field. lets call the query SQLtext. and the date field startdate.

in the script that runs every day use the setfield command for example

setfield(SQLtext, "select invoicenumfrom invoicetable where invoicedate > " & startdate)

then do an import based on the variable SQLtext.

I hope this helps.

[LVA]

Thanks .

[cdenby]

All well and good, except how do you do a SQL statement using the field? That's what I've been struggling with. I can't seem to find any help on putting FM field content directly into any query.

[Garry Claridge]

From Jerry:

setfield(SQLtext, "select invoicenumfrom invoicetable where invoicedate > " & startdate)

This is in an FM Script. The field is "startdate". Other fields can be concatenated into the string with "&".

Hope this helps.

Garry

[cdenby]

So you mean to say that setting the field in a script step will set the import query for me... weird - how do I invoke the import then? There must be a place where the datasourcename, UID & PWD is set...

setfield(SQLtext, "select invoicenumfrom invoicetable where invoicedate > " & startdate)

[Garry Claridge]

The DSN, UID and Password are set when you create the Script step "Execute SQL" (not "SetField").

All the best.

Garry

[cdenby]

What plug ins or version do I need to do an "Execute SQL" script step? I can't seem to find it in filemaker 5.0