Re: SQL Insert help

In article <Ef2dnXCmka_qPiTfRVn-gg@xxxxxxxxxxxx>, "Mark Durgee"
<mdurgee@xxxxxxxxxxxx> wrote:

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

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:

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

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
.

Relevant Pages

  • Re: creating and executing queries in VBA.
    ... Execute it into what? ... You can't just "execute" a select query. ... When you execute sql it has to go into a report, or a form or a record set. ...
    (microsoft.public.access.modulesdaovba)
  • LongestRunningQueries.vbs - Using a VB Script to show long-running queries, complete with qu
    ... Try this script to see what queries are taking over a second. ... you need a long-running query. ... Clicking on SQL opens the full SQL batch as a .txt file, ... filename = temp & filename ...
    (comp.databases.ms-sqlserver)
  • Re: Converting Perl Web Report to Python
    ... lets say that your sql script looks like this: ... This capability would allow each sql script to be extendable. ... How would you execute ...
    (comp.lang.python)
  • Re: UPDATE query in Access 2003 raising error
    ... Runtime error 3066 Query must have at least one destination field. ... The SQL works fine if I use it in the QBF Design mode. ... Set qdfTemp = db.CreateQueryDef ... Elsewhere in the code I use the same technique to execute an SQL ...
    (microsoft.public.access.formscoding)
  • Re: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT I
    ... This statement causes ADO to create a recordset to receive the results ... of the query being executed and assign that recordset to your qryAddProj ... You should use the ExecuteOptions argument of the Execute ... string containing a sql statement to be executed. ...
    (microsoft.public.data.ado)