What is the proper syntax for putting a Filemaker field in an SQL statement?

Assumptions:
- "company" is an SQL database my CAD uses
- "companyName" is one field in that database
- The field name in Filemaker is "Name" and is text
- I want to insert a record in "company" with the value from Name

I have constructed this so far with a little help from the Filemaker help
file:

"INSERT INTO company (companyName) VALUES ( "'" & Name & "'" )"

This keeps giving me an error. When I hard-code the values it works. What
are the rules for surrounding the Filemaker field names between the
parathesis?


Thanks,
Mark Durgee

Figured it out, this should be done in a calculated field.


"Mark Durgee" <mdurgee (AT) nothanks (DOT) net> wrote

In article <Ef2dnXCmka_qPiTfRVn-gg (AT) giganews (DOT) com>, "Mark Durgee"
<mdurgee (AT) nothanks (DOT) net> wrote:

Where, exactly, are you placing your query syntax? Directly into the
Execute query script function, or someplace else (a global field, for
example) where it could by grabbed by the query script?

I've wrestled with this a bit and for my purposes, it is easiest to set
the query into a global text field, g_InsertQuery, via script (more
control over debugging & altering things for future new purposes). My
Execute SQL script function simply uses the global field as its query.

What I would do with your example is make a looping script in FM that
starts with a found set of all FM records where you need to insert records
with "Name" into SQL. If this is tens of thousands, it might take a
while. If it is dozens, or even hundreds, it should be pretty much
instantaneous. You loop through the set and the script in turn sets
g_InsertQuery with the proper syntax plus that record's "Name", execute's
the query, inserting a single record, then moves on to the next FM record.

If you are already doing something like this, sorry. You didn't tell us
much about how you were doing it, and I'm just thinking out loud.

Where you can get tripped up is setting g_InsertQuery with the correct
syntax via script. The script function is a simple Set Field (result
text).

INSERT INTO company (companyName) VALUES ('TestData1')

This is how the actual query should look, right? TestData1 being the
contents of one FM record's Name field. If so, from the Set Field
function's point of view, you are setting g_InsertQuery with a string of
text, followed by a field, followed by a second string of text. The first
string of text is everything up to the field name, within double quotes:

"INSERT INTO company (companyName) VALUES ('"

Then the field name, between ampersands, no quotes:

& Name &

Then the remaining text string, everything after the field name, within
double quotes:

"')"

Put it all together and you have:

"INSERT INTO company (companyName) VALUES ('" & Name & "')"

Compare to yours:

And you will see that you have too many double quotes. FM gave an error
because it didn't know where a text string ended and a field name began.

The actual Execute SQL script step would be to execute g_InsertQuery.

If your query is more complex, with several FM fields included, you can
get lost easily in clusters of double & single quotes. I know I have!

Steve Brown