Is there SQLBindParameter() equivalent of node ODBC

[SabaKauser]

Hello,
Does this driver has any SQLBindparameter() equivalent to allow developers to explicitly bind the parameters by passing the input C and SQL type?
or
Is there a way to bind a file to parameter instead of the actual value? i.e I have some data in a file and I would like to bind this file to a parameter marker in my SQL instead of reading the contents and passing it.
e.g:
var callStatement = "call mySP('par1',?);
// file filename has contents that I want to bind to second parameter
ibmdb.query(callStatement, [filename],function (err, result){
if (err) {
console.log(err);
return cb("Error " + err, "-1");
}
else {
console.log("Affected rows = " + result);
//In some cases closing of StatementHandle is also needed stmt.closeSync();
// result.closeSync();
return cb("success", "Rows affected : " + result);
}

Thanks in Advance!

[markdirish]

Hi @SabaKauser ,

(Everything below is in reference to v2.0 and above, which is a complete rewrite of odbc for Node.js. I have v2.0.0 done, and will release it later today):

Does this driver has any SQLBindparameter() equivalent to allow developers to explicitly bind the parameters by passing the input C and SQL type?

Just for clarification, odbc downloaded from npm is not a driver, it is an application that talks to the driver manager, which uses the driver you have downloaded for your database. There is currently no mechanism to explicitly bind C and SQL type. I know in idb-connector, which is an older connector for IBM i, you had to explicitly define them, but that is no longer needed in this case.

When interacting with a table or a view with .query, the program calls SQLDescribeCol() for each column, which (among other things) returns the column's data type, making it unnecessary to explicitly specify SQL type (unless you wanted to create a mismatch, which I believe the driver will rectify anyways). For the C type, it is specified based on the SQL type, (SQL_DOUBLE -> SQL_C_DOUBLE, etc.). Is there are reason you would need to specify either of these explicitly?

Is there a way to bind a file to parameter instead of the actual value? i.e I have some data in a file and I would like to bind this file to a parameter marker in my SQL instead of reading the contents and passing it.

Currently there is no way to do this. It would be difficult to determine whether the String passed was intended as a String, or indicated a file name. The best thing to do would probably be to use the built-in File System APIs in Node.js and fs.readFile to get your file in a String or Buffer representation, which can then be bound to the parameter.

As a side note, I notice that you are calling a stored procedure with .query. In v2.0.0, there is a .callProcedure function, which allows you to just pass the catalog/schema/name of the procedure and the parameters, and it will create the call query for you behind the scenes. Using .query is ok, but to ODBC there are some small subtle differences between regular queries and calling procedures.

[asztal]

ODBC supports SQLPutData and SQLGetData to stream large objects into and out of the database - of course that's even more work than SQLBindParameter.

I did have both SQLBindParameter and SQLGetData working in the ODBC library I wrote, but it's very out of date now and was too low-level to use for most use cases.

[SabaKauser]

@markdirish Thank you!
While invoking the call, as you said, the sqltype is as we get from server in describe information and I wanted to override it to non-w char equivalent. I found that I can do so by removing the UNICODE define from the binding.gyp and can now get the non-unicode sqltype.
I would be interested to know if you have examples of .callProcedure and SQLPutData for your application somewhere for me to refer.

Thanks in advance!

[markdirish]

The odbc connector does not use SQLPutData anywhere in the code: Parameters are expected to be bound with SQLBindParameter (and are done in the C++ code when a parameter array is passed to .query or .callProcedure).

.callProcedure documentation can be found in the 2.0 README, but it looks like the callback example doesn't actually document it, I will fix. Promise example work though.

To use .callProcedure, you call:

const result = await connection.callProcedure(<catalog>, <schema>, <procedureName>, [<parameters>]);

When passing the array of parameters, the size of the array must match the number of parameters expected by the procedure called. OUT parameters still have to be passed, but can be passed as undefined or null. The result object will contain the result set, but it also has a property parameters that will contain the values of all parameters: IN parameters unchanged, IN/OUT and OUT parameters with their new values.

[ statement: '{ CALL MIRISH.MAXBAL (?) }',
  parameters: [ 3987.5 ],
  return: undefined,
  count: 0,
  columns: [] ]

In the above example, I passed in [undefined] as my parameter, and because its an OUT parameter in the procedure, the value is placed in the result array's 'parameters' array

NOTE It looks like the version on npm has a double free issue that regressed when I was fixing memory leaks. I have solved the problem on my local branch and look to push it today

[ugate]

@markdirish in reference to the question/answer regarding binding file parameters...

Is there a way to bind a file to parameter instead of the actual value? i.e I have some data in a file and I would like to bind this file to a parameter marker in my SQL instead of reading the contents and passing it.
Currently there is no way to do this. It would be difficult to determine whether the String passed was intended as a String, or indicated a file name. The best thing to do would probably be to use the built-in File System APIs in Node.js and fs.readFile to get your file in a String or Buffer representation, which can then be bound to the parameter.

Is there any plan to allow binding a ReadStream using fs.createReadStream? It would seem that instead of using a file path string the ODBC module could use bindParameter instanceof ReadStream?