I need to get the number of rows in a query. I only see number of columns with ResultSet_getColumnCount
Please advice.
Thanks Joe
ResultSet is designed as a dynamic result sets and the size of the set is not known a priori. That is, instead of retrieving the full result set once, only a few rows are retrieved when needed and when there are no more rows, ResultSet_next returns false. The idea of this design is to save memory and be able to retrieve GB sized result sets without using GB of memory.
That was the original idea, but it is only implemented for MySQL and possibly for Oracle, the other drivers retrieve the full set AFAIK. Though it is unknown if libpq (postgres) tries to do some similar caching internally. Anyway, the result of this is that number of rows in a result set is not known beforehand at the libzdb API level.
To get the number of rows in a set, you will have to use something like 'select count(*)' first.
On Apr 6, 2012, at 4:05 PM, Joe Flemmings wrote:
I need to get the number of rows in a query. I only see number of columns with ResultSet_getColumnCount
Please advice.
Thanks Joe
Is their a flag to return the whole result set as it would kinda be expensive to add another select statement. The query is very extensive.
Joe
On Fri, Apr 6, 2012 at 8:11 AM, Jan-Henrik Haukeland hauk@tildeslash.comwrote:
ResultSet is designed as a dynamic result sets and the size of the set is not known a priori. That is, instead of retrieving the full result set once, only a few rows are retrieved when needed and when there are no more rows, ResultSet_next returns false. The idea of this design is to save memory and be able to retrieve GB sized result sets without using GB of memory.
That was the original idea, but it is only implemented for MySQL and possibly for Oracle, the other drivers retrieve the full set AFAIK. Though it is unknown if libpq (postgres) tries to do some similar caching internally. Anyway, the result of this is that number of rows in a result set is not known beforehand at the libzdb API level.
To get the number of rows in a set, you will have to use something like 'select count(*)' first.
On Apr 6, 2012, at 4:05 PM, Joe Flemmings wrote:
I need to get the number of rows in a query. I only see number of
columns with ResultSet_getColumnCount
Please advice.
Thanks Joe
-- To unsubscribe: http://www.tildeslash.com/mailman/listinfo/libzdb-general
On Apr 6, 2012, at 5:54 PM, Joe Flemmings wrote:
Is their a flag to return the whole result set as it would kinda be expensive to add another select statement. The query is very extensive.
Unfortunately, no. It would require internal changes to libzdb to provide this and as I mentioned it would then use a lot more memory.
I assume the reason you need to know the full result set size is to allocate some data structure to hold the result? If that is the case, why not use a dynamic data structure such as a hash table or a dynamic array? Maybe even the vector ADT used by libzdb internally?
I need it as i'm passing the results to lua and need to know the table size in advance before sending to lua. Their is no other way to pass results to lua from c without knowing the size first.
Joe
On Fri, Apr 6, 2012 at 9:19 AM, Jan-Henrik Haukeland hauk@tildeslash.comwrote:
On Apr 6, 2012, at 5:54 PM, Joe Flemmings wrote:
Is their a flag to return the whole result set as it would kinda be
expensive to add another select statement. The query is very extensive.
Unfortunately, no. It would require internal changes to libzdb to provide this and as I mentioned it would then use a lot more memory.
I assume the reason you need to know the full result set size is to allocate some data structure to hold the result? If that is the case, why not use a dynamic data structure such as a hash table or a dynamic array? Maybe even the vector ADT used by libzdb internally? -- To unsubscribe: http://www.tildeslash.com/mailman/listinfo/libzdb-general
On Apr 6, 2012, at 6:46 PM, Joe Flemmings wrote:
I need it as i'm passing the results to lua and need to know the table size in advance before sending to lua. Their is no other way to pass results to lua from c without knowing the size first.
I have no experience with lua, but can't you create a native lua interface to ResultSet and only send the ResultSet pointer to lua and have lua iterate over the set? Otherwise I'm out of suggestions other than changing libzdb. The required changes in libzdb is not complicated, but it is more a question of design and time.
That would be nice but in lua you create a table in C that you pass to lua. I will add another query to select then go from their.
Thank You for your help.
On Fri, Apr 6, 2012 at 10:07 AM, Jan-Henrik Haukeland hauk@tildeslash.comwrote:
On Apr 6, 2012, at 6:46 PM, Joe Flemmings wrote:
I need it as i'm passing the results to lua and need to know the table
size in advance before sending to lua. Their is no other way to pass results to lua from c without knowing the size first.
I have no experience with lua, but can't you create a native lua interface to ResultSet and only send the ResultSet pointer to lua and have lua iterate over the set? Otherwise I'm out of suggestions other than changing libzdb. The required changes in libzdb is not complicated, but it is more a question of design and time.
-- To unsubscribe: http://www.tildeslash.com/mailman/listinfo/libzdb-general
On Apr 6, 2012, at 7:21 PM, Joe Flemmings wrote:
That would be nice but in lua you create a table in C that you pass to lua.
If you don't mind continue the discussion a little bit longer? I was curious and went and looked at lua and C integration (http://www.lua.org/pil/26.1.html) and came across this example for reading a directory which kind of maps conceptually to a result set.
static int l_dir (lua_State *L) { DIR *dir; struct dirent *entry; int i; const char *path = luaL_checkstring(L, 1);
/* open directory */ dir = opendir(path); if (dir == NULL) { /* error opening the directory? */ lua_pushnil(L); /* return nil and ... */ lua_pushstring(L, strerror(errno)); /* error message */ return 2; /* number of results */ }
/* create result table */ lua_newtable(L); i = 1; while ((entry = readdir(dir)) != NULL) { lua_pushnumber(L, i++); /* push key */ lua_pushstring(L, entry->d_name); /* push value */ lua_settable(L, -3); }
closedir(dir); return 1; /* table is already on top */ }
As far as I can see, lua tables are dynamic hash tables and from the example above there is no need to know the number of entries beforehand.
I simply rewrote the above example to this general C method that can be called from lua and will return any result set given any sql select statement. I have no idea if it will it will compile or has errors, but according to the documentation something like this should be possible. Maybe useful?
static int l_select (lua_State *L) { int result = 1; /* table is on top if all goes well */ const char *select = luaL_checkstring(L, 1); Connection_T c = ConnectionPool_getConnection(pool); // pool is usually global TRY { ResultSet_T r = Connection_executeQuery(c, select); /* create result table */ lua_newtable(L); int columnCount = ResultSet_getColumnCount(r); while (ResultSet_next(r)) { for (int i = 1; i < columnCount; i++) { lua_pushstring(L, ResultSet_getColumnName(r, i)); /* push column name */ lua_pushstring(L, ResultSet_getString(r, i)); /* push value as a string */ lua_settable(L, -3); } } } ELSE { lua_pushnil(L); /* return nil and ... */ lua_pushstring(L, Exception_frame.message); /* error message */ result = 2; /* number of results */ } FINALLY { Connection_close(c); } END_TRY; return result; }
Thanks,
I use lua_createtable(L,0,num_rows); but can try this as well.
Joe
On Fri, Apr 6, 2012 at 11:28 AM, Jan-Henrik Haukeland hauk@tildeslash.comwrote:
On Apr 6, 2012, at 7:21 PM, Joe Flemmings wrote:
That would be nice but in lua you create a table in C that you pass to
lua.
If you don't mind continue the discussion a little bit longer? I was curious and went and looked at lua and C integration ( http://www.lua.org/pil/26.1.html) and came across this example for reading a directory which kind of maps conceptually to a result set.
static int l_dir (lua_State *L) { DIR *dir; struct dirent *entry; int i; const char *path = luaL_checkstring(L, 1);
/* open directory */ dir = opendir(path); if (dir == NULL) { /* error opening the directory? */ lua_pushnil(L); /* return nil and ... */ lua_pushstring(L, strerror(errno)); /* error message */ return 2; /* number of results */ } /* create result table */ lua_newtable(L); i = 1; while ((entry = readdir(dir)) != NULL) { lua_pushnumber(L, i++); /* push key */ lua_pushstring(L, entry->d_name); /* push value */ lua_settable(L, -3); } closedir(dir); return 1; /* table is already on top */}
As far as I can see, lua tables are dynamic hash tables and from the example above there is no need to know the number of entries beforehand.
I simply rewrote the above example to this general C method that can be called from lua and will return any result set given any sql select statement. I have no idea if it will it will compile or has errors, but according to the documentation something like this should be possible. Maybe useful?
static int l_select (lua_State *L) { int result = 1; /* table is on top if all goes well */ const char *select = luaL_checkstring(L, 1); Connection_T c = ConnectionPool_getConnection(pool); // pool is usually global TRY { ResultSet_T r = Connection_executeQuery(c, select); /* create result table */ lua_newtable(L); int columnCount = ResultSet_getColumnCount(r); while (ResultSet_next(r)) { for (int i = 1; i < columnCount; i++) { lua_pushstring(L, ResultSet_getColumnName(r, i)); /* push column name */ lua_pushstring(L, ResultSet_getString(r, i)); /* push value as a string */ lua_settable(L, -3); } } } ELSE { lua_pushnil(L); /* return nil and ... */ lua_pushstring(L, Exception_frame.message); /* error message */ result = 2; /* number of results */ } FINALLY { Connection_close(c); } END_TRY; return result; }
-- To unsubscribe: http://www.tildeslash.com/mailman/listinfo/libzdb-general
On Apr 6, 2012, at 8:53 PM, Joe Flemmings wrote:
Thanks,
I use lua_createtable(L,0,num_rows); but can try this as well.
Looking at my code a second time, you probably need one more step where each row is a new lua table. So you will have a table of tables. In my example, I probably overwrite the table key at each loop. But I guess you already have figured it out. Anyway, glad to be of help.
Yes, thanks.
Joe
On Fri, Apr 6, 2012 at 12:08 PM, Jan-Henrik Haukeland hauk@tildeslash.comwrote:
On Apr 6, 2012, at 8:53 PM, Joe Flemmings wrote:
Thanks,
I use lua_createtable(L,0,num_rows); but can try this as well.
Looking at my code a second time, you probably need one more step where each row is a new lua table. So you will have a table of tables. In my example, I probably overwrite the table key at each loop. But I guess you already have figured it out. Anyway, glad to be of help. -- To unsubscribe: http://www.tildeslash.com/mailman/listinfo/libzdb-general
-
Jan-Henrik Haukeland -
Joe Flemmings