Thread: ECPG bug: "unterminated quoted identifier"
Hello
I have code:
void main()
{
char *foo = "aaa\"bbb";
EXEC SQL char *bar = "aaa\"bbb";
}
{
char *foo = "aaa\"bbb";
EXEC SQL char *bar = "aaa\"bbb";
}
ecpg --version
ecpg (PostgreSQL) 13.0 (Ubuntu 13.0-1.pgdg20.04+1)
ecpg (PostgreSQL) 13.0 (Ubuntu 13.0-1.pgdg20.04+1)
ecpg sample.pgc -o 1.c
sample .pgc:10: ERROR: unterminated quoted identifier
On Tue, Oct 20, 2020 at 9:47 AM 1250kv <1250kv@gmail.com> wrote:
HelloI have code:void main()
{
char *foo = "aaa\"bbb";
EXEC SQL char *bar = "aaa\"bbb";
}ecpg --version
ecpg (PostgreSQL) 13.0 (Ubuntu 13.0-1.pgdg20.04+1)ecpg sample.pgc -o 1.csample .pgc:10: ERROR: unterminated quoted identifier
General usage questions regarding PostgreSQL and ECPG should be sent to the -general list.
What are you expecting to be the result of that?
David J.
1250kv <1250kv@gmail.com> writes:
> void main()
> {
> char *foo = "aaa\"bbb";
> EXEC SQL char *bar = "aaa\"bbb";
> }
> ecpg sample.pgc -o 1.c
> sample .pgc:10: ERROR: unterminated quoted identifier
I don't really see a bug there. While I'm not an ecpg expert by
any means, I'd expect the EXEC SQL section to parse quoted strings
according to SQL rules not C rules. And under SQL, that's not
what you do to write a valid quoted identifier.
regards, tom lane
I have expected that host-variable bar the variable will be assigned the value 'aaa"bbb'
similarly if this value were obtained from a query:
#include <stdio.h>
int main()
{
EXEC SQL char *foo;
foo = (char *) malloc(5);
EXEC SQL SELECT 'aaa"bbb' INTO :foo;
printf("%s\n", foo);
return 0;
}
Result:
aaa"bbb
similarly if this value were obtained from a query:
#include <stdio.h>
int main()
{
EXEC SQL char *foo;
foo = (char *) malloc(5);
EXEC SQL SELECT 'aaa"bbb' INTO :foo;
printf("%s\n", foo);
return 0;
}
Result:
aaa"bbb
On Tue, Oct 20, 2020 at 7:57 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Oct 20, 2020 at 9:47 AM 1250kv <1250kv@gmail.com> wrote:HelloI have code:void main()
{
char *foo = "aaa\"bbb";
EXEC SQL char *bar = "aaa\"bbb";
}ecpg --version
ecpg (PostgreSQL) 13.0 (Ubuntu 13.0-1.pgdg20.04+1)ecpg sample.pgc -o 1.csample .pgc:10: ERROR: unterminated quoted identifierGeneral usage questions regarding PostgreSQL and ECPG should be sent to the -general list.What are you expecting to be the result of that?David J.
When I assign the value "aaa\"bbb" to the non-host variable foo and then assign the value of foo to the host-variable bar there no error:
int main()
{
char *foo = "aaa\"bbb";
printf("%s\n", foo);
EXEC SQL char *bar = foo;
printf("%s\n", bar);
return 0;
}
Result:
aaa"bbb
aaa"bbb
int main()
{
char *foo = "aaa\"bbb";
printf("%s\n", foo);
EXEC SQL char *bar = foo;
printf("%s\n", bar);
return 0;
}
Result:
aaa"bbb
aaa"bbb
On Tue, Oct 20, 2020 at 8:23 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
1250kv <1250kv@gmail.com> writes:
> void main()
> {
> char *foo = "aaa\"bbb";
> EXEC SQL char *bar = "aaa\"bbb";
> }
> ecpg sample.pgc -o 1.c
> sample .pgc:10: ERROR: unterminated quoted identifier
I don't really see a bug there. While I'm not an ecpg expert by
any means, I'd expect the EXEC SQL section to parse quoted strings
according to SQL rules not C rules. And under SQL, that's not
what you do to write a valid quoted identifier.
regards, tom lane
1250kv <1250kv@gmail.com> writes:
> EXEC SQL char *bar = foo;
I think it's pure luck that that doesn't throw an error.
You should not be using the EXEC SQL prefix for something
that isn't a SQL command.
Anyway, the problem you're hitting here is that as soon as you
say EXEC SQL, the syntax rules for quoted strings change.
This is not well documented, and I think it's got some bugs
in itself [1], but "EXEC SQL char *bar = "aaa\"bbb";" is just
wrong. You can't use C literal syntax inside a SQL code
segment, whether or not the command would work otherwise.
regards, tom lane
[1] https://www.postgresql.org/message-id/673825.1603223178%40sss.pgh.pa.us
>>You can't use C literal syntax inside a SQL code
>>segment, whether or not the command would work otherwise.
I have come across cases in which there is a need to use nested double quotes inside C string literal.
This is necessary for correct access to database objects whose names contain uppercase characters.
CREATE FUNCTION "My_Func"(IN p_i INTEGER)
RETURNS INTEGER
AS
$BODY$
BEGIN
RETURN p_i + 1;
END;
$BODY$
LANGUAGE plpgsql;
int main()
{
EXEC SQL int i;
EXEC SQL EXECUTE IMMEDIATE "DO $$\n\
BEGIN\n\
:i := embeddedc.\"My_Func\"(:i);\n\
END\n\
$$";
>>segment, whether or not the command would work otherwise.
I have come across cases in which there is a need to use nested double quotes inside C string literal.
This is necessary for correct access to database objects whose names contain uppercase characters.
CREATE FUNCTION "My_Func"(IN p_i INTEGER)
RETURNS INTEGER
AS
$BODY$
BEGIN
RETURN p_i + 1;
END;
$BODY$
LANGUAGE plpgsql;
int main()
{
EXEC SQL int i;
EXEC SQL EXECUTE IMMEDIATE "DO $$\n\
BEGIN\n\
:i := embeddedc.\"My_Func\"(:i);\n\
END\n\
$$";
return 0;
}
Result: ERROR: syntax error at or near "My_Func"
I have discovered another workaround (in addition to what I showed earlier): \" could be replaced with \x22.
However, I believe that this is a bug in the ECPG lexical analyzer.
}
Result: ERROR: syntax error at or near "My_Func"
I have discovered another workaround (in addition to what I showed earlier): \" could be replaced with \x22.
However, I believe that this is a bug in the ECPG lexical analyzer.
On Tue, Oct 20, 2020 at 11:10 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
1250kv <1250kv@gmail.com> writes:
> EXEC SQL char *bar = foo;
I think it's pure luck that that doesn't throw an error.
You should not be using the EXEC SQL prefix for something
that isn't a SQL command.
Anyway, the problem you're hitting here is that as soon as you
say EXEC SQL, the syntax rules for quoted strings change.
This is not well documented, and I think it's got some bugs
in itself [1], but "EXEC SQL char *bar = "aaa\"bbb";" is just
wrong. You can't use C literal syntax inside a SQL code
segment, whether or not the command would work otherwise.
regards, tom lane
[1] https://www.postgresql.org/message-id/673825.1603223178%40sss.pgh.pa.us
1250kv <1250kv@gmail.com> writes:
> I have come across cases in which there is a need to use nested double
> quotes inside C string literal.
> EXEC SQL EXECUTE IMMEDIATE "DO $$\n\
> BEGIN\n\
> :i := embeddedc.\"My_Func\"(:i);\n\
> END\n\
> $$";
I'd be interested to understand why you feel the need to write that,
and not just
EXEC SQL DO $$
BEGIN
:i := embeddedc."My_Func"(:i);
END
$$;
AFAICS, EXECUTE IMMEDIATE with a constant string isn't really useful
for anything. (Note that if you mean those :i's as references to
your ECPG variable, I don't think that works in either syntax, since
it's inside a literal.)
regards, tom lane
> EXEC SQL DO
Thanks, it works!
> Note that if you mean those :i's as references to
> your ECPG variable, I don't think that works in either syntax, since
> it's inside a literal.)
Yes, you are right! I had to use a temporary table as a buffer in order to refer to host variable values in an anonymous block.
Thank you for helping me!
However, there is one another case when I have to use a C string literal with nested double quotes inside Embedded SQL.
I have a stored procedure with case sensitive name and INOUT parameter:
CREATE OR REPLACE PROCEDURE embeddedc."My_Proc_outparam"(INOUT p TEXT)
AS
$BODY$
BEGIN
p := '222';
END;
$BODY$
LANGUAGE plpgsql;
When I call it from DB there is no issue:
DO
$BODY$
DECLARE
t TEXT := 'qqq';
BEGIN
CALL embeddedc."My_Proc_outparam"(t);
END;
$BODY$
But when I try to call this procedure from embedded SQL...
Thanks, it works!
> Note that if you mean those :i's as references to
> your ECPG variable, I don't think that works in either syntax, since
> it's inside a literal.)
Yes, you are right! I had to use a temporary table as a buffer in order to refer to host variable values in an anonymous block.
Thank you for helping me!
However, there is one another case when I have to use a C string literal with nested double quotes inside Embedded SQL.
I have a stored procedure with case sensitive name and INOUT parameter:
CREATE OR REPLACE PROCEDURE embeddedc."My_Proc_outparam"(INOUT p TEXT)
AS
$BODY$
BEGIN
p := '222';
END;
$BODY$
LANGUAGE plpgsql;
When I call it from DB there is no issue:
DO
$BODY$
DECLARE
t TEXT := 'qqq';
BEGIN
CALL embeddedc."My_Proc_outparam"(t);
END;
$BODY$
But when I try to call this procedure from embedded SQL...
int main()
{
EXEC SQL char foo[9];
EXEC SQL CALL embeddedc."My_Proc_outparam"(:foo);
return 0;
}
{
EXEC SQL char foo[9];
EXEC SQL CALL embeddedc."My_Proc_outparam"(:foo);
return 0;
}
...an error occurs: "SQL error: too few arguments on line ..."
As far as I can see the Embedded SQL CALL statement does not support procedures with INOUT parameters.
To get around this limitation, I decided to use PREPARE .. FROM + EXECUTE.
But I ran into the situation that I described in here: https://www.postgresql.org/message-id/flat/CA%2B4qtLett6CMxojrwn%2ByQq7qmN8SMOM3eZQ8yQG9D5tCuF-vKw%40mail.gmail.com
As far as I can see the Embedded SQL CALL statement does not support procedures with INOUT parameters.
To get around this limitation, I decided to use PREPARE .. FROM + EXECUTE.
But I ran into the situation that I described in here: https://www.postgresql.org/message-id/flat/CA%2B4qtLett6CMxojrwn%2ByQq7qmN8SMOM3eZQ8yQG9D5tCuF-vKw%40mail.gmail.com
On Wed, Oct 21, 2020 at 4:12 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
1250kv <1250kv@gmail.com> writes:
> I have come across cases in which there is a need to use nested double
> quotes inside C string literal.
> EXEC SQL EXECUTE IMMEDIATE "DO $$\n\
> BEGIN\n\
> :i := embeddedc.\"My_Func\"(:i);\n\
> END\n\
> $$";
I'd be interested to understand why you feel the need to write that,
and not just
EXEC SQL DO $$
BEGIN
:i := embeddedc."My_Func"(:i);
END
$$;
AFAICS, EXECUTE IMMEDIATE with a constant string isn't really useful
for anything. (Note that if you mean those :i's as references to
your ECPG variable, I don't think that works in either syntax, since
it's inside a literal.)
regards, tom lane