Option to run only with driver library (without driver manager)

[paulhendrix]

Hi
I have made modifications to node-odbc to communicate direct with sql driver without the need to define data sources etc. In this configuration requirement is only a one shared library of odbc driver (ie. in the current directory). This allows to deploy program without modification of global system configuration (It is even not required to have odbc installed in th system). What do you think about this possibility ?
I have tested this under Windows but it should run under Linux. I'm new to github so please tell me how can I send my modifications ?
Paul

[wankdanker]

Hi @paulhendrix,

I'm not sure I exactly follow what you are describing, so seeing code would be best. To share your code, you should first fork node-odbc from within github. Then apply your changes to your fork and push those changes back to your fork on github. Then we can see what you have done. If we like it, you can then issue a pull request and we can merge your changes.

help.github.com may have some starter guides to help you out.

Hope this helps,

Dan

[paulhendrix]

Hurrah I have made my first commit to github :)

https://github.com/paulhendrix/node-odbc
I have added module dynodbc.*
In this example, driver name is hardcoded in Database.cpp and it is Firebird SQL driver,
but you can replace this with any other odbc driver library.

We can think of an option to dynamically define driver name eg:
var db = new odbc.Database("odbcfb.dll");
if we omit the parameter than it will work standard way, with driver manager.

To compile this do not link in odbc32.lib and odbccp32.lib and add define "dynodbc"
In connection string you dont need to define driver name, only define database eg:
My test connection string:
var connectionString = "SERVER=localhost;DATABASE=localhost:c:\test\test1.fdb;UID=sysdba;PWD=masterkey;";

Paul

[wankdanker]

Cool! Grats on the first commit! :)

So, I'm gathering that the purpose of this would be to bypass the ODBC
driver manager layer and talk directly to the ODBC drivers. Have you seen
any performance improvements by doing this?

Thanks,

Dan

On Mon, Nov 5, 2012 at 4:56 PM, paulhendrix notifications@github.comwrote:

Hurrah I have made my first commit to github :)

https://github.com/paulhendrix/node-odbc
I have added module dynodbc.*
In this example, driver name is hardcoded in Database.cpp and it is
Firebird SQL driver,
but you can replace this with any other odbc driver library.

We can think of an option to dynamically define driver name eg:
var db = new odbc.Database("odbcfb.dll");
if we omit the parameter than it will work standard way, with driver
manager.

To compile this do not link in odbc32.lib and odbccp32.lib and add define
"dynodbc"
In connection string you dont need to define driver name, only define
database eg:
My test connection string:
var connectionString =
"SERVER=localhost;DATABASE=localhost:c:\testy\fm\roxi\versio.fdb;UID=sysdba;PWD=masterkey;";

Paul

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/28#issuecomment-10089823.

[paulhendrix]

I haven't done any benchmarks yet, for me the main difference is that this allows xcopy deploment http://en.wikipedia.org/wiki/XCOPY_deployment
I copy js scripts, node.exe, odbc driver and it just works without any system changes.

Paul

[wankdanker]

This is interesting. We may be able to incorporate this but I would need to see some +1's to move it along. Would also need the pull request to incorporate handling the arguments to the constructor function as you suggested. It should be something like:

var db = new odbc.Database({ driver : "odbcfb.dll" });

[paulhendrix]

I'm glad you think it's interesting :)
Mayby we can use connection string to include driver library name ?
eg:
"DRIVERLIB=odbcfb.dll;SERVER=localhost;DATABASE=localhost:c:\test\test1.fdb;UID=sysdba;PWD=masterkey;"

[Dave-Snigier]

FWIW I would be very interested in this functionality as well. I currently have to work within the contraints of a rather old server so just about everything I do requires me to compile from source. This would ease that pain as I could talk directly to a driver that's already installed versus attempting to compile unixODBC.

I also do my migrations via a direct copy (rsync).

[wankdanker]

Hi @paulhendrix,

I have integrated your patch into my v0.5 tree (https://github.com/wankdanker/node-odbc/tree/v0.5). I have successfully tested it with libsqlite3odbc on linux. Queries per second increased by ~5k when using this method.

To use it you need to modify binding.gyp to include the flag 'dynodbc' and remove the library references -lodbc or odbc32.lib as you mentioned before ie:

{
  'targets' : [
    {
      'target_name' : 'odbc_bindings',
      'sources' : [ 
        'src/odbc.cpp',
        'src/odbc_connection.cpp',
        'src/odbc_statement.cpp',
        'src/odbc_result.cpp',
        'src/dynodbc.cpp'
      ],
      'defines' : [
        'dynodbc'
      ],
      'conditions' : [
        [ 'OS == "linux"', {
          'libraries' : [ 

          ],
          'cflags' : [
            '-g'
          ]
        }],
        [ 'OS == "mac"', {
          'libraries' : [ 

          ]
        }],
        [ 'OS=="win"', {
          'sources' : [
            'src/strptime.c',
            'src/odbc.cpp'
          ],
          'libraries' : [ 

          ]
        }]
      ]
    }
  ]
}

After compiling you can specify the library from javascript-land like this:

var odbc = require('odbc');
odbc.library = 'libsqlite3odbc';

//or

var db = require('odbc')({ library : 'libsqlite3odbc' });

If anyone is still interested in this feature, please checkout my v0.5 branch, compile, hack and fix bugs that you find.

Thanks,

Dan

[amcintyre99]

Will I be able to use this feature with DB2? to avoid any UNIXODBC overhead?

[wankdanker]

Any DB2 specifics are out of the scope of my work. My primary goal is to provide an interface to ODBC and it's various drivers on any platform that nodejs runs on, with the ODBC overhead. However, there is a feature which can be enabled in binding.gyp (see https://github.com/wankdanker/node-odbc/tree/v0.5#build-options) that will allow you to bypass the ODBC driver management layer and access a specific ODBC driver directly. It's totally untested and debugging it will probably be left up to the user. As it does not fit my or my company's use case.

The one benchmark I ran under the direct to driver scenario did see a substantial increase in queries per second. So, it would probably be worth exploring if that is what you need.

[wankdanker]

Ha. Just realized this is a different issue than what you were working on before. Sorry for the drawn-out explanation. Yeah, you can use what is discussed in this thread to bypass the ODBC driver layer. :)

[amcintyre99]

tried this with db2 - old 9.7 32bit

https://gist.github.com/amcintyre99/5486102

[wankdanker]

Yeah, I ran in to the same problem when trying it with the FreeTDS driver. But it worked fine with the libsqlite3odbc driver. Not sure what the problem is...

[amcintyre99]

Got this working with db2 97 32bit using:
var odbc = require('odbc')
odbc.library = 'libodbc'

Using standard db2 profile which sets:
LD_LIBRARY_PATH=xxxxxx/sqllib/lib32

I will try it on db2 10 64bit in the next few days. I'm trying to see what each change is doing to performance.

[amcintyre99]

testing dynodbc...

npm rebuild odbc works on my old box with node 10.5 - and my testing works

but on new box with node 8.23 npm rebuild odbc fails... see messages below... do I need to go to 10.5 for this to work?

$ npm rebuild odbc

> odbc@0.5.9 preinstall /home/andrew/dma/prd/node_modules/odbc
> node-gyp configure build

make: Entering directory `/home/andrew/dma/prd/node_modules/odbc/build'
  CXX(target) Release/obj.target/odbc_bindings/src/odbc.o
  CXX(target) Release/obj.target/odbc_bindings/src/odbc_connection.o
  CXX(target) Release/obj.target/odbc_bindings/src/odbc_statement.o
../src/odbc_statement.cpp: In static member function ‘static void ODBCStatement::UV_AfterExecuteNonQuery(uv_work_t*, int)’:
../src/odbc_statement.cpp:353:57: error: cannot convert ‘SQLLEN* {aka long int*}’ to ‘SDWORD* {aka int*}’ in argument passing
../src/odbc_statement.cpp: In static member function ‘static v8::Handle<v8::Value> ODBCStatement::ExecuteNonQuerySync(const v8::Arguments&)’:
../src/odbc_statement.cpp:414:57: error: cannot convert ‘SQLLEN* {aka long int*}’ to ‘SDWORD* {aka int*}’ in argument passing
make: *** [Release/obj.target/odbc_bindings/src/odbc_statement.o] Error 1
make: Leaving directory `/home/andrew/dma/prd/node_modules/odbc/build'
gyp ERR! build error 
gyp ERR! stack Error: `make` failed with exit code: 2
gyp ERR! stack     at ChildProcess.onExit (/usr/lib/nodejs/npm/node_modules/node-gyp/lib/build.js:267:23)
gyp ERR! stack     at ChildProcess.EventEmitter.emit (events.js:99:17)
gyp ERR! stack     at Process._handle.onexit (child_process.js:680:10)
gyp ERR! System Linux 3.2.0-40-generic
gyp ERR! command "node" "/usr/lib/nodejs/npm/node_modules/node-gyp/bin/node-gyp.js" "configure" "build"
gyp ERR! cwd /home/andrew/dma/prd/node_modules/odbc
gyp ERR! node -v v0.8.23
gyp ERR! node-gyp -v v0.9.5
gyp ERR! not ok 
npm ERR! odbc@0.5.9 preinstall: `node-gyp configure build`
npm ERR! `sh "-c" "node-gyp configure build"` failed with 1
npm ERR! 
npm ERR! Failed at the odbc@0.5.9 preinstall script.
npm ERR! This is most likely a problem with the odbc package,
npm ERR! not with npm itself.
npm ERR! Tell the author that this fails on your system:
npm ERR!     node-gyp configure build
npm ERR! You can get their info via:
npm ERR!     npm owner ls odbc
npm ERR! There is likely additional logging output above.

npm ERR! System Linux 3.2.0-40-generic
npm ERR! command "nodejs" "/usr/bin/npm" "rebuild" "odbc"
npm ERR! cwd /home/andrew/dma/prd
npm ERR! node -v v0.8.23
npm ERR! npm -v 1.2.18
npm ERR! code ELIFECYCLE
npm ERR! 
npm ERR! Additional logging details can be found in:
npm ERR!     /home/andrew/dma/prd/npm-debug.log
npm ERR! not ok code 0

npm-debug.log

0 info it worked if it ends with ok
1 verbose cli [ 'nodejs', '/usr/bin/npm', 'rebuild', 'odbc' ]
2 info using npm@1.2.18
3 info using node@v0.8.23
4 verbose read json /home/andrew/dma/prd/package.json
5 verbose read json /home/andrew/dma/prd/node_modules/odbc/package.json
6 verbose read json /home/andrew/dma/prd/node_modules/oauth/package.json
7 verbose read json /home/andrew/dma/prd/node_modules/hexy/package.json
8 verbose read json /home/andrew/dma/prd/node_modules/microtime/package.json
9 verbose read json /home/andrew/dma/prd/node_modules/odbc/node_modules/bindings/package.json
10 verbose read json /home/andrew/dma/prd/node_modules/microtime/node_modules/bindings/package.json
11 verbose readInstalled returning odbc@0.5.9
12 verbose readInstalled returning oauth@0.9.10
13 verbose readInstalled returning hexy@0.2.5
14 verbose readInstalled returning microtime@0.3.3
15 verbose readInstalled returning bindings@1.0.0
16 verbose readInstalled returning bindings@1.0.0
17 verbose readInstalled returning undefined
18 info readInstalled object
19 verbose rebuild path, id [ '/home/andrew/dma/prd/node_modules/odbc', 'odbc@0.5.9' ]
20 silly rebuild set [ '/home/andrew/dma/prd/node_modules/odbc' ]
21 info build /home/andrew/dma/prd/node_modules/odbc
22 verbose from cache /home/andrew/dma/prd/node_modules/odbc/package.json
23 info preinstall odbc@0.5.9
24 verbose unsafe-perm in lifecycle true
25 silly exec sh "-c" "node-gyp configure build"
26 silly sh,-c,node-gyp configure build,/home/andrew/dma/prd/node_modules/odbc spawning
27 info odbc@0.5.9 Failed to exec preinstall script
28 error odbc@0.5.9 preinstall: `node-gyp configure build`
28 error `sh "-c" "node-gyp configure build"` failed with 1
29 error Failed at the odbc@0.5.9 preinstall script.
29 error This is most likely a problem with the odbc package,
29 error not with npm itself.
29 error Tell the author that this fails on your system:
29 error     node-gyp configure build
29 error You can get their info via:
29 error     npm owner ls odbc
29 error There is likely additional logging output above.
30 error System Linux 3.2.0-40-generic
31 error command "nodejs" "/usr/bin/npm" "rebuild" "odbc"
32 error cwd /home/andrew/dma/prd
33 error node -v v0.8.23
34 error npm -v 1.2.18
35 error code ELIFECYCLE
36 verbose exit [ 1, true ]

[wankdanker]

You are hitting a bug that I just fixed before I left the office. I didn't push out the changes though. That particular issue is due to using SQLRowCount in ODBCStatement::ExecuteNonQuery[Sync]. Which was not in use before the last time I tested dynodbc. Will push those out in a few.

[amcintyre99]

but it works on my old 32bit 10.5 box with your latest code - just not my new 64bit 8.23 box, which will be going to 10.5 soon

[wankdanker]

Weird. Just pushed out those changes I mentioned earlier if you want to test it out.

[amcintyre99]

yep, npm rebuild works now on 8.23 64bit

dynodbc with your executeNonQuery bench run same as in other thread - so no improvement that i can see

[wankdanker]

Not sure what library you specified for your test, but I think that if you specify libodbc it will just be loading the odbc driver manager and you won't be gaining anything. I just pushed out a change that will print to the console the name of any function that is unable to be dynamically loaded from the specified library. In the gist you posted the other day (https://gist.github.com/amcintyre99/5486102) it would specify why it failed instead of saying nothing...

I was just trying this with a driver and it looks like the driver needs to be completely odbc v2 compatible. We are using a mixture of v2 and v3 function calls in node-odbc. We need to get that straightened out before we can probably get this to work.

The odbc driver manager maps v2 calls to their appropriate v3 function call if the odbc driver does not implement the v2 call.

[amcintyre99]

I'll try it again soon with your latest change....

Re odbc levels. Not sure if this helps any but it shows what db2 handles:
http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.apdv.cli.doc/doc/r0000553.html

And in case you feel the urge:
http://www.ibm.com/developerworks/downloads/im/db2express/index.html

[amcintyre99]

Using db2 libdb2.so on 97 32bit:

$ node bench-prepare-executeNonQuery.js
node-odbc: error loading function: SQLDrivers

/home/andrew/dma/prd/node_modules/odbc/lib/odbc.js:48
throw new Error("Could not load library. You may need to specify full "
^
Error: Could not load library. You may need to specify full path.

[wankdanker]

Yeah, that actually makes sense. Why would the driver need to implement the SQLDrivers function? If you want to try one-by-one you can comment out the lines in dynodbc.ccp that cause the failure and then rebuild.

For example SQLDrivers is on this line: https://github.com/wankdanker/node-odbc/blob/v0.5/src/dynodbc.cpp#L139

[amcintyre99]

That was the only one I had to comment out and now that test runs fine. Is that line required?

Old box is to slow to see if there is any difference. I'll try this again on faster box later.

[wankdanker]

Nope. We never call SQLDrivers (at least yet) from node-odbc so if it works without that, then you should be good.

[amcintyre99]

So if this works for me and gets me any significant decrease, are you saying I would have to fork the code myself?

I'm already confused enough. :-)

[wankdanker]

Hahaha. No, I can comment that line out and push that out since we don't use it and it would never be used in a direct-to-driver situation any way. In the future I may have to add a flag to specify which type of dynodbc you are doing (direct-to-driver or direct-to-libodbc).

I just pushed it out.

[amcintyre99]

8.23 64bit slightly better with dynodbc on

$ node bench-prepare-executeNonQuery.js
100000 queries issued in 19.404 seconds, 5153/sec : Prepare - ExecuteNonQuery

[amcintyre99]

Wait, the test above was with the 32bit libdb2.so on a 64bit box. I should have been testing with the 64bit libdb2o.so.

When I do I get this:
$ node bench-prepare-executeNonQuery.js
node-odbc: error loading ODBC library: /home/db2inst1/sqllib/lib64/libdb2o.so: undefined symbol: sqlhaResourceModelRecreate

/home/andrew/dma/prd/node_modules/odbc/lib/odbc.js:48
throw new Error("Could not load library. You may need to specify full "
^
Error: Could not load library. You may need to specify full path.

[amcintyre99]

That 64bit libdb2o.so driver is specified in my odbcinst.ini file so it works fine without dynodbc.

[wankdanker]

It seems as if libdb2o.so requires some other library which is not loaded. Don't know how to resolve that. :-\

sqlhaResourceModelRecreate Is not any function that we use in node-odbc and is probably specific to 64 bit db2 stuff.

[wankdanker]

I'm going to consider this issue closed. If you have any further issues we should create a new issue for the specific problem.

Thanks for your help with testing, @amcintyre99.

[amcintyre99]

just tried this again at node 10.15, odbc 5.25 and db2 10.5 (was at db2 10.1 before) and dynodbc works!! no undefined symbol.... dynodbc only, and no unicode

using odbc.library = '/home/db2inst1/sqllib/lib64/libdb2o'

will report more results end of day wed

[paulhendrix]

That's great !
I think that driver manager in odbc has more drawback than advantages, direct communication with odbc driver is far more easier (configuration, dll hell)
Paul

[amcintyre99]

db2 cpu usage about 15% lower today (harder to compare now with volume differences) - so that's great!!

but i just realized i made two changes, dynodbc and no unicode - so really unsure which one helped