Example: Call CL command using SQL CALL statement

It is possible to run IBM® i commands by using an SQL CALL statement. The two examples that are provided here apply to ODBC programs.

Simply call Execute Command (QCMDEXC) to run the command. The process is easy, simply provide the command string and the length of the command string as parameters on the CALL statement. Use the Remote Command API as an alternative.

The first example enables the powerful SQL tracing facility that writes data into the joblog for the job running the SQL (in this case, the server job).

The second example allows a member other than the first of a multi-member file to be accessed using SQL. You cannot create a multi-member file through CREATE TABLE. However, the following example shows you how to access a member other than the first of a multi-member file that is created through DDS:

	 Dim hStmt                  As Long

	 rc = SQLAllocHandle(SQL_HANDLE_STMT, ghDbc, hStmt)
	  If rc <> SQL_SUCCESS Then
		 Call DspSQLError(SQL_HANDLE_DBC, ghDbc, "Problem: Allocating Debug Statement Handle")
	 End If

	 ' Note that the string within single quotes 'STRDBG UPDPROD(*YES)' is exactly 20 bytes
	 cmd = "call qsys2.qcmdexc('STRDBG UPDPROD(*YES)',20)"

	 ' Put the system job in debug mode
	 rc = SQLExecDirect(hStmt, cmd, SQL_NTS)
	 If rc <> SQL_SUCCESS Then
		 Call DspSQLError(SQL_HANDLE_STMT, hStmt, "Problem: Start Debug")
	 End If
  
	 rc = SQLAllocHandle(SQL_HANDLE_STMT, ghDbc, ovrhstmt)
	 If rc <> SQL_SUCCESS Then
		 Call DspSQLError(SQL_HANDLE_DBC, ghDbc, "Problem: Allocating Override Statement Handle")
	 End If

 ' Note that the string within single quotes 'OVRDBF FILE(BRANCH)... OVRSCOPE(*JOB)' 
    is exactly 68 bytes
   cmd = "call qsys.qcmdexc('OVRDBF FILE(BRANCH) TOFILE(HOALIB/BRANCH) MBR(FRANCE) 
                                                 OVRSCOPE(*JOB)',68)"

	 ' Override the IBM i file to point to the 'france' member
	 rc = SQLExecDirect(hStmt, cmd, SQL_NTS)
	 If rc <> SQL_SUCCESS Then
		 Call DspSQLError(SQL_HANDLE_STMT, hStmt, "File Override")
	 End If