f$SQLInsert

A data record is inserted into a database table with this function. All recorded parameters in the specified parameter group with names corresponding to a column name in the table are included in the Insert command. To insert the values, the description of the table is calculated and the data types adjusted. Only binary data types cannot be processed. If desired, the data record is read again after inserting in order to fill the parameters in the parameter group which were filled by database default values or database triggers.

Note: since it takes time to make the connection, a new connection should not be established for every SQL function.

Syntax:	Status = f$SQLInsert(Channel,Table,PGName,Read[,Count,colnames])
	Argument	Type	Meaning
	Channel	Integer	Channel number for the SQL connection
	Table	String	Name of the table in which the data record is to be inserted.
	PGName	String	Name of the parameter group whose values are to be inserted. If only one data record is to be created, either the parameter group may not be indexed or the index must be entered as well.
	Read	Integer	Value: 1 The data record is read after saving and written into the parameter group.
Optional	Count	Integer	Number of records to be inserted. If an indexed parameter group is used, multiple data records can be created with this command.
Optional	Colnames	String	Table column names separated with a comma which are used for reading the data records after inserting. If column names are not given, all columns that had a recorded value when inserting are searched with. If column names are given, only these are used for searching. Depending on database and the format of the fields, the read may not find the data record if all columns are used for searching. Example: column of type bit. Insert with the value:-1. SQL server converts the value to 1 upon insertion. Searching with the value –1 however does not deliver any results.
Optional	Option	Integer	With this option you can manage how new data records with parameters in NOVAL or UNDEF state are treated when created: 0 – parameter in NOVAL or UNDEF state as well as empty strings are ignored 1 – empty strings are written as " 2 - parameters in NOVAL state are written as " if the database column is of varchar type or similar 3 - parameters in NOVAL or UNDEF are written as " if the database column is of varchar type or similar
Result:	Integer	Error status: 0 :command was resolved correctly 1:table name is empty 2:the parameter group does not exist 3:the parameter group is empty or the parameters do not agree with the columns in the table (depending on the ODBC settings, they are case-sensitive. With Oracle, SMP_NG.ODBC_DBTYP has to be set to value 2). -9999: the SQL command was not successful. In this case, the error status and error description can be calculated using: f$SQLError. -9998:the data record was created successfully but could not be read. The data record was possibly modified by a database trigger when being inserted.
Example:	Channel = f$SQLConnect( db, user, password ) Status = f$SQLInsert ( Channel, 'TM_Document','Documents[No]',1) Status = f$SQLDisconnect( Channel ) or Channel = f$SQLConnect( db, user, password ) Status = f$SQLInsert ( Channel, 'TM_Document','Documents',1,Count_doc,'ObjectID,Objecttype,DocumentNo') Status = f$SQLDisconnect( Channel )

Note: if the f$SQLInsert function determines that a column exists in the table to which the data record is to be inserted, and that the column was filled with an autovalue, the results are sorted descendingly from this column when reading the data records. If the data record read delivers multiple results, the newest data record is calculated in this way. The function can recognize this with the Microsoft SQLServer and MySQL database systems, however not with Oracle.