SQL Script step update problem

[Bob476]

I'm working with an inherited config and have very raw SQL skills. I'm trying to update a script in my FM 5.5 db that imports from an external SQL table. The main issue is adding new fields to the SQL table, and then updating the FM script to import matching values.

Details:

The present config imports known matching fields from SQL to FM using a Filemaker script which operates through an ODBC plug-in. The plug-in and drivers reside on a client PC running FM 5.5.

I created new text fields in the SQL table and then created identical text fields in FM.

In the current ImportSQL script I'm looking to modify, it presently imports data from a set number of fields through separate script steps. Each matching Filemaker and SQL field is given a separate "Set Field" script step in which the calculation for the Filemaker field to be set is as follows:

External("SQL-getColumn", "")

I have added new fields in SQL table and new fields in Filemaker table with matching names. I then added duplicate script steps using the same calculation entry.

When new records exist with data in the new fields, it shows up in Filemaker with <EMPTY> in the matching fields.

Can anyone advise on a fix for this?

[Leb i Sol]

ODBC plugin?

no need for such a thing....there is builtin driver for SQL....

simply run Import script based on SQL like u have....

SLECT * form TABLE where Table.Field ="variable" or "field in FM"

and then Import...fields HAVE to match.

In this scenario SQL is "dominant field creator"...

to get FM to "dominate" u would :

U could use FM to CREATE tables and store that SQL in FM....then on import (it would BE SQL script not an Import script) refer to those fileds.

~ Create the table

create table MAINtbl

(

fld1 varchar(20) ,

fld2 varchar(20) ,

fld3 int ,

fld4 datetime

)

~create field

ALTER TABLE MAINtbl ADD COLUMN UnitPrice CURRENCY NOT NULL;

However, I would not relay on ODBC or DSN to do the table/column creation as stored procedures are much more powerfull and faster....the only other way I can think would be sending DDE and hopeing that SQL can execute stored procedures based on DDE...(not sure thought).

So to recap:

use your original approach if u dont mind manually adding created field to FM and then adjusting IMport script

OR

(I have not tested it)

use pure SQL with "Execute SQL" scirpt to create and alter tables + script to create a matching field in FM and then RUN an "import" script based on a *field*

where *field* would be the one containting entire SQL

UPDATE MAINtbl WHERE <new field from FM & in SQL> ="variable" or "field value"

Unfrtunatelly, I dont have an SQL server running here so I cant test it....but these would be the concepts to dynamic FM--DSN/ODBC--SQL--FM

All the best!

[Bob476]

Actually, I do need the plugin it seems. Only clients that have the plug-in installed are able to run the current script.

The solution was ultimately provided through SQL in modifying the stored procedures. Once I did that, the Filemaker side worked fine.

[Leb i Sol]

oops ODBC driver has to exist right...I assumed ur talking about FMpro not the "solution" as the install of FMPro adds ODBC by default