postgreSQL.cursor.executeQuery

Syntax

postgreSQL.cursor.executeQuery (cursorRef, query, params)

Params

cursorRef is a reference to a cursor as returned by postgreSQL.cursor.create.

query is a string containing the SQL code to be executed.

params is an optional list, record, or address of a table containing parameters to be substituted into the query string.

Action

If specified, substitutes params into the query string, and executes the query on the database server.

Returns

The number of rows in the result set, or -1 if the query did not generate a result set.

Examples

postgreSQL.cursor.executeQuery (cursorRef, "SELECT CURRENT_TIMESTAMP")

« 1 //result set is one row with one field containg a date value, e.g. "03/04/2002; 8:01:34 PM"

postgreSQL.cursor.executeQuery (cursorRef, "SELECT {#1} * {#2}", {3, 4})

« 1 //result set is one row with one field containg the number 12

local (paramTable)

new (tableType, @paramTable);

paramTable.firstname = "Ian"

paramTable.lastname = "O'Sullivan"

paramTable.age = 48

postgreSQL.cursor.executeQuery (cursorRef, "INSERT INTO friend VALUES ({$first}, {$last}, {$age})", @paramTable)

« -1 //insertion does not generate a result set

Notes

If params is not specified, query will be sent to the server for execution unchanged.

If params is a list, record, or the address of a table, query will be parsed for placeholders to be substituted with values from params. In that case, the sub-string "{#n}" in query will be replaced with the nth item from params. The sub-string "{$foo}" will be replaced with the item named "foo" from the params record or table. If query contains a placeholder for a named parameter but params is a list, a script error will be generated since list items can only be referenced by their numerical index.

When substituting values from params, UserTalk data types will be converted to corresponding string representations of PostgreSQL data types. String representations will be automatically enclosed in single quotes if neccessary, and single quotes or backslashes contained in substituted parameters will be escaped automatically. The UserTalk nil value corresponds to the SQL NULL value.

For the data type conversion to work correctly, the PostgreSQL extension automatically sets the date style for each connection to ISO, and you should refrain from changing it, e.g. by running a SET DATESTYLE command over the connection.

SQL commands like SELECT generate a result set and the return value of this verb will be a number equal to or greater than nil. You can retrieve the result set by calling the postgreSQL.cursor.fetchOneRow, postgreSQL.cursor.fetchMultipleRows, or postgreSQL.cursor.fetchAllRows verbs. You can use postgreSQL.cursor.getDescription to obtain information about the columns of the result set.

SQL commands like INSERT or UPDATE do not generate a result set and the return value of this verb will be -1. You can retrieve the OID of the last row affected by the command via postgreSQL.cursor.getLastOID.

If an error occurs while executing the query, the current transaction will automatically be rolled back.

If you want to execute multiple queries using the same query string but different values for the params list or record, you can use the postgreSQL.cursor.executeMultipleQueries verb.

Caution: Since the DLL interface is somewhat limited as far as resolving addresses and obtaining table values are concerned, I have had to resort to a bit of a hack to look up params by name in the table. The DLL constructs a string beginning with the string representation of the table address, followed by a dot, and the param name specified inside the curly braces in the SQL command. This string is evaluated via the DoScript callback of the DLL interface. In theory, this allows you to execute arbitrary UserTalk code as part of the param name. Suppose the address of the table is @paramTable.postargs and the SQL command contains the param reference {$username}, then the DLL would basically evaluate the string "paramTable.postargs.username" as UserTalk code. On the one hand, this approach allows you to also reference items in sub-tables of the param table by using a param name like {$data.filename} in the SQL command. On the other hand, a param name like {$foobar; delete(@someTable)} would actually delete someTable in Frontier.root and just substitute true for the param value.

See Also

postgreSQL.cursor.create

postgreSQL.cursor.getDescription

postgreSQL.cursor.getLastOID

postgreSQL.cursor.fetchOneRow

postgreSQL.cursor.fetchMultipleRows

postgreSQL.cursor.fetchAllRows

postgreSQL.cursor.executeMultipleQueries

postgreSQL.cursor.callProcedure