f2s_esql_close_stmt¶

Applies to: Oracle, MS SQL, MySql/MariaDB, PostgreSQL Drivers

Syntax

f2s_esql_close_stmt [using {variant}]

Description

This command is used to close any open cursors or Select Statements related to a f2s_esql_create_stmt. This should be used once the SQL statement has been executed and all rows have been fetched.

The optional “using” syntax allows you to pass around the interface created by this command. The variable passed MUST be a variant. This allows for multiple statements to be active concurrently in some cases. If you use the “using” syntax, it must be used on all subsequent commands. Mixing the using syntax is not supported. So for instance, if you plan on having multiple statements active concurrently, all active statements must make use of the using syntax.

Examples

f2s_esql_close_stmt with no parameters

 Variant[] vRow
 f2s_esql_create_stmt "select * from orderheader where SalesPerson_ID = ?"
 f2s_esql_prepare_stmt
 f2s_esql_set_param 1 to "NONE"
 f2s_esql_execute_stmt
 f2s_esql_move_next
 While (Found)
     f2s_esql_get_row vRow
     Showln (SFormat("First Column: %1 Second Column:  %2",vRow[0], vRow[1]))
     f2s_esql_move_next
 Loop
 f2s_esql_close_stmt

f2s_esql_close_stmt with the optional “using” parameters

 Use f2s.pkg
 Use cMertechImageParser.pkg

 f2s_activate_driver "f2s_ms"
 // Login to MyInstance using a trusted connection
 Login "localhost\MyInstance" "" "" _f2s_ActiveDriver
 // Don't use the default DB, instead use MyOtherDB
 set_attribute f2s_esql_db_name of _f2s_ActiveDriverID to "MyOtherDB"

 /SomeESQL
 select top 10 C.ID, C.CORETYPE, C.BRANCH, C.REF_NO, C.FROMC, C.NAME from CUSTMAIN as C
 join CUSTALT as N on C.ACTUAL_NO = N.CUSTOMER_NO and C.REF_NO = N.TEMP_REF
 where C.BRANCH = ? and C.REF_DATE = ? and C.TYPE =? ORDER BY C.REF_NO, C.BRANCH
 /*

 Function CallSomeESQL Variant[] params returns Variant[]
     Variant vStat
     Variant[] vRow
     Integer i

     f2s_esql_create_stmt (ImageToString(mdsImageParser, "SomeESQL")) using vStat
     f2s_esql_prepare_stmt using vStat

     for i from 1 to (SizeOfArray(params))
         f2s_esql_set_param i to params[i-1] using vStat
     loop

     f2s_esql_execute_stmt using vStat
     f2s_esql_move_next using vStat

     While (Found)
         // If f2s_esql_move_next returns true, it means there is a row available, so process it
         // The processing is meaningless in this case, just showing a code pattern.
         f2s_esql_get_row vRow using vStat
         clear coreRef
         move vRow[1] to coreRef.type
         find eq coreRef by Index.2
         if (found) Begin
             f2s_esql_close_stmt using vStat
             move (InsertInArray(vRow, -1 coreRef.someVal)) to vRow
             function_return vRow
         end
         f2s_esql_move_next using vStat
     loop

     // If we get here there was no match in the returned data, so just clean up.
     f2s_esql_close_stmt using vStat
     end_function

     Variant[] myParams vRow

     move 2 to myParams[0] // BRANCH
     move 04/01/2020 to myParams[1] // REF_DATE
     move "ON" to myParams[2] // TYPE

     Get CallSomeESQL myParams to vRow
     if (SizeOfArray(vRow) > 0) Begin
         // Call worked! Do something
     end

Related Commands

• f2s_esql_create_stmt

• f2s_esql_prepare_stmt

• f2s_esql_set_param

• f2s_esql_execute_stmt

• f2s_esql_move_next

Replaces: Commands SQL_CANCEL_QUERY_STMT and SQL_CLOSE_CURSOR_STMT