Updating source database during an import

admin — Fri, 31 Oct 2008 19:06:59 GMT

Occassionally, it is necessary to update the source or target database during an import operation. For example, it may be necessary to flag a source record as processed. Inaport supports this using the "dbexecupdate()" function, that allows you to perform an arbitrary SQL UPDATE statement against either the source or the target database.

The general format of the function is:

dbexecupdate("S" or "T", "UPDATE table SET field = 'value' WHERE key = 'keyvalue'")

The "S" or "T" specifies the source or target database respectively.

The second parameter is a string that needs to resolve to a legal SQL UPDATE statement for the database. The string can be built up using stanard inaport expressions. For example, if the time is 10:22:34 and the field keyfield has the value "aa123", then the string:

dbexecupdate("S", "UPDATE table SET field = '" & nowstr("HH:mm:ss") & "' WHERE key = '" & #keyfield & "'")

will produce a SQL UPDATE that looks like this:

"UPDATE table SET field = '10:22:34' WHERE key = 'aa123'"

NOTE: It is important to note the sequence of single and double quotes. There are two things going on:

The single quotes need to be positioned to produce legal SQL
The double quotes are marking the boundaries of strings that are joined together by the expression evaluator to produce the SQL.

So the piece of text: key = '" & #keyfield & "'"
is really: key = SQ DQ & #keyfield & DQ SQ DQ

NOTE FOR EXCEL: Excel requires that the worksheet name end in "$", and be surrounded by the "`" character - this is NOT a single quote. So the above example for Excel would be:

dbexecupdate("S", "UPDATE `table$` SET field = '" & nowstr("HH:mm:ss") & "' WHERE key = '" & #keyfield & "'")

Knowledge Base

Updating source database during an import