Re: [GRASS-user] Max length of sql_query in v.extract
On 28/01/2010 00:45, Markus Neteler wrote: On Wed, Jan 27, 2010 at 8:35 PM, Luigi Ponti lpo...@infinito.it wrote: Hello, Is there a length limit to a where sql statement fed to v.extract? I am getting an ... Error in db_open_select_cursor() ... in the middle of a very long query. Could you please try to analyse with g.gisenv set=DEBUG=3 to identify when it gets lost? And perhaps provide an example based on the Spearfish or North Carolina data sets? Version: WinGRASS-6.4.SVN-r40650-1 (installed in C:\GRASS-64-SVN i.e. no spaces in path; OS Windows 7) Dataset: North Caroline dataset Mapset: user1 vector: census_wake2000 (this actually resides in PERMANENT) Working on the Cmd input box of the wxGUI. I have chosen a case analogous to that where I encountered the error, i.e. a DBF Character field containing 105 different numeric codes (e.g. 050100) one per record: (note that you can find full debug output in a text file available at the following link: https://docs.google.com/leaf?id=0B0EpsfMZ-IZYOTJiYWVmNjUtNGYzMC00NDM5LWI1NDYtZTMyNzg3M2NjOThksort=namelayout=listnum=50 I thought 20,000+ lines was too much for the mailing list but please advice if had better taken a different course of action.) ### START GRASS CMD HISTORY ### g.gisenv set=DEBUG=3 (Thu Jan 28 12:12:34 2010) Command finished (0 sec) (Thu Jan 28 12:12:43 2010) v.extract input=census_wake2000 output=census_extract_all_trt2000 where=(TRT2000='050100')or(TRT2000='050300')or(TRT2000='050400')or(TRT2000='050500')or(TRT2000='050600')or(TRT2000='050700')or(TRT2000='050800')or(TRT2000='050900')or(TRT2000='051000')or(TRT2000='051100')or(TRT2000='051200')or(TRT2000='051400')or(TRT2000='051501')or(TRT2000='051502')or(TRT2000='051600')or(TRT2000='051700')or(TRT2000='051800')or(TRT2000='051900')or(TRT2000='052001')or(TRT2000='052002')or(TRT2000='052101')or(TRT2000='052102')or(TRT2000='052201')or(TRT2000='052202')or(TRT2000='052301')or(TRT2000='052302')or(TRT2000='052401')or(TRT2000='052402')or(TRT2000='052404')or(TRT2000='052405')or(TRT2000='052501')or(TRT2000='052503')or(TRT2000='052504')or(TRT2000='052601')or(TRT2000='052602')or(TRT2000='052603')or(TRT2000='052701')or(TRT2000='052703')or(TRT2000='052704')or(TRT2000='052705')or(TRT2000='052801')or(TRT2000='052802')or(TRT2000='052803')or(TRT2000='052804')or(TRT2000='052805')or(TRT2000='052900')or(TRT2000='053001')or(TRT2000='053002')or(TRT2000='053101')or(TRT2000='053103')or(TRT2000='053104')or(TRT2000='053200')or(TRT2000='053402')or(TRT2000='053403')or(TRT2000='053404')or(TRT2000='053405')or(TRT2000='053406')or(TRT2000='053407')or(TRT2000='053501')or(TRT2000='053505')or(TRT2000='053506')or(TRT2000='053507')or(TRT2000='053508')or(TRT2000='053509')or(TRT2000='053510')or(TRT2000='053512')or(TRT2000='053513')or(TRT2000='053514')or(TRT2000='053515')or(TRT2000='053600')or(TRT2000='053703')or(TRT2000='053706')or(TRT2000='053707')or(TRT2000='053709')or(TRT2000='053710')or(TRT2000='053711')or(TRT2000='053712')or(TRT2000='053713')or(TRT2000='053714')or(TRT2000='053715')or(TRT2000='053716')or(TRT2000='053801')or(TRT2000='053802')or(TRT2000='053900')or(TRT2000='054001')or(TRT2000='054003')or(TRT2000='054004')or(TRT2000='054006')or(TRT2000='054007')or(TRT2000='054008')or(TRT2000='054009')or(TRT2000='054010')or(TRT2000='054102')or(TRT2000='054104')or(TRT2000='054105')or(TRT2000='054106')or(TRT2000='054107')or(TRT2000='054108')or(TRT2000='054109')or(TRT2000='054201')or(TRT2000='054202')or(TRT2000='054301')or(TRT2000='054302')or(TRT2000='054401')or(TRT2000='054402') D3/3: Mapset = PERMANENT D1/3: Vect_open_old(): name = census_wake2000 mapset= PERMANENT update = 0 D1/3: Vect_set_thresh(): thresh = 0.00 [...] D3/3: db_select_int() D3/3: SQL: SELECT cat FROM census_wake2000 WHERE (TRT2000= '050100')or(TRT2000='050300')or(TRT2000='050400')or(TRT2000= '050500')or(TRT2000='050600')or(TRT2000='050700')or(TRT2000= '050800')or(TRT2000='050900')or(TRT2000='051000')or(TRT2000= '051100')or(TRT2000='051200')or(TRT2000='051400')or(TRT2000= '051501')or(TRT2000='051502')or(TRT2000='051600')or(TRT2000= '051700')or(TRT2000='051800')or(TRT2000='051900')or(TRT2000= '052001')or(TRT2000='052002')or(TRT2000='052101')or(TRT2000= '052102')or(TRT2000='052201')or(TRT2000='052202')or(TRT2000= '052301')or(TRT2000='052302')or(TRT2000='052401')or(TRT2000= '052402')or(TRT2000='052404')or(TRT2000='052405')or(TRT2000= '052501')or(TRT2000='052503')or(TRT2000='052504')or(TRT2000= '052601')or(TRT2000='052602')or(TRT2000='052603')or(TRT2000= '052701')or(TRT2000='052703')or(TRT2000='052704')or(TRT2000= '052705')or(TRT2000='052801')or(TRT2000='052802')or(TRT2000= '052803')or(TRT2000='052804')or(TRT2000='052805')or(TRT2000= '052900')or(TRT2000='053001')or(TRT2000='053002')or(TRT2000= '053101')or(TRT DBMI-DBF driver error: SQL parser error: syntax error, unexpected $end processing
Re: [GRASS-user] Max length of sql_query in v.extract
On Thu, Jan 28, 2010 at 1:34 PM, Luigi Ponti lpo...@infinito.it wrote:
On 28/01/2010 00:45, Markus Neteler wrote:
On Wed, Jan 27, 2010 at 8:35 PM, Luigi Ponti lpo...@infinito.it wrote:
Hello,
Is there a length limit to a where sql statement fed to v.extract?
I am getting an
...
Error in db_open_select_cursor()
...
in the middle of a very long query.
Could you please try to analyse with
g.gisenv set=DEBUG=3
to identify when it gets lost? And perhaps provide an example
based on the Spearfish or North Carolina data sets?
Version: WinGRASS-6.4.SVN-r40650-1 (installed in C:\GRASS-64-SVN i.e. no
spaces in path; OS Windows 7)
Dataset: North Caroline dataset
Mapset: user1
vector: census_wake2000 (this actually resides in PERMANENT)
Working on the Cmd input box of the wxGUI. I have chosen a case
analogous to that where I encountered the error, i.e. a DBF Character field
containing 105 different numeric codes (e.g. 050100) one per record:
(note that you can find full debug output in a text file available at the
following link:
https://docs.google.com/leaf?id=0B0EpsfMZ-IZYOTJiYWVmNjUtNGYzMC00NDM5LWI1NDYtZTMyNzg3M2NjOThksort=namelayout=listnum=50
I thought 20,000+ lines was too much for the mailing list but please advice
if had better taken a different course of action.)
### START GRASS CMD HISTORY ###
g.gisenv set=DEBUG=3
(Thu Jan 28 12:12:34 2010) Command finished (0 sec)
(Thu Jan 28 12:12:43 2010)
v.extract input=census_wake2000 output=census_extract_all_trt2000
where=
[... very long WHERE suppressed...]
(TRT2000='050100')or(TRT2000='050300')or(TRT2000='050400')or(TRT2000='050500')or(TRT2000='050600')or(TRT2000='050700')or(TRT2000='050800')or(TRT2000='050900')or(TRT2000='051000')or(TRT2000='051100')or(TRT2000='051200')or(TRT2000='051400')or(TRT2000='051501')or(TRT2000='051502')or(TRT2000='051600')or(TRT2000='051700')or(TRT2000='051800')or(TRT2000='051900')or(TRT2000='052001')or(TRT2000='052002')or(TRT2000='052101')or(TRT2000='052102')or(TRT2000='052201')or(TRT2000='052202')or(TRT2000='052301')or(TRT2000='052302')or(TRT2000='052401')or(TRT2000='052402')or(TRT2000='052404')or(TRT2000='052405')or(TRT2000='052501')or(TRT2000='052503')or(TRT2000='052504')or(TRT2000='052601')or(TRT2000='052602')or(TRT2000='052603')or(TRT2000='052701')or(TRT2000='052703')or(TRT2000='052704')or(TRT2000='052705')or(TRT2000='052801')or(TRT2000='052802')or(TRT2000='052803')or(TRT2000='052804')or(TRT2000='052805')or(TRT2000='052900')or(TRT2000='053001')or(TRT2000='053002')or(TRT2000='053101')or(TRT2000='053103')or(TRT2000='053104')or(TRT2000='053200')or(TRT2000='053402')or(TRT2000='053403')or(TRT2000='053404')or(TRT2000='053405')or(TRT2000='053406')or(TRT2000='053407')or(TRT2000='053501')or(TRT2000='053505')or(TRT2000='053506')or(TRT2000='053507')or(TRT2000='053508')or(TRT2000='053509')or(TRT2000='053510')or(TRT2000='053512')or(TRT2000='053513')or(TRT2000='053514')or(TRT2000='053515')or(TRT2000='053600')or(TRT2000='053703')or(TRT2000='053706')or(TRT2000='053707')or(TRT2000='053709')or(TRT2000='053710')or(TRT2000='053711')or(TRT2000='053712')or(TRT2000='053713')or(TRT2000='053714')or(TRT2000='053715')or(TRT2000='053716')or(TRT2000='053801')or(TRT2000='053802')or(TRT2000='053900')or(TRT2000='054001')or(TRT2000='054003')or(TRT2000='054004')or(TRT2000='054006')or(TRT2000='054007')or(TRT2000='054008')or(TRT2000='054009')or(TRT2000='054010')or(TRT2000='054102')or(TRT2000='054104')or(TRT2000='054105')or(TRT2000='054106')or(TRT2000='054107')or(TRT2000='054108')or(TRT2000='054109')or(TRT2000='054201')or(TRT2000='054202')or(TRT2000='054301')or(TRT2000='054302')or(TRT2000='054401')or(TRT2000='054402')
D3/3: Mapset = PERMANENT
D1/3: Vect_open_old(): name = census_wake2000 mapset=
PERMANENT update = 0
D1/3: Vect_set_thresh(): thresh = 0.00
[...]
D3/3: db_select_int()
D3/3: SQL: SELECT cat FROM census_wake2000 WHERE (TRT2000=
... first part of very long statement...
'052601')or(TRT2000='052602')or(TRT2000='052603')or(TRT2000=
'052701')or(TRT2000='052703')or(TRT2000='052704')or(TRT2000=
'052705')or(TRT2000='052801')or(TRT2000='052802')or(TRT2000=
'052803')or(TRT2000='052804')or(TRT2000='052805')or(TRT2000=
'052900')or(TRT2000='053001')or(TRT2000='053002')or(TRT2000=
'053101')or(TRT
DBMI-DBF driver error:
SQL parser error: syntax error, unexpected $end processing
This looks suspicious:
lib/dbmi/dbmi_client/select.c
int db_select_int(dbDriver * driver, const char *tab, const char *col,
const char *where, int **pval)
{
int type, more, alloc, count;
int *val;
char buf[1024];
...
/* allocate */
alloc = 1000;
val = (int *)G_malloc(alloc * sizeof(int));
if (where == NULL || strlen(where) == 0)
G_snprintf(buf, 1023, SELECT %s FROM %s, col, tab);
else
G_snprintf(buf, 1023, SELECT %s FROM %s WHERE %s, col, tab, where);
G_debug(3, SQL: %s, buf);
db_init_string(stmt);
db_append_string(stmt, buf);
Re: [GRASS-user] Max length of sql_query in v.extract
Thanks for your answer, Markus. On 28/01/2010 14:57, Markus Neteler wrote: On Thu, Jan 28, 2010 at 1:34 PM, Luigi Ponti lpo...@infinito.it wrote: On 28/01/2010 00:45, Markus Neteler wrote: On Wed, Jan 27, 2010 at 8:35 PM, Luigi Ponti lpo...@infinito.it wrote: Hello, Is there a length limit to a where sql statement fed to v.extract? I am getting an ... Error in db_open_select_cursor() ... in the middle of a very long query. [...] v.extract input=census_wake2000 output=census_extract_all_trt2000 where= [... very long WHERE suppressed...] (TRT2000='050100')or(TRT2000='050300')or(TRT2000='050400')or(TRT2000='050500')or(TRT2000='050600')or(TRT2000='050700')or(TRT2000='050800')or(TRT2000='050900')or(TRT2000='051000')or(TRT2000='051100')or(TRT2000='051200')or(TRT2000='051400')or(TRT2000='051501')or(TRT2000='051502')or(TRT2000='051600')or(TRT2000='051700')or(TRT2000='051800')or(TRT2000='051900')or(TRT2000='052001')or(TRT2000='052002')or(TRT2000='052101')or(TRT2000='052102')or(TRT2000='052201')or(TRT2000='052202')or(TRT2000='052301')or(TRT2000='052302')or(TRT2000='052401')or(TRT2000='052402')or(TRT2000='052404')or(TRT2000='052405')or(TRT2000='052501')or(TRT2000='052503')or(TRT2000='052504')or(TRT2000='052601')or(TRT2000='052602')or(TRT2000='052603')or(TRT2000='052701')or(TRT2000='052703')or(TRT2000='052704')or(TRT2000='052705')or(TRT2000='052801')or(TRT2000='052802')or(TRT2000='052803')or(TRT2000='052804')or(TRT2000='052805')or(TRT2000='052900')or(TRT2000='053001')or(TRT2000='053002')or(TRT2000='053101')or(TRT2000='053103')or(TRT2000='053104')or(TRT2000='053200')or(TRT2000='053402')or(TRT2000='053403')or(TRT2000='053404')or(TRT2000='053405')or(TRT2000='053406')or(TRT2000='053407')or(TRT2000='053501')or(TRT2000='053505')or(TRT2000='053506')or(TRT2000='053507')or(TRT2000='053508')or(TRT2000='053509')or(TRT2000='053510')or(TRT2000='053512')or(TRT2000='053513')or(TRT2000='053514')or(TRT2000='053515')or(TRT2000='053600')or(TRT2000='053703')or(TRT2000='053706')or(TRT2000='053707')or(TRT2000='053709')or(TRT2000='053710')or(TRT2000='053711')or(TRT2000='053712')or(TRT2000='053713')or(TRT2000='053714')or(TRT2000='053715')or(TRT2000='053716')or(TRT2000='053801')or(TRT2000='053802')or(TRT2000='053900')or(TRT2000='054001')or(TRT2000='054003')or(TRT2000='054004')or(TRT2000='054006')or(TRT2000='054007')or(TRT2000='054008')or(TRT2000='054009')or(TRT2000='054010')or(TRT2000='054102')or(TRT2000='054104')or(TRT2000='054105')or(TRT2000='054106')or(TRT2000='054107')or(TRT2000='054108')or(TRT2000='054109')or(TRT2000='054201')or(TRT2000='054202')or(TRT2000='054301')or(TRT2000='054302')or(TRT2000='054401')or(TRT2000='054402') Yes, that's very long. Would there be a better way of achieving this in GRASS e.g. via *.db.* commands or is v.extract a wrapper for them, and hence they would fail anyway? Kind regards, Luigi [...] I guess that we need to change to dynamic string length allocation here? The cut string is pretty close to the 1023 chars length. Markus ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
[GRASS-user] Max length of sql_query in v.extract
Hello, Is there a length limit to a where sql statement fed to v.extract? I am getting an ... Error in db_open_select_cursor() ... in the middle of a very long query. Thanks for any hints, Luigi ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Max length of sql_query in v.extract
On Wed, Jan 27, 2010 at 8:35 PM, Luigi Ponti lpo...@infinito.it wrote: Hello, Is there a length limit to a where sql statement fed to v.extract? I am getting an ... Error in db_open_select_cursor() ... in the middle of a very long query. Could you please try to analyse with g.gisenv set=DEBUG=3 to identify when it gets lost? And perhaps provide an example based on the Spearfish or North Carolina data sets? best Markus ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
