Using INSERT LOCATION with ODBC [CR 380217]

The IQ INSERT LOCATION syntax uses '{' and '}' characters, which represent a start and end escape sequence in the ODBC standard. Because the INSERT LOCATION select statement passed within the '{ }' characters is not a valid ODBC escape sequence, ODBC may return a syntax error like [ODBC Driver] Syntax error or access violation.

To avoid the ODBC syntax error, use one of the following methods:

• Turn off ODBC escape processing when sending an INSERT LOCATION statement by using the SQL_NOSCAN attribute setting in the ODBC program. For example:

  SQLSetStmtAttr( stmt, SQL_NOSCAN, TRUE );
  

• Create an IQ stored procedure to accept string input that will be a valid INSERT LOCATION statement with different characters used in place of '{ }' that are not part of the INSERT LOCATION statement. The stored procedure would replace the alternate characters with '{ }' and execute the INSERT LOCATION statement.

  For example, to create an IQ stored procedure that replaces '[ ]' with '{ }':

  create procedure sp_ins(string1 varchar(1024))
  begin 
     declare @cmdline varchar(1024);
     set @cmdline = replace(string1, '[', '{' );
     set @cmdline = replace(@cmdline, ']', '}' ); 
     execute immediate @cmdline;
  end
  

  Then the ODBC application would send the following to IQ:

  call sp_ins
  ('INSERT into t1(c1) LOCATION ''host.db'' [select c1
  from t2]')
  

  The stored procedure would convert the string to:

  INSERT into t1(c1) LOCATION 'host.db' {select c1 from t1}
  

  and execute that command.