Обсуждение: ECPG: non-integer constant in group by
I actually have two questions. 1) It seems like the fastest way to find the # of distinct elements in a column is using GROUP BY. With ECPG, if I try EXEC SQL SELECT filenm FROM beamdata GROUP BY filenm; I will get "sql error Too few arguments". Why? Can I correct the query to avoid the error message? (sqlca.sqlerrd[2] does contain the # of elements in spite of error) 2) The code below was meant to find the # of distinct elements for many columns, but fails with the message sql error 'non-integer constant in GROUP BY' thrown at the EXEC SQL EXECUTE statement. What is the problem? I suspect it is the definition char *vars[NVARS], but couldn't find any working alternatives. :-| -------------------------- #define NVARS 24 int main(int argc, char *argv[]) { int i, n_occ[NVARS]; EXEC SQL BEGIN DECLARE SECTION; char *vars[NVARS] = { "filenm", "yr", "mo", "dy", "hr", "mt", "sc", "us", "stat_id", "bmnum", "channel", "scnflag", "cp_id", "intt", "intt_us", "frang", "rsep", "tfreq", "noise", "natten", "nave", "nrang", "gsct", "isct" }; char dbnm[50], *stmt = "SELECT ? FROM beamdata GROUP BY ?;"; EXEC SQL END DECLARE SECTION; EXEC SQL WHENEVER SQLWARNING SQLPRINT; EXEC SQL WHENEVER SQLERROR SQLPRINT; sprintf(dbnm,"%s",argv[1]); EXEC SQL CONNECT TO :dbnm; EXEC SQL PREPARE query FROM :stmt; for (i=0; i<NVARS; i++) { EXEC SQL EXECUTE query USING $vars[i], $vars[i]; n_occ[i] = sqlca.sqlerrd[2]; fprintf(stderr,"# %s: %d\n", vars[i], n_occ[i]); } EXEC SQL DEALLOCATE PREPARE query; EXEC SQL COMMIT; EXEC SQL DISCONNECT; return 0; }
On 2006-09-15, Poul Jensen <flyvholm@gfy.ku.dk> wrote: > 2) The code below was meant to find the # of distinct elements for many > columns, but fails with the message > sql error 'non-integer constant in GROUP BY' > thrown at the EXEC SQL EXECUTE statement. What is the problem? > char dbnm[50], *stmt = "SELECT ? FROM beamdata GROUP BY ?;"; That statement is not legal - parameters can only be used to substitute _data_, not identifiers or syntax. You would have to interpolate the column name directly into the query (remembering to quote it with "" if needed) rather than use a parameter for it. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
On Fri, Sep 15, 2006 at 02:40:49AM -0800, Poul Jensen wrote: > 1) It seems like the fastest way to find the # of distinct elements in a > column is using GROUP BY. With ECPG, if I try > EXEC SQL SELECT filenm FROM beamdata GROUP BY filenm; > I will get "sql error Too few arguments". Why? Can I correct the > query to avoid the error message? (sqlca.sqlerrd[2] does contain the # > of elements in spite of error) The problem is that you are selecting a value but you don't say where ecpg should store the result. You're missing the "INTO :var" part. Joachim -- Joachim Wieland joe@mcknight.de GPG key available
Joachim Wieland wrote: > On Fri, Sep 15, 2006 at 02:40:49AM -0800, Poul Jensen wrote: > >> 1) It seems like the fastest way to find the # of distinct elements in a >> column is using GROUP BY. With ECPG, if I try >> EXEC SQL SELECT filenm FROM beamdata GROUP BY filenm; >> I will get "sql error Too few arguments". Why? Can I correct the >> query to avoid the error message? (sqlca.sqlerrd[2] does contain the # >> of elements in spite of error) >> > The problem is that you are selecting a value but you don't say where ecpg > should store the result. You're missing the "INTO :var" part. > Thank you; that makes sense. I'm not sure why it's considered an error though. Documentation says "Any SQL command can be run...", and why not. The other problem I posted is worse and is what's really keeping me stuck. Why are my text strings considered unacceptable "non-integer constants"? Here are the relevant lines of code: #define NVARS 24 EXEC SQL BEGIN DECLARE SECTION; char *vars[NVARS] = { "filenm", "yr", "mo", "dy", "hr", "mt", "sc", "us", "stat_id", "bmnum", "channel", "scnflag", "cp_id", "intt", "intt_us", "frang", "rsep", "tfreq", "noise", "natten", "nave", "nrang", "gsct", "isct" }; char *stmt = "SELECT ? FROM beamdata GROUP BY ?;"; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE query FROM :stmt; for (i=0; i<NVARS; i++) { EXEC SQL EXECUTE query USING :vars[i], :vars[i]; } Fails at EXECUTE with error message: sql error 'non-integer constant in GROUP BY'
On Fri, Sep 15, 2006 at 02:44:33PM -0800, Poul Jensen wrote: > The other problem I posted is worse and is what's really keeping me > stuck. Why are my text strings considered unacceptable "non-integer > constants"? Here are the relevant lines of code: <snip> That's because your query is expanding to: SELECT 'filenm' FROM beamdata GROUP BY 'filenm'; Which is probably not what you're looking for. The reason postgres is complaining is because grouping by a constant doesn't make any sense. Integer constants are special-cased to mean column numbers, hence the reference to non-integer constants. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Вложения
Thank you for your replies. My next attempt was: for (i=0; i<NVARS; i++) { sprintf(stmt,"SELECT %s FROM beamdata GROUP BY %s;",vars[i],vars[i]); EXEC SQL EXECUTE IMMEDIATE :stmt; n_occ[i] = sqlca.sqlerrd[2]; } It works, but throws error message "too few parameters" every time. I need to add INTO <host variable> (I do need to retrieve the data for later use anyway), but it appears I can't use EXECUTE IMMEDIATE with a host variable in the statement. So now I can only think of one way: EXEC SQL SELECT filenm INTO :filenms FROM beamdata GROUP BY filenm; n_occ[0] = sqlca.sqlerrd[2]; EXEC SQL SELECT yr INTO :yrs FROM beamdata GROUP BY yr; n_occ[1] = sqlca.sqlerrd[2]; <Repeat for all variables!!> Works and will get the job done, but so repetitive that it's hard to accept. Is there no way to get away with a loop? Poul Jensen
On Sat, Sep 16, 2006 at 03:47:28AM -0800, Poul Jensen wrote: > Thank you for your replies. My next attempt was: > > for (i=0; i<NVARS; i++) > { > sprintf(stmt,"SELECT %s FROM beamdata GROUP BY %s;",vars[i],vars[i]); > EXEC SQL EXECUTE IMMEDIATE :stmt; > n_occ[i] = sqlca.sqlerrd[2]; > } I've never used ECPG, but from the manual it seems the following should work: for (i=0; i<NVARS; i++) { sprintf(stmt,"SELECT %s FROM beamdata GROUP BY %s;",vars[i],vars[i]); EXEC SQL PREPARE mystmt FROM :stmt; EXEC SQL EXECUTE mystmt INTO wherever; n_occ[i] = sqlca.sqlerrd[2]; } I don't know what the n_occ line does, so I left it alone but maybe that needs changing too. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Вложения
Martijn van Oosterhout wrote: > for (i=0; i<NVARS; i++) > { > sprintf(stmt,"SELECT %s FROM beamdata GROUP BY %s;",vars[i],vars[i]); > EXEC SQL PREPARE mystmt FROM :stmt; > EXEC SQL EXECUTE mystmt INTO wherever; > n_occ[i] = sqlca.sqlerrd[2]; > } > Apologies. I already read this in the docs, but also forgot it again. :-| There is a little more to the solution since I need another array to save the retrieved data after each query. So in the hope to help others, here's how I did it: int *all_vars[NVARS]; int *tmp=NULL; for (i=0; i<NVARS; i++) { sprintf(stmt,"SELECT %s FROM beamdata GROUP BY %s;",vars[i],vars[i]); EXEC SQL PREPARE query FROM :stmt; EXEC SQL EXECUTE query INTO :tmp; n_occ[i] = sqlca.sqlerrd[2]; /* Number of rows processed in query */ if ((all_vars[i]=(int *)malloc(n_occ[i]*sizeof(int)))==NULL) { fprintf(stderr,"Memory allocation failure\n"); exit(-1); } memcpy(all_vars[i], tmp, n_occ[i]*sizeof(int)); EXEC SQL DEALLOCATE PREPARE query; tmp=NULL; } (Remember to free allocated memory when done) Thanks so much for the help!