Binding for long strings produce errors

[shaikatz]

Hi, looks like we having issue using binding for string bigger then the defined threshold . https://github.com/snowflakedb/snowflake-connector-nodejs/blob/b73e33a7f0c84078f887f01536684bc51aea939a/lib/connection/bind_uploader.js#L13

If my array is small (below 100000 characters) the binding works (since it doesn't use file upload), while going above the threshold, seeing the below error.

1. What version of NodeJS are you using (node --version and npm --version)? v14.19.1

2. What operating system and processor architecture are you using? System Version: macOS 12.2.1 (21D62) Kernel Version: Darwin 21.3.0

3. What did you do?

  const array: any = [];
  for (let i = 0; i < 100000; i++) {
    array.push({
      'stuff1': 'some-data-for-stuff1',
      'stuff2': 'some-data-for-stuff2',
    });
  }
const query = `insert into TABLE_NAME(stuff1, stuff2) 
	                      select  value:stuff1,
	                                  value:stuff2
	                        from table(flatten(parse_json(?)))
`
connection.execute(query, [JSON.stringify(array)]

6. What did you expect to see? Successful insert
7. What did you see instead?

{"level":"TRACE","message":"[2:38:18.3818 PM]: https://MY_ACCOUNT_ID.us-east-1.snowflakecomputing.com/session/v1/login-request?requestId=284ad156-9d33-43db-b840-4237c8cd8b5b&warehouse=XSMALL&databaseName=MY_DB_NAME&schemaName=PUBLIC"}
{"level":"DEBUG","message":"[2:38:18.3818 PM]: OCSP validation disabled for MY_ACCOUNT_ID.us-east-1.snowflakecomputing.com"}
{"level":"DEBUG","message":"[2:38:19.3819 PM]: --createStatementPreExec"}
{"level":"DEBUG","message":"[2:38:19.3819 PM]: -- binds.length= 1"}
{"level":"DEBUG","message":"[2:38:19.3819 PM]: numBinds = 6600001"}
{"level":"DEBUG","message":"[2:38:19.3819 PM]: BindUploaders"}
{"level":"DEBUG","message":"[2:38:19.3819 PM]: token = ****"}
{"level":"DEBUG","message":"[2:38:19.3819 PM]: BindUploaders::Upload"}
{"level":"DEBUG","message":"[2:38:20.3820 PM]: fileName=1"}
{"level":"DEBUG","message":"[2:38:20.3820 PM]: BindUploaders::UploadStream"}
{"level":"DEBUG","message":"[2:38:20.3820 PM]: context.bindStage = @SYSTEM$BIND/b18d9cef-dffa-47f6-8635-3bd93bdf46bf"}
{"level":"DEBUG","message":"[2:38:20.3820 PM]: createStage"}
{"level":"DEBUG","message":"[2:38:20.3820 PM]: CREATE_STAGE_STMT = CREATE OR REPLACE TEMPORARY STAGE SYSTEM$BIND file_format=( type=csv field_optionally_enclosed_by='\"')"}
{"level":"DEBUG","message":"[2:38:20.3820 PM]: --createStatementPreExec"}
{"level":"DEBUG","message":"[2:38:20.3820 PM]: numBinds = 0"}
{"level":"DEBUG","message":"[2:38:20.3820 PM]: RowStatementPreExec"}
{"level":"DEBUG","message":"[2:38:20.3820 PM]: context.bindStage=undefined"}
{"level":"TRACE","message":"[2:38:20.3820 PM]: https://MY_ACCOUNT_ID.us-east-1.snowflakecomputing.com/queries/v1/query-request?requestId=06ec161c-d028-417e-a891-ac7d32dc8945"}
{"level":"DEBUG","message":"[2:38:20.3820 PM]: OCSP validation disabled for MY_ACCOUNT_ID.us-east-1.snowflakecomputing.com"}
{"level":"DEBUG","message":"[2:38:21.3821 PM]: stream"}
{"level":"DEBUG","message":"[2:38:21.3821 PM]: err null"}
{"level":"DEBUG","message":"[2:38:21.3821 PM]: stream on data"}
{"level":"DEBUG","message":"[2:38:21.3821 PM]: uploadFiles @SYSTEM$BIND/b18d9cef-dffa-47f6-8635-3bd93bdf46bf"}
{"level":"DEBUG","message":"[2:38:21.3821 PM]: Put=PUT file://1'@SYSTEM$BIND/b18d9cef-dffa-47f6-8635-3bd93bdf46bf' overwrite=true auto_compress=false source_compression=gzip"}
{"level":"DEBUG","message":"[2:38:21.3821 PM]: --createStatementPreExec"}
{"level":"DEBUG","message":"[2:38:21.3821 PM]: context.bindStage=undefined"}
{"level":"TRACE","message":"[2:38:21.3821 PM]: https://MY_ACCOUNT_ID.us-east-1.snowflakecomputing.com/queries/v1/query-request?requestId=18a62c16-109f-428f-802e-b6d03acbe13f"}
{"level":"DEBUG","message":"[2:38:21.3821 PM]: OCSP validation disabled for MY_ACCOUNT_ID.us-east-1.snowflakecomputing.com"}
{"level":"DEBUG","message":"[2:38:46.3846 PM]: uploadFiles done "}
{"level":"DEBUG","message":"[2:38:46.3846 PM]: stream on data"}
{"level":"DEBUG","message":"[2:38:46.3846 PM]: stream on end "}
{"level":"DEBUG","message":"[2:38:46.3846 PM]: all completed"}
{"level":"DEBUG","message":"[2:38:46.3846 PM]: Clean File=1"}
{"level":"DEBUG","message":"[2:38:46.3846 PM]: RowStatementPreExec"}
{"level":"DEBUG","message":"[2:38:46.3846 PM]: context.bindStage=@SYSTEM$BIND/b18d9cef-dffa-47f6-8635-3bd93bdf46bf"}
{"level":"TRACE","message":"[2:38:46.3846 PM]: https://MY_ACCOUNT_ID.us-east-1.snowflakecomputing.com/queries/v1/query-request?requestId=da87e8a5-0885-40b3-ab07-8fe2a10b743f"}
{"level":"DEBUG","message":"[2:38:46.3846 PM]: OCSP validation disabled for MY_ACCOUNT_ID.us-east-1.snowflakecomputing.com"}
(node:7596) UnhandledPromiseRejectionWarning: OperationFailedError: SQL compilation error: error line 22 at position 40
Bind variable ? not set.
    at createError (/Users/shaikatz/Development/finaloop/node_modules/.pnpm/[email protected]/node_modules/snowflake-sdk/lib/errors.js:536:15)
    at Object.exports.createOperationFailedError (/Users/shaikatz/Development/finaloop/node_modules/.pnpm/[email protected]/node_modules/snowflake-sdk/lib/errors.js:315:10)
    at Object.callback (/Users/shaikatz/Development/finaloop/node_modules/.pnpm/[email protected]/node_modules/snowflake-sdk/lib/services/sf.js:647:28)
    at /Users/shaikatz/Development/finaloop/node_modules/.pnpm/[email protected]/node_modules/snowflake-sdk/lib/http/base.js:111:25
    at done (/Users/shaikatz/Development/finaloop/node_modules/.pnpm/[email protected]/node_modules/urllib/lib/urllib.js:589:5)
    at /Users/shaikatz/Development/finaloop/node_modules/.pnpm/[email protected]/node_modules/urllib/lib/urllib.js:953:9
    at decodeContent (/Users/shaikatz/Development/finaloop/node_modules/.pnpm/[email protected]/node_modules/urllib/lib/urllib.js:740:14)
    at handleResponseCloseAndEnd (/Users/shaikatz/Development/finaloop/node_modules/.pnpm/[email protected]/node_modules/urllib/lib/urllib.js:924:7)
    at IncomingMessage.<anonymous> (/Users/shaikatz/Development/finaloop/node_modules/.pnpm/[email protected]/node_modules/urllib/lib/urllib.js:962:7)
    at IncomingMessage.emit (events.js:412:35)
(Use `node --trace-warnings ...` to show where the warning was created)

[kfirba]

We also have the exact issue on our end

[hexelon]

This is a major blocker here as well

[sfc-gh-ext-simba-dl]

//remove by wrong msg

[sfc-gh-ext-simba-dl]

why they pass it by json? can they pass it by array?

[sfc-gh-ext-simba-dl]

this is my sample code, and it works fine.

var useSchema = 'use schema SEN'; var createTestTbl = 'create or replace table testTbl2(colA varchar(30), colB varchar(30))'; var dropTestTbl = 'drop table if exists testTbl2'; var insertWithQmark = 'insert into testTbl2 values(?, ?)';

var connection_ID; connection.connect ( function (err, conn) { if(err) { console.error('Unable to connect: ' + err.message); } else { console.log('Successfully connected to Snowflake.'); // Optional: store the connection ID. connection_ID = conn.getId(); UseSchema(); } } );

function UseSchema() { console.log('use schema'); var schemaStatement = connection.execute({ sqlText: useSchema, complete: function (err) { if (err) { console.error('0 failed to change schema: ' + err.message); } else { CreateTable(); } } }); }

function CreateTable() { console.log('create table execute.'); var statement = connection.execute({ sqlText: createTestTbl, complete: function (err, stmt, rows) { if (err) { console.error('1 Failed to execute statement due to the following error: ' + err.message); } else { InsertTable(); } } }); }

function InsertTable() { console.log('insert table execute.'); var arrBind = []; for(var i = 0; i<100000; i++) { arrBind.push(["some-data-for-stuff1","some-data-for-stuff2"]); } try { var insertStatement = connection.execute({ sqlText: insertWithQmark, binds: arrBind, complete: function (err, stmt) { if (err) { console.error('1 Failed to execute statement due to the following error: ' + err.message); } else { console.log('inserted rows=' + stmt.getNumUpdatedRows()); } CloseConnection(); } }); } catch (error) { console.log('error' + error); } }

function CloseConnection() { connection.destroy(); }

[sfc-gh-dszmolka]

#439 & #443 should fix

[sfc-gh-dszmolka]

release [email protected] is out with the fix PR. please upgrade to the fixed version and re-test. if the issue still persists, please reopen the issue or comment with the details and I'll reopen it.