Comments (8)
This will be solved in v0.5 and can be tested from here: https://github.com/wankdanker/node-odbc/tree/v0.5
There is a result set object which has fetch() and fetchAll() methods. fetch() will get a single record asynchronously from the database.
var db = require('odbc')();
db.open(process.env.ODBC_CONNECTION_STRING, function (err) {
db.queryResult('select * from orders', function (err, result) {
//now you have to use some async callback management
//to fetch all your rows. But here we just get the first one:
result.fetch(function (err, data) {
//the first record
console.log(data);
});
});
});
I will leave this issue open until these changes make their way to an npm release.
from node-odbc.
This is done. fetch
has been implemented so that individual rows can be requested and dealt with one at a time rather than allocating all the memory for a large set at once.
from node-odbc.
Hello, I tried out the latest v0.5.25 branch and noticed that queryResult still resulted in large memory consumption (all the memory on my VM). To try and simplify things as much as possible I open a connection and run queryResult without ever fetching any of the rows. The queryResult call takes about 15 to 30 seconds before the provided callback is executed. During this time the memory usage steadily increases. The test query I run returns 5 million records.
Has anyone else noticed this behavior? Could I be doing something wrong or perhaps the libraries I'm using have known issues? Thanks for your great work on this module!
(If you'd like me to open a new issue on your fork I'd be happy to do that)
db.open(process.env.ODBC_CONNECTION_STRING, function (err) {
db.queryResult('select * from orders', function (err, result) {
//queryResult call takes 15 to 30 seconds to return during which time memory consumption
//increases until all memory is used on the machine (no calls to fetch or fetchAll are made
//The process does not crash due to swap I think and the query does eventually complete but
//but the spike in memory consumption makes me nervous.
});
});
My specs:
Fedora 15
Node v0.10.13
node-odbc 0.5.25
postgres 9.1rc1
postgressql-odbc-09.00.0200-2.fc15.i686
from node-odbc.
Hey @sarus,
I haven't seen this happen before (not that I have tested it extensively). I did just test this on Ubuntu with the MS SQL Native Client and selecting * from a table with 4.8 million rows had no noticable increase in memory usage when using queryResult
. Memory usage did increase as I fetched rows.
I wonder if the Postgres ODBC driver is pre-loading the rows. Or using a client side cursor by default. I don't even know if those things are possible though. Something is definitely fishy here.
At this time, the only way I can think to really test if it is just the ODBC driver doing this is to write a test using ODBC in C that just connects to the database and then executes the same query. If you want to try this, you could use test/odbc-bench.c
as a starting point.
To do that you would need to change the connection string at line 48, comment out lines 63, 68-70 and 73, and change the SQL query on line 66. Then compile with gcc odbc-bench.c -lodbc -o odbc-bench
. Watch the memory usage while executing ./odbc-bench.
If you try this, then maybe we can pinpoint if this is a Postgres ODBC issue or something that is going on within this module.
Thanks,
Dan
from node-odbc.
Thank you for the great debugging suggestions. I went ahead and modified the test/odbc-bench.c
file as you outlined in your post. The test program immediately started using up all available memory on my system and started using swap. Based on this test I did some research on the postgres odbc driver and found out that the default behavior is to fetch all rows into memory.
You can override this behavior by adding the UseDeclareFetch = 1
to odbc.ini
. Sure enough after adding this flag the test program ran and returned immediately with no spike in memory usage. Thinking the problem was solved I ran a query from Node.js and my node program immediately crashed. The problem was now with the database.query(<query>,<callback>)
call which was no longer working. Setting UseDeclareFetch = 1
was causing this new issue (I turned it off and on a couple times to confirm)
Essentially, the following code crashes hard when using UseDeclareFetch = 1
in odbc.ini
var client = new odbc.Database();
client.open(dbConnectionString, function(error, db){
// This now fails (hard crash of the node process)
client.query("SELECT * FROM testTable", callback);
// The callback is never called and no error is provided
});
I went ahead and rebuilt node-odbc with the debug flag on. Here is the debug output:
ODBC::CreateConnection
ODBC::UV_CreateConnection
ODBC::UV_AfterConnetion
ODBCConnection::New
ODBCConnection::Open
ODBCConnection::UV_Open
ODBCConnection::UV_AfterOpen
ODBCConnection::Query
ODBCConnection::Query : sqlLen=134, sqlSize=270, sql=S
ODBCConnection::UV_Query
ODBCConnection::UV_AfterQuery
ODBCResult::New
ODBCResult::New m_hDBC=92F2E88 m_hDBC=92E6AC8 m_hSTMT=B5420418 canFreeHandle=1
ODBCResult::FetchAll
ODBCResult::UV_FetchAll
ODBCResult::UV_AfterFetchAll
ERROR: [WID-1] Died. //this is my own error catching routine
I then started adding DEBUG_PRINTF
statements everywhere to try and narrow the problem down. The crash was happening in odbc_result.cpp
on line 486:
self->columns = ODBC::GetColumns(self->m_hSTMT, $self->colCount);
Digging a little more the problem is in odbc.cpp
in the ODBC::GetColumns
method. The program actually crashes right at line 301 in the call to SQLColAttribute
. Note that the loop does execute properly a couple of times before crashing.
ret = SQLColAttribute(hStmt, columns[i].index,
#ifdef STRICT_COLUMN_NAMES
...
It turns out the call to SQLNumResultCols(hStmt, colCount)
on line 283 appears to be returning a bogus value for the number of columns. colCount
is properly initialized to 0 but then after the call to SQLNumResultCols
the colCount
value becomes 256 (it should be 7). I just added the following print statement to find this:
DEBUG_PRINTF("ODBC::GetColumns::NumColumns is %i\n", *colCount)
I'm definitely not a C++ person so I assume this is the correct way to format the print statement. Confused I went back to the odbc-bench.c
program and tried adding in a call to SQLNumResultCols
there to see if it returned the right number after the call to SQLExecDirect
on line 66 (it worked as expected).
short colCount;
ret = SQLNumResultCols(m_hStmt, &colCount);
printf("Column Count: %i\n", colCount); //returns 7 which is the correct value
At this point I ran out of ideas on where to look. Sorry for the long post but wanted to be thorough. The bottom line is that the entire node-odbc module crashes hard when UseDeclareFetch = 1
is set for postgresODBC. My best guess at the moment is that it has something to do with the incorrect number of columns returned by SQLNumResultsCol
inside of ODBC::GetColumns
. I also tried updating to the latest postgres ODBC driver but the problem did not go away with the latest driver (same exact issue).
Any ideas??
Thank you!
from node-odbc.
Thanks for all the details. That's excellent. I found this thread which might be relevant:
Wondering if it is required to specify a value for FETCH in the connection string when using UseDeclareFetch=1. If the default value for FETCH is 0, maybe the driver does not know any information about the columns because it hasn't fetched any yet. I'll have to get my postgres server back up and see if I can reproduce the issue, but it might take me some time.
from node-odbc.
Hello,
So I think I figured out the issue and it was not what I was expecting. To start I did have the Fetch
parameter set in odbc.ini
so my file had the following:
# Turn on Fetch support to prevent postgres from loading results into memory
UseDeclareFetch = 1
# Number of rows to fetch each time
Fetch = 1000
The crash however still occurred. I beefed up the odbc-bench.c
test program to include fetching the data and it worked just fine (but my actual code kept crashing). I'm not entirely sure why but at some point I remember that the actual queries I was running from Node.js included SQL comments in them of the form --
which I did not use in odbc-bench.c
. As it turns out this was the problem.
Query that causes crashes with UseDeclareFetch = 1
:
SELECT * FROM testTable -- User: Admin
For some reason when UseDeclareFetch is turned on the comment crashes node-odbc (almost certainly because of a bug somewhere in postgresodbc). It works fine when UseDeclareFetch is not turned on (but then you can't return large result sets). The other comment style does work however so if you change the above query to use the /* syntax as shown below everything works.
SELECT * FROM testTable /* User: Admin */
Bottom Line: I think the bug is in the postgres odbc driver (not node-odbc) and I figured out a suitable workaround. Hopefully this helps someone else in the future!
Thank you again for your hard work on such a useful module. It's greatly appreciated!
from node-odbc.
Yeah, that's pretty unexpected! Glad you figured it out.
Based on what I have seen in some output logs of other folks debugging UseDeclareFetch
issues, it looks like the Postgres driver wraps the original query in a cursor declaration and fetch command like the following:
declare "P01304" cursor for select * from your_original_statement -- this is a comment which blocks the following also added by the driver ;fetch 100 in "P01304
Where select * from your_original_statement -- this is a comment which blocks the following also added by the driver
is the entire statement submitted by the user. Including comments by '--' would also comment out the fetch command that is added by the driver after the statement.
Wonder if the Postgres ODBC developers are aware of this.
Thank you for your effort in debugging the issue.
from node-odbc.
Related Issues (20)
- Error '[node-odbc] Error in ODBCResult::MoreResultsSync', HOT 1
- support for SQL_SUCCESS_WITH_INFO return HOT 1
- endless loop HOT 1
- node-odbc incompatible with Electron? HOT 3
- Error while installing package [Failed at the [email protected] install script 'node-gyp configure build'.]
- npm install error OSX HOT 2
- code fails with latest version of node-odbc as well as ibm_db
- node-odbc SSL encryption support? HOT 1
- Unsupported platform for [email protected] HOT 1
- Support more ODBC Features
- Statically linked Library
- npm WARN lifecycle [email protected]~preinstall: cannot run in wd %s %s (wd=%s) [email protected] node-gyp configure build /usr/src/pal/node_modules/odbc HOT 3
- Row count when doing UPDATE, INSERT, or DELETE via querySync()
- iso-8859-15 dont show character
- Build error when the UNICODE define is removed from binding.gyp HOT 2
- Windows odbc.open crashes on 2nd invocation with Microsoft Access Driver
- Only returning first character on macOS. HOT 2
- How to prevent conversion of datetime string to JS Date object? HOT 2
- How does odbc handle sql injection HOT 2
- odbc.Database is not a constructor HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from node-odbc.