Thread: 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.
Attachment
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.
Attachment
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!