BUG #4550: ecpg problem with copy command and hostvar - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      4550
Logged by:          Clemens Fischer
Email address:      cfi@mbs-software.de
PostgreSQL version: 8.3.5
Operating system:   QNX 6.3
Description:        ecpg problem with copy command and hostvar
Details:

Hello List,

Don't know whether anyone here can help but...
We have some code that has compiled and ran just fine from postgresql 7.4.x
thru 8.2.9.
It uses embedded sql.
With the new port - 8.3.5 - this code will compile but the program generates
an error at runtime.
The copy command can't be used with a hostvar.
Below is a standalone code, which shows you the details. Any help or ideas
would be appreciated.

-- snip ecpgtest-copy.pgc begin --
#include <stdio.h>
#include <stdlib.h>

EXEC SQL include sqlca;
EXEC SQL WHENEVER SQLERROR   sqlprint;
EXEC SQL WHENEVER SQLWARNING sqlprint;
EXEC SQL WHENEVER NOT FOUND  continue;

int main( void )
{
    EXEC SQL begin declare section;
    int     no;
    VARCHAR name[30];
    char    export_file[50];
    EXEC SQL end declare section;

    ECPGdebug(1, stderr);
//  EXEC SQL SET autocommit TO ON; // OFF
    EXEC SQL connect to postgres user postgres using postgres;

    EXEC SQL DROP TABLE IF EXISTS ecpgtest;
    EXEC SQL CREATE TABLE ecpgtest (no   INTEGER     PRIMARY KEY,
                                    name VARCHAR(30) NOT NULL);
    EXEC SQL INSERT INTO ecpgtest VALUES (1, 'first');
    EXEC SQL INSERT INTO ecpgtest VALUES (2, 'second');
    EXEC SQL INSERT INTO ecpgtest VALUES (3, 'third');

    // test 1
    EXEC SQL COPY ecpgtest TO '/tmp/ecpgtest_export1';

    // test 2
    strcpy(export_file, "/tmp/ecpgtest_export2");
    EXEC SQL COPY ecpgtest TO :export_file;

    EXEC SQL DISCONNECT ALL;
    ECPGdebug(0, stderr);

    return( EXIT_SUCCESS );
}
-- snip ecpgtest-copy.pgc end --

Running the compiled program generates the following output:

-- snip ecpgtest-copy output begin --
[11395125]: ECPGdebug: set to 1
[11395125]: ECPGconnect: opening database postgres on <DEFAULT> port
<DEFAULT> for user postgres
[11395125]: ecpg_execute line 21: QUERY: drop table if exists ecpgtest  with
0 parameter on connection postgres
[11395125]: ecpg_execute line 21: using PQexec
[11395125]: ecpg_execute line 21 Ok: DROP TABLE
[11395125]: ecpg_execute line 22: QUERY: create  table ecpgtest ( no integer
  primary key   , name varchar ( 30 )    not null )     with 0 parameter on
connection postgres
[11395125]: ecpg_execute line 22: using PQexec
[11395125]: ecpg_execute line 22 Ok: CREATE TABLE
[11395125]: ecpg_execute line 24: QUERY: insert into ecpgtest values ( 1 ,
'first' )  with 0 parameter on connection postgres
[11395125]: ecpg_execute line 24: using PQexec
[11395125]: ecpg_execute line 24 Ok: INSERT 0 1
[11395125]: ecpg_execute line 25: QUERY: insert into ecpgtest values ( 2 ,
'second' )  with 0 parameter on connection postgres
[11395125]: ecpg_execute line 25: using PQexec
[11395125]: ecpg_execute line 25 Ok: INSERT 0 1
[11395125]: ecpg_execute line 26: QUERY: insert into ecpgtest values ( 3 ,
'third' )  with 0 parameter on connection postgres
[11395125]: ecpg_execute line 26: using PQexec
[11395125]: ecpg_execute line 26 Ok: INSERT 0 1
[11395125]: ecpg_execute line 29: QUERY: copy  ecpgtest  to
'/tmp/ecpgtest_export1'    with 0 parameter on connection postgres
[11395125]: ecpg_execute line 29: using PQexec
[11395125]: ecpg_execute line 29 Ok: COPY 3
[11395125]: ecpg_execute line 33: QUERY: copy  ecpgtest  to  $1     with 1
parameter on connection postgres
[11395125]: ecpg_execute line 33: using PQexecParams
[11395125]: free_params line 33: parameter 1 = /tmp/ecpgtest_export2
[11395125]: ecpg_check_PQresult line 33: Error: ERROR:  syntax error at or
near "$1"
LINE 1: copy  ecpgtest  to  $1
                            ^
[11395125]: raising sqlstate 42601 (sqlcode: -400) in line 33, ''syntax
error at or near "$1"' in line 33.'.
sql error 'syntax error at or near "$1"' in line 33.
[11395125]: ecpg_finish: Connection postgres closed.
-- snip ecpgtest-copy output end --

-- snip ecpgtest-copy.c code fragment begin, processed by ecpg 4.4.1 --
    // test 1
    { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "copy  ecpgtest  to
'/tmp/ecpgtest_export1'   ", ECPGt_EOIT, ECPGt_EORT);
#line 29 "ecpgtest-copy.pgc"

if (sqlca.sqlwarn[0] == 'W') sqlprint();
#line 29 "ecpgtest-copy.pgc"

if (sqlca.sqlcode < 0) sqlprint();}
#line 29 "ecpgtest-copy.pgc"


    // test 2
    strcpy(export_file, "/tmp/ecpgtest_export2");
    { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "copy  ecpgtest  to  $1
   ",
        ECPGt_char,(export_file),(long)50,(long)1,(50)*sizeof(char),
        ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);
#line 33 "ecpgtest-copy.pgc"

if (sqlca.sqlwarn[0] == 'W') sqlprint();
#line 33 "ecpgtest-copy.pgc"
-- snip ecpgtest-copy.c code fragment end --

I think the hostvar is correctly detected (parameter 1 =
/tmp/ecpgtest_export2).

Hope that helps.
Best regards
Clemens Fischer