@MySQLCreatePreparedStatement
Syntax
@MySQLCreatePreparedStatement(MySQLHandle;QueryStr;DataTypes;MaxLengths);Description
Creates a PreparedStatement query.
Such a query represents a kind of template that can then be completed with data to form a complete query and executed.
This function is intended for repeated execution of similar queries.
For example, to write data to a MySQL database.
TEXT QueryStr contains the query or query template.
Example:"INSERT INTO obs.access (ID,Name,Access,CrtUserWithProfs) VALUES (?,?,?,?)"
FN DataTypes contains the data types of the parameters corresponding to the VALUES list specified in the query.
Possible types:
Value Type
000 MYSQL_TYPE_DECIMAL
001 MYSQL_TYPE_TINY
002 MYSQL_TYPE_SHORT
003 MYSQL_TYPE_LONG
004 MYSQL_TYPE_FLOAT
005 MYSQL_TYPE_DOUBLE
006 MYSQL_TYPE_NULL
007 MYSQL_TYPE_TIMESTAMP
008 MYSQL_TYPE_LONGLONG
009 MYSQL_TYPE_INT24
010 MYSQL_TYPE_DATE
011 MYSQL_TYPE_TIME
012 MYSQL_TYPE_DATETIME
013 MYSQL_TYPE_YEAR
014 MYSQL_TYPE_NEWDATE
247 MYSQL_TYPE_ENUM
248 MYSQL_TYPE_SET
249 MYSQL_TYPE_TINY_BLOB
250 MYSQL_TYPE_MEDIUM_BLOB
251 MYSQL_TYPE_LONG_BLOB
252 MYSQL_TYPE_BLOB
253 MYSQL_TYPE_VAR_STRING
254 MYSQL_TYPE_STRING
255 MYSQL_TYPE_GEOMETRY.
FN MaxLengths specifies the maximum field sizes of the parameters corresponding to the VALUES list specified in the query.
Return:
On success, a generated VSPECMYSQLPREPSTAT; otherwise @Error;
Example: @MySQLCreatePreparedStatement(MySQLHandle;QueryStr;DataTypes;MaxLengths);
/* Example: Simple transfer of Notes documents to a MySQL table.
This script does not perform any reconciliation or synchronization.
The target table is cleared first; then all matching Notes documents are inserted again.
The ID generated by MySQL is written back to the respective Notes document. */
SrcDBPath:="test\testdb1.nsf";
SrcDBh:=@OpenDB(SrcDBPath);
/* If the Notes database could not be opened, log the error. */
IF(@IsError(SrcDBh)){
@LogReport(SrcDBh;3);
}ELSE{
/* Initialize the MySQL handle. */
MySQLh:=@MySQLInit;
/* If MySQL initialization fails, log the error. */
IF(@IsError(MySQLh)){
@LogReport(MySQLh;3);
}ELSE{
/* Set or initialize the MySQL options. */
@MySQLOptions(MySQLh);
/* Define the connection data for the MySQL server. */
MySQLUser:="MySQLConnector";
MySQLPassword:="ConnectorPassword";
MySQLDefaultDatabase:="testdb";
MySQLHost:="172.16.1.215";
/* Establish the connection to the MySQL database. */
Ret:=@MySQLConnect(MySQLh;MySQLHost;MySQLUser;MySQLPassword;MySQLDefaultDatabase);
/* If the connection fails, log the error. */
IF(@IsError(Ret)){
@LogReport(Ret;3);
}ELSE{
/* Clear the target table before the transfer.
No reconciliation is performed; the data is transferred completely again. */
QueryStr:="TRUNCATE testdb.amount;";
Ret:=@MySQLQuery(MySQLh;QueryStr);
IF(@IsError(Ret)){
/* If the table cannot be cleared, the import is not continued. */
@LogReport(Ret;3);
}ELSE{
/* Prepare the prepared statement for inserting the Notes data into MySQL.
ModTime is set directly in MySQL using NOW(). */
QueryStr:="INSERT INTO testdb.amount (Name,Amount,UID,ModTime) VALUES (?,?,?,NOW());";
/* Define the data types and field lengths for the parameters of the prepared statement. */
QueryDataTypes:=253:4:253;
QueryLengths:=30:0:32;
/* Create the prepared statement. */
MySql_PrepStat:=@MySQLCreatePreparedStatement(MySQLh;QueryStr;QueryDataTypes;QueryLengths);
/* If the prepared statement could not be created, log the error. */
IF(@IsError(MySql_PrepStat)){
@LogReport(MySql_PrepStat;3);
}ELSE{
/* Select the Notes documents to be transferred. */
ScanFormula:="SELECT Form=\"MySqlAmount\";";
/* Scan the source database for matching documents and collect UNIDs. */
UNIDList:=@ScanDB(SrcDBh;ScanFormula;@AnyAllDay;1;2);
/* Prepare the loop over all found UNIDs. */
UNIDList_Index:=1;
UNIDList_Ele:=@Elements(UNIDList);
WHILE(UNIDList_Index<=UNIDList_Ele){
/* Read the current UNID from the list and additionally prepare it as text. */
ThisNoteUNID:=@GetListElement(UNIDList;UNIDList_Index);
ThisNoteUNIDText:=@GetListElement(@Text(ThisNoteUNID);1);
/* Open the current Notes document by its UNID. */
ThisNoteh:=@OpenNoteByUID(SrcDBh;ThisNoteUNID;ThisNoteh;1);
/* Log the values to be transferred. */
@LogReport("UNID :"+ThisNoteUNIDText;3);
@LogReport("Name :"+Name[ThisNoteh];3);
@LogReport("Amount :"+@Text(Amount[ThisNoteh]);3);
/* Execute the prepared statement with the values from the Notes document. */
Ret:=@MySQLExecutePreparedStatement(MySql_PrepStat;Name[ThisNoteh];Amount[ThisNoteh];ThisNoteUNIDText);
/* Only after a successful INSERT, read the generated MySQL ID and write it back. */
IF(@IsError(Ret)){
@LogReport(Ret;3);
}ELSE{
/* Determine the ID of the inserted record generated by MySQL. */
ThisQueryInsertedID:=@MySQLGetLastInsertIDPreparedStatement(MySql_PrepStat);
/* Write the generated MySQL ID back to the Notes document. */
MySQLID[ThisNoteh]:=ThisQueryInsertedID;
@LogReport("MySQLID:"+@Text(MySQLID[ThisNoteh]);3);
}
/* Save and close the Notes document. */
ThisNoteh:=@CloseNote(ThisNoteh;1);
/* Move to the next list entry. */
@Increment(UNIDList_Index);
} /*WHILE(UNIDList_Index<=UNIDList_Ele)*/
/* Close the prepared statement. */
MySql_PrepStat:=@MySQLClosePreparedStatement(MySql_PrepStat);
} /*END ELSE IF(@IsError(MySql_PrepStat))*/
} /*END ELSE IF(@IsError(Ret)) TRUNCATE*/
} /*END ELSE IF(@IsError(Ret)) @MySQLConnect*/
/* Close the MySQL connection. */
MySQLh:=@MySQLClose(MySQLh);
} /*END ELSE IF(@IsError(MySQLh))*/
/* End or clean up the MySQL thread context. */
@MySQLThreadEnd;
/* Close the Notes source database. */
SrcDBh:=@CloseDB(SrcDBh);
} /*END ELSE IF(@IsError(SrcDBh))*/
Note : This text was machine-translated and may contain inaccuracies.