4D v16.3

SQL SET PARAMETER

Home

 
4D v16.3
SQL
SQL SET PARAMETER

SQL SET PARAMETER 


 

SQL SET PARAMETER ( object ; paramType ) 
Parameter Type   Description
object  4D object in 4D object to be used (variable, array or field)
paramType  Longint in Type of parameter

The SQL SET PARAMETER command allows the use of a 4D variable, array or field value in SQL requests.

Note: It is also possible to directly insert the name of a 4D object to be used (variable, array or field) between the << and >> characters in the text of the request (see example 1). For more information about this, please refer to the Overview of SQL Commands section.

  • In the object parameter, pass the 4D object (variable, array or field) to be used in the request.
  • In the paramType parameter, pass the SQL type of the parameter. You can pass a value or use one of the following constants, located in the “SQL” theme:
    Constant Type Value Comment
    SQL param in Longint 1
    SQL param in out Longint 2 Usable only in the context of an SQL stored procedure (in-out parameter defined in the stored procedure)
    SQL param out Longint 4 Usable only in the context of an SQL stored procedure (out parameter defined in the stored procedure)

The value of the 4D object replaces the ? character in the SQL request (standard syntax). If the request contains more than one ? character, several calls to SQL SET PARAMETER will be necessary. The values of the 4D objects will be assigned sequentially in the request, in accordance with the execution order of the commands.

Warning: This command is used for handling parameters passed to the SQL request. It is not possible to use the SQL param out type to associate a 4D object with the result of an SQL request. SQL request results are retrieved, for example, using the boundObj parameter of the SQL EXECUTE command (see the Overview of SQL Commands). The SQL SET PARAMETER command is mainly intended for setting parameters passed to the request (SQL param in); the SQL param out and SQL param in out types are reserved for use in the context of SQL stored procedures that could return parameters.

This example is used to execute an SQL request which calls the associated 4D variables directly:

 C_TEXT(MyText)
 C_LONGINT(MyLongint)
 
 SQL LOGIN("mysql";"root";"")
 SQLStmt:="insert into app_testTable (alpha_field, longint_field) VALUES (<<MyText>>, <<MyLongint>>)"
 For(vCounter;1;10)
    MyText:="Text"+String(vCounter)
    MyLongint:=vCounter
    SQL EXECUTE(SQLStmt)
    SQL CANCEL LOAD
 End for
 SQL LOGOUT

Same example as the previous one, but using the SQL SET PARAMETER command:

 C_TEXT(MyText)
 C_LONGINT(MyLongint)
 
 SQL LOGIN("mysql";"root";"")
 SQLStmt:="insert into app_testTable (alpha_field, longint_field) VALUES (?,?)"
 For(vCounter;1;10)
    MyText:="Text"+String(vCounter)
    MyLongint:=vCounter
    SQL SET PARAMETER(MyText;SQL param in)
    SQL SET PARAMETER(MyLongint;SQL param in)
    SQL EXECUTE(SQLStmt)
    SQL CANCEL LOAD
 End for
 SQL LOGOUT

If the command has been executed correctly, the system variable OK returns 1. Otherwise, it returns 0.

 
PROPERTIES 

Product: 4D
Theme: SQL
Number: 823

The OK variable is changed by the command

 
HISTORY 

Created: 4D 2004

 
ARTICLE USAGE

4D Language Reference ( 4D v16)
4D Language Reference ( 4D v16.1)
4D Language Reference ( 4D v16.2)
4D Language Reference ( 4D v16.3)