ECPG: non-integer constant in group by

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/

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/

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!