cancel
Showing results for 
Search instead for 
Did you mean: 

ecpg bug report (returning clause)

SOLVED
Adventurer

ecpg bug report (returning clause)

os: redhat 6

edb version: 9.6.6.11

ecpg bug report

 

with x as (update ...returning)

inserint ..

returing clause parsing error!!

 

example)

 

 

[scbatch@TEST:/cssc/EDU/03.ecpg]$ cat makefile
INCLUDES = -I$(shell pg_config --includedir)
LIBPATH = -L $(shell pg_config --libdir)
CFLAGS += $(INCLUDES) -g
LDFLAGS += -g
LDLIBS += $(LIBPATH) -lecpg -lpq

.SUFFIXES: .pgc
.pgc.c:
ecpg  -c $(INCLUDES) $?
[scbatch@TEST:/cssc/EDU/03.ecpg]$ cat bug.pgc
#include <stdio.h>
#include <stdlib.h>


int main(void)
{
EXEC SQL WHENEVER SQLERROR sqlprint;

EXEC SQL BEGIN DECLARE SECTION;
const char *target = "perf@localhost:5444";
const char *user = "mig";
const char *passwd = "mig";
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT TO :target USER :user USING :passwd;


/********************************************
0) prepare
********************************************/
EXEC SQL drop table if exists test1;
EXEC SQL create table test1(a int primary key, b text, c timestamp);
EXEC SQL truncate table test1;
EXEC SQL insert into test1 values(1,'superman',current_timestamp);
EXEC SQL insert into test1 values(2,'spiderman',current_timestamp);
EXEC SQL insert into test1 values(3,'ultraman',current_timestamp);
EXEC SQL WITH UPD AS (UPDATE mig.test1 SET b= 'Joe' WHERE a = 2 returning a), INS AS (SELECT 2, 'Joe', current_timestamp WHERE NOT EXISTS (SELECT * FROM UPD)) INSERT INTO mig.test1(a,b,c) SELECT * FROM INS;
EXEC SQL COMMIT;

}
[scbatch@TEST:/cssc/EDU/03.ecpg]$ make bug
ecpg -c -I/ppas_client/ENGINE/as9.6/include bug.pgc
bug.pgc:26: ERROR: syntax error at or near ")"
make: *** [bug.c] Error 3
[scbatch@TEST:/cssc/EDU/03.ecpg]$

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Moderator

Re: ecpg bug report (returning clause)

Hi,

Hope you are doing good !
It seems some issue with your script.

 

We tried executing the makefile and programme provided by you, which is working fine in our case.

Please check below test case.

[root@localhost bin]# ./ecpg --version
ecpg (EnterpriseDB 9.6.7.13) 4.12.0

 

[root@localhost bin]# make -B ecpg_bug
ecpg  -c -I/opt/edb/as9.6/include ecpg_bug.pgc
cc -I/opt/edb/as9.6/include -g   -c -o ecpg_bug.o ecpg_bug.c
cc -g  ecpg_bug.o  -L /opt/edb/as9.6/lib -lecpg -lpq -o ecpg_bug
rm ecpg_bug.o ecpg_bug.c

 

[root@localhost bin]# cat makefile
INCLUDES = -I$(shell pg_config --includedir)
LIBPATH = -L $(shell pg_config --libdir)
CFLAGS += $(INCLUDES) -g
LDFLAGS += -g
LDLIBS += $(LIBPATH) -lecpg -lpq

.SUFFIXES: .pgc
.pgc.c:
    ecpg  -c $(INCLUDES) $?

 

[root@localhost bin]# cat ecpg_bug.pgc
#include <stdio.h>
#include <stdlib.h>


int main(void)
{
EXEC SQL WHENEVER SQLERROR sqlprint;

EXEC SQL BEGIN DECLARE SECTION;
const char *target = "edb@localhost:5446";
const char *user = "enterprisedb";
const char *passwd = "adminedb";
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT TO :target USER :user USING :passwd;


/********************************************
0) prepare
********************************************/
EXEC SQL drop table if exists test1;
EXEC SQL create table test1(a int primary key, b text, c timestamp);
EXEC SQL truncate table test1;
EXEC SQL insert into test1 values(1,'superman',current_timestamp);
EXEC SQL insert into test1 values(2,'spiderman',current_timestamp);
EXEC SQL insert into test1 values(3,'ultraman',current_timestamp);
EXEC SQL WITH UPD AS (UPDATE test1 SET b= 'Joe' WHERE a = 2 returning a), INS AS (SELECT 2, 'Joe', current_timestamp WHERE NOT EXISTS (SELECT *  FROM UPD)) INSERT INTO test1(a,b,c) SELECT * FROM INS;
EXEC SQL COMMIT;

}
[root@localhost bin]# ./ecpg_bug
[root@localhost bin]# 


In addition to the above could you also try executing with the latest version of ecpg and let us know if you still face any issue.

2 REPLIES 2
Highlighted
Moderator

Re: ecpg bug report (returning clause)

Hi,

Hope you are doing good !
It seems some issue with your script.

 

We tried executing the makefile and programme provided by you, which is working fine in our case.

Please check below test case.

[root@localhost bin]# ./ecpg --version
ecpg (EnterpriseDB 9.6.7.13) 4.12.0

 

[root@localhost bin]# make -B ecpg_bug
ecpg  -c -I/opt/edb/as9.6/include ecpg_bug.pgc
cc -I/opt/edb/as9.6/include -g   -c -o ecpg_bug.o ecpg_bug.c
cc -g  ecpg_bug.o  -L /opt/edb/as9.6/lib -lecpg -lpq -o ecpg_bug
rm ecpg_bug.o ecpg_bug.c

 

[root@localhost bin]# cat makefile
INCLUDES = -I$(shell pg_config --includedir)
LIBPATH = -L $(shell pg_config --libdir)
CFLAGS += $(INCLUDES) -g
LDFLAGS += -g
LDLIBS += $(LIBPATH) -lecpg -lpq

.SUFFIXES: .pgc
.pgc.c:
    ecpg  -c $(INCLUDES) $?

 

[root@localhost bin]# cat ecpg_bug.pgc
#include <stdio.h>
#include <stdlib.h>


int main(void)
{
EXEC SQL WHENEVER SQLERROR sqlprint;

EXEC SQL BEGIN DECLARE SECTION;
const char *target = "edb@localhost:5446";
const char *user = "enterprisedb";
const char *passwd = "adminedb";
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT TO :target USER :user USING :passwd;


/********************************************
0) prepare
********************************************/
EXEC SQL drop table if exists test1;
EXEC SQL create table test1(a int primary key, b text, c timestamp);
EXEC SQL truncate table test1;
EXEC SQL insert into test1 values(1,'superman',current_timestamp);
EXEC SQL insert into test1 values(2,'spiderman',current_timestamp);
EXEC SQL insert into test1 values(3,'ultraman',current_timestamp);
EXEC SQL WITH UPD AS (UPDATE test1 SET b= 'Joe' WHERE a = 2 returning a), INS AS (SELECT 2, 'Joe', current_timestamp WHERE NOT EXISTS (SELECT *  FROM UPD)) INSERT INTO test1(a,b,c) SELECT * FROM INS;
EXEC SQL COMMIT;

}
[root@localhost bin]# ./ecpg_bug
[root@localhost bin]# 


In addition to the above could you also try executing with the latest version of ecpg and let us know if you still face any issue.

Adventurer

Re: ecpg bug report (returning clause)

After upgrade to 9.6.7 it works good.

Thank you!