How to Add Inline Code Statements Containing ODBC/JDBC/ADO.NET Statements › Inline Code Statements with ODBC/JDBC/ADO.NET › Construct the ODBC/JDBC Statements

Construct the ODBC/JDBC Statements

This procedure gives you an idea about constructing ODBC/JDBC statements.

Follow these steps:

1. Initialize the statement handle or object with SQL query string. This is done in the Code section of the inline code statement.
   • C/ODBC

     /* To execute SQL (INSERT) query */
     ret_code = TiodbcAllocStmt(&create_stmt, “create_employee”, FALSE);
     ret_code = TiodbcPrepare(&create_stmt, “INSERT INTO `EMPLOYEE` (`NUMBER`,`NAME`, 
     			`WORK_PHONE`) VALUES (?, ?, ?)”);
     
     /* To execute SQL (SELECT) query */
     ret_code = TiodbcAllocStmt(&read_stmt, “read_employee”, FALSE);
     ret_code = TiodbcPrepare(&read_stmt, “SELECT `NUMBER`, `NAME`, 
     			`WORK_PHONE` FROM `EMPLOYEE` WHERE `NUMBER` = ? FOR UPDATE OF `WORK_PHONE`”);
     
     /* To execute SQL (UPDATE) query */
     ret_code = TiodbcAllocStmt(&update_stmt, “update_employee”, FALSE);
     ret_code = TiodbcPrepare(&update_stmt, 
     			“UPDATE `EMPLOYEE` SET `WORK_PHONE` = ? WHERE `NUMBER` = ?”);
     
     

   • C#/ADO.NET

     /* To execute SQL (INSERT) query */
     create_cmd = Globdata.GetDBMSData().GetDBMSManager().GetCommand(Globdata, 
     			IefRuntimeParm2, “IEFDB”, “INSERT INTO `CUSTOMER` 
     			(`NUMBER`, `NAME`, `WORK_PHONE`) VALUES (?, ?, ?)”);
     
     /* To execute SQL (SELECT) query */
     read_cmd = Globdata.GetDBMSData().GetDBMSManager().GetCommand(Globdata, 
     			IefRuntimeParm2, “IEFDB”, “SELECT `NUMBER`, `NAME`, `WORK_PHONE` FROM `EMPLOYEE` 
     			WHERE `NUMBER` = ? FOR UPDATE OF `WORK_PHONE`”);
     
     /* To execute SQL (UPDATE) query */
     update_cmd = Globdata.GetDBMSData().GetDBMSManager().GetCommand(Globdata, 
     			IefRuntimeParm2, “IEFDB”, “UPDATE `EMPLOYEE` SET 
     			`WORK_PHONE` = ? WHERE `NUMBER` = ?”);
     

   Note: The string literal “IEFDB” refers to the data source name.

   • JAVA/JDBC

     /* To execute SQL (INSERT) query */
     create_cmd = globdata.getDBMSData().getDBMSManager().getStatement(globdata, 
     			iefRuntimeParm2, “IEFDB”, “INSERT INTO `CUSTOMER` (`NUMBER`, 
     			`NAME`, `WORK_PHONE`) VALUES (?, ?, ?)”);
     
     /* To execute SQL (SELECT) query */
     read_cmd = globdata.getDBMSData().getDBMSManager().getStatement(globdata, 
     			iefRuntimeParm2, “IEFDB”, “SELECT `NUMBER`, `NAME`, `WORK_PHONE` 
     			FROM `EMPLOYEE` WHERE `NUMBER` = ? FOR UPDATE OF `WORK_PHONE`”);
     
     /* To execute SQL (UPDATE) query */
     update_cmd = globdata.getDBMSData().getDBMSManager().getStatement(globdata, 
     			iefRuntimeParm2, “IEFDB”, “UPDATE `EMPLOYEE` 
     			SET `WORK_PHONE` = ? WHERE `NUMBER` = ?”);
     
     

   Note: The string literal “IEFDB” refers to the data source name.

2. Binding the columns or parameters using host variables provides a way to exchange data between the data source and the application. When an SQL statement is executed, the bounded parameters will be read and the bounded columns will be retrieved. The application should take the responsibility to read from and/or write to the host variables. Sometimes, more than one set of host variables are needed (for example, SELECT + UPDATE).

   Binding the columns in a SELECT SQL statement using host variables applies to C/ODBC only. Result sets are used to retrieve data in C#/ADO.NET and Java/JDBC.

   • C/ODBC

   Use the SQLBindCol ODBC API to bind columns with known host variables. For more information, see the MSDN documentation on ODBC APIs.

   /* To execute SQL (SELECT) query */
   ret_code = SQLBindCol(read_stmt.hstmt, 1, SQL_C_SLONG, &hv_number, 
   			sizeof(hv_number), &cbData);
   ret_code = SQLBindCol(read_stmt.hstmt, 2, SQL_C_CHAR, &hv_name, 
   			sizeof(hv_name), &cbData);
   ret_code = SQLBindCol(read_stmt.hstmt, 3, SQL_C_CHAR, &hv_work_phone, 
   			sizeof(hv_work_phone), &hvind_work_phone);
   
   

   Note: The column index starts from one. Non-query SQL statements, such as INSERT / UPDATE / DELETE, do not need binding columns.

   Note: Refer to Table 1 in Appendix section about SQL C types for various Gen data types.

   Bind the parameters using host variables, if any. All types of SQL statements need parameter binding.

   • C/ODBC

     /* To execute SQL (INSERT) query */
     ret_code = TiodbcParameter(&create_stmt, 1, “”, “EMPLOYEE”, “NUMBER”);
     ret_code = SQLBindParameter(create_stmt.hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, 
     			info->sqlType, info->precision, info->scale, &hv_number, 
     			sizeof(hv_number), NULL);
     ret_code = TiodbcParameter(&create_stmt, 2, “”, “EMPLOYEE”, “NAME”);
     ret_code = SQLBindParameter(create_stmt.hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, 
     			info->sqlType, sizeof(hv_name)-1, info->scale, hv_name, 
     			sizeof(hv_name), NULL);
     ret_code = TiodbcParameter(&create_stmt, 3, “”, “EMPLOYEE”, “WORK_PHONE”);
     ret_code = SQLBindParameter(create_stmt.hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, 
     			info->sqlType, sizeof(hv_work_phone)-1, info->scale, hv_work_phone, 
     			sizeof(hv_work_phone), NULL);
     
     /* To execute SQL (SELECT) query */
     ret_code = TiodbcParameter(&read_stmt, 1, “”, “EMPLOYEE”, “NUMBER”);
     ret_code = SQLBindParameter(read_stmt.hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, 
     			info->sqlType, info->precision, info->scale, &hv_number, 
     			sizeof(hv_number), NULL);
     
     /* To execute SQL (UPDATE) query */
     ret_code = TiodbcParameter(&update_stmt, 1, “”, “EMPLOYEE”, “WORK_PHONE”);
     ret_code = SQLBindParameter(update_stmt.hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, 
     			info->sqlType, sizeof(hv_work_phone)-1, info->scale, hv_work_phone, 
     			sizeof(hv_work_phone), NULL);
     ret_code = TiodbcParameter(&update_stmt, 2, “”, “EMPLOYEE”, “NUMBER”);
     ret_code = SQLBindParameter(update_stmt.hstmt, 2, SQL_PARAM_INPUT, SQL_C_SLONG, 
     			info->sqlType, info->precision, info->scale, &hv_number, 
     			sizeof(hv_number), NULL);
     
     

   Note: The parameter index starts from one. Column and Parameter indices are independent of each other. When required, specify the OWNER of DB physical table at the third parameter of TiodbcParameter API.

   Note: Refer to Table 1 in Appendix section about SQL C types for various Gen data types.

   • C#/ADO.NET

     /* To execute SQL (INSERT) query */
     create_cmd.InsertParameter();
     create_cmd.BindParameter(0, hv_number);
     create_cmd.InsertParameter();
     create_cmd.BindParameter(1, hv_name);
     create_cmd.InsertParameter();
     create_cmd.BindParameter(2, hv_work_phone);
     
     /* To execute SQL (SELECT) query */
     read_cmd.InsertParameter();
     read_cmd.BindParameter(0, hv_number);
     
     /* To execute SQL (UPDATE) query */
     update_cmd.InsertParameter();
     update_cmd.BindParameter(0, hv_work_phone);
     update_cmd.InsertParameter();
     update_cmd.BindParameter(1, hv_number);
     
     

   Note: The parameter index starts from zero.

   Note: Refer to Table 2 in Appendix section about bind methods for various Gen data types.

   • JAVA/JDBC

     /* To execute SQL (INSERT) query */
     create_cmd.setInt(1, hv_number);
     create_cmd.setString(2, hv_name);
     create_cmd.setString(3, hv_work_phone);
     
     /* To execute SQL (SELECT) query */
     read_cmd.setInt(1, hv_number);
     
     /* To execute SQL (UPDATE) query */
     update_cmd.setString(1, hv_work_phone);
     update_cmd.setInt(2, hv_number);
     
     

   Note: The parameter index starts from one.

   Note: Refer to Table 3 in Appendix section about bind methods for various Gen data types.