f2s_esql_append_stmt¶

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

Syntax

f2s_esql_append_stmt {value} [using {variant}]

Description

This command is used to append a command string to the command buffer initialized by f2s_esql_create_stmt.

It is so that long SQL expressions could be broken into smaller strings and appended to a dynamic command buffer, which is then passed to the server.

It will concatenate a string to the command buffer initialized by the f2s_esql_set_param command and it can be called as many times as necessary to complete the SQL expression. Subsequently, the f2s_esql_execute_stmt command executes the completed command.

The option to pass the file name as an extra parameter is used in cases where the data needs to be fetched directly into the record buffer of the specified file.

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_append_stmt

 Variant[] vRow
 //Select the first 10 Customers
 f2s_esql_create_stmt " select Top (10) "
 f2s_esql_append_stmt " Address, City, "
 f2s_esql_append_stmt " PHONE_NUMBER, FAX_NUMBER "
 f2s_esql_append_stmt " From Customer "
 f2s_esql_prepare_stmt
 f2s_esql_execute_stmt
 f2s_esql_get_row vRow
 f2s_esql_move_next
 While (Found)
   f2s_esql_get_row vRow
   Showln (SFormat("Address: %1, City:%2, Phone:%3, Fax:%4", vRow[0], vRow[1],vRow[2], vRow[3]))
   f2s_esql_move_next
 Loop
 f2s_esql_close_stmt
 inkey WindowIndex

As an alternative to using the f2s_esql_append_stmt the following example uses an image to store the SQL Statement. The advantage of this approach is complex SQL queries can be stored without having to use strings and the entire SQL Statement can be easily formatted.

Example of f2s_esql_append_stmt from an image with the optional “using” syntax with a variant

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_move_next

• f2s_esql_close_stmt

• f2s_esql_next_resultset

Replaces: Command SQL_APPEND_STMT