Bug #806: create function failing... ERROR: stat failed on file '

Lynn Tilby (unixprgrmr(at)aol(dot)com) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
create function failing... ERROR: stat failed on file '

Long Description
DEBUG: ProcessUtility: create function dys_btwn_dates ( date , date ) returns int as '
BEGIN
RETURN $1 - $2;
END;
' language 'c'
ERROR: stat failed on file '
BEGIN
RETURN $1 - $2;
END;
': No such file or directory

Sample Code
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! CPG SOURCE CODE !!!!!!!!!!!!!!!!!!!!!!

/* SET TABSTOP = 2 */

/* days between dates.... an attempt at using the 'date' data type in postgres
to try and determine the actual days between calendar dates.
*/

#include <stdio.h>

EXEC SQL INCLUDE sqlca; /* include the sql debugging stuff... */

/********* !!!!!!!!!! POSTGRES SQL DEBUGGING STRUCTURE FIELDS ************/
#define DEBUG_SQL printf("sqlcaid = %s\n", sqlca.sqlcaid); printf("sqlabc = %f\n", sqlca.sqlabc); printf("sqlcode = %f\n", sqlca.sqlcode); printf("sqlca.sqlerrm.sqlerrml = %d\n", sqlca.sqlerrm.sqlerrml); printf("sqlca.sqlerrm.sqlerrmc = %s\n", sqlca.sqlerrm.sqlerrmc); printf("sqlerrp = %s\n", sqlca.sqlerrp); printf("sqlerrd = %f\n", sqlca.sqlerrd); printf("sqlext = %s\n", sqlca.sqlext);

int main()
{

EXEC SQL BEGIN DECLARE SECTION; /* declare the variables used by sql */

char t_date1[] = {'1','9','9','9','-','0','3','-','0','1','\0'};
char t_date2[] = {'1','9','9','9','-','0','1','-','0','1','\0'};
int date_rtrn = 20;
char t_in_date[12];

EXEC SQL END DECLARE SECTION; /* declare the variables used by sql */

EXEC SQL CONNECT TO pma;

EXEC SQL WHENEVER sqlerror sqlprint; /* turn on verbose sql error ckg */

printf("after whenever\n");

EXEC SQL CREATE FUNCTION dys_btwn_dates(date, date) RETURNS int AS '
BEGIN
RETURN $1 - $2;
END;
' LANGUAGE 'c';
DEBUG_SQL

printf("after create\n");

EXEC SQL DECLARE date_crsr CURSOR
FOR SELECT dta_date
FROM stck_dta_tbl
WHERE dta_date < '1999-02-04'
AND :date_rtrn >= dys_btwn_dates(:t_date1, dta_date);

printf("after cusrsor\n");

EXEC SQL OPEN date_crsr;
printf("after open cusrsor\n");
EXEC SQL FETCH FORWARD NEXT IN date_crsr INTO :t_in_date;
printf("after fetch\n");

DEBUG_SQL

printf("days = %d\n",t_in_date);

}
/*
DECLARE
date1 ALIAS FOR $1;
date2 ALIAS FOR $2;
*/

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! CPG PRE PROCESSOR OUTPUT !!!!!!!!!!!!!!!!!!!!!!

/* Processed by ecpg (2.8.0) */
/* These three include files are added by the preprocessor */
#include <ecpgtype.h>
#include <ecpglib.h>
#include <ecpgerrno.h>
#line 1 "dys_btwn_dates.cpg"

/* SET TABSTOP = 2 */

/* days between dates.... an attempt at using the 'date' data type in postgres
to try and determine the actual days between calendar dates.
*/

#include <stdio.h>

#line 1 "/usr/local/pgsql/include/sqlca.h"
#ifndef POSTGRES_SQLCA_H
#define POSTGRES_SQLCA_H

#ifndef DLLIMPORT
#ifdef __CYGWIN__
#define DLLIMPORT __declspec (dllimport)
#else
#define DLLIMPORT
#endif /* __CYGWIN__ */
#endif /* DLLIMPORT */

#define SQLERRMC_LEN 70

#ifdef __cplusplus
extern "C"
{
#endif

struct sqlca
{
char sqlcaid[8];
long sqlabc;
long sqlcode;
struct
{
int sqlerrml;
char sqlerrmc[SQLERRMC_LEN];
} sqlerrm;
char sqlerrp[8];
long sqlerrd[6];
/* Element 0: empty */
/* 1: OID of processed tuple if applicable */
/* 2: number of rows processed */
/* after an INSERT, UPDATE or */
/* DELETE statement */
/* 3: empty */
/* 4: empty */
/* 5: empty */
char sqlwarn[8];
/* Element 0: set to 'W' if at least one other is 'W' */
/* 1: if 'W' at least one character string */
/* value was truncated when it was */
/* stored into a host variable. */

/*
* 2: if 'W' a (hopefully) non-fatal notice occured
*//* 3: empty */
/* 4: empty */
/* 5: empty */
/* 6: empty */
/* 7: empty */

char sqlext[8];
};

extern DLLIMPORT struct sqlca sqlca;

#ifdef __cplusplus
}

#endif

#endif

#line 9 "dys_btwn_dates.cpg"
/* include the sql debugging stuff... */

/********* !!!!!!!!!! POSTGRES SQL DEBUGGING STRUCTURE FIELDS ************/
#define DEBUG_SQL printf("sqlcaid = %s\n", sqlca.sqlcaid); printf("sqlabc = %f\n", sqlca.sqlabc); printf("sqlcode = %f\n", sqlca.sqlcode); printf("sqlca.sqlerrm.sqlerrml = %d\n", sqlca.sqlerrm.sqlerrml); printf("sqlca.sqlerrm.sqlerrmc = %s\n", sqlca.sqlerrm.sqlerrmc); printf("sqlerrp = %s\n", sqlca.sqlerrp); printf("sqlerrd = %f\n", sqlca.sqlerrd); printf("sqlext = %s\n", sqlca.sqlext);

int main()
{

/* exec sql begin declare section */ /* declare the variables used by sql */

#line 21 "dys_btwn_dates.cpg"
char t_date1 [] = { '1' , '9' , '9' , '9' , '-' , '0' , '3' , '-' , '0' , '1' , '\0' } ;

#line 22 "dys_btwn_dates.cpg"
char t_date2 [] = { '1' , '9' , '9' , '9' , '-' , '0' , '1' , '-' , '0' , '1' , '\0' } ;

#line 23 "dys_btwn_dates.cpg"
int date_rtrn = 20 ;

#line 24 "dys_btwn_dates.cpg"
char t_in_date [ 12 ] ;
/* exec sql end declare section */
#line 26 "dys_btwn_dates.cpg"
/* declare the variables used by sql */

{ ECPGconnect(__LINE__, "pma" , NULL,NULL , NULL, 0); }
#line 28 "dys_btwn_dates.cpg"

/* exec sql whenever sqlerror sqlprint ; */

#line 30 "dys_btwn_dates.cpg"
/* turn on verbose sql error ckg */

printf("after whenever\n");

{ ECPGdo(__LINE__, NULL, "create function dys_btwn_dates ( date , date ) returns int as '
BEGIN
RETURN $1 - $2;
END;
' language 'c' ", ECPGt_EOIT, ECPGt_EORT);
#line 38 "dys_btwn_dates.cpg"

if (sqlca.sqlcode < 0) sqlprint();}
#line 38 "dys_btwn_dates.cpg"

DEBUG_SQL

printf("after create\n");

/* declare date_crsr cursor for select dta_date from stck_dta_tbl where dta_date < '1999-02-04' and ? >= dys_btwn_dates ( ? , dta_date ) */
#line 47 "dys_btwn_dates.cpg"

printf("after cusrsor\n");

{ ECPGdo(__LINE__, NULL, "declare date_crsr cursor for select dta_date from stck_dta_tbl where dta_date < '1999-02-04' and ? >= dys_btwn_dates ( ? , dta_date ) ",
ECPGt_int,&(date_rtrn),1L,1L,sizeof(int),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_char,&(t_date1),0L,1L,1*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);
#line 51 "dys_btwn_dates.cpg"

if (sqlca.sqlcode < 0) sqlprint();}
#line 51 "dys_btwn_dates.cpg"

printf("after open cusrsor\n");
{ ECPGdo(__LINE__, NULL, "fetch forward next in date_crsr", ECPGt_EOIT,
ECPGt_char,(t_in_date),12L,1L,12*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
#line 53 "dys_btwn_dates.cpg"

if (sqlca.sqlcode < 0) sqlprint();}
#line 53 "dys_btwn_dates.cpg"

printf("after fetch\n");

DEBUG_SQL

printf("days = %d\n",t_in_date);

}
/*
DECLARE
date1 ALIAS FOR $1;
date2 ALIAS FOR $2;
*/

!!!!!!!!!!!!!!!!!!!!!!!!!!!! POSTMASTER DEBUG OUTPUT !!!!!!!!!!!!!!!!!!!!!!

[postgres(at)polaris pgsql]$ postmaster: ServerLoop: handling reading 4
postmaster: ServerLoop: handling reading 4
postmaster: ServerLoop: handling writing 4
postmaster: BackendStartup: pid 23327 user lynn db pma socket 4
postmaster child[23327]: starting with (postgres -d4 -v131072 -p pma )
FindExec: searching PATH ...
ValidateBinary: can't stat "/var/lib/pgsql/bin/postgres"
ValidateBinary: can't stat "/bin/postgres"
ValidateBinary: can't stat "/usr/bin/postgres"
ValidateBinary: can't stat "/usr/local/bin/postgres"
ValidateBinary: can't stat "/usr/X11R6/bin//postgres"
ValidateBinary: can't stat "/sbin/bin/postgres"
ValidateBinary: can't stat "/usr/sbin/postgres"
FindExec: found "/usr/local/pgsql/bin/postgres" using PATH
DEBUG: connection: host=[local] user=lynn database=pma
DEBUG: InitPostgres
DEBUG: StartTransactionCommand
DEBUG: query: select getdatabaseencoding()
DEBUG: parse tree: { QUERY :command 1 :utility <> :resultRelation 0 :into <> :isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable <> :jointree { FROMEXPR :fromlist <> :quals <>} :rowMarks () :targetList ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 19 :restypmod -1 :resname getdatabaseencoding :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { EXPR :typeOid 19 :opType func :oper { FUNC :funcid 1039 :functype 19 } :args <>}}) :groupClause <> :havingQual <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}
DEBUG: plan: { RESULT :startup_cost 0.00 :total_cost 0.00 :rows 0 :width 0 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 19 :restypmod -1 :resname getdatabaseencoding :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { EXPR :typeOid 19 :opType func :oper { FUNC :funcid 1039 :functype 19 } :args <>}}) :qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :resconstantqual <>}
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: StartTransactionCommand
DEBUG: query: SET client_encoding = 'SQL_ASCII'
DEBUG: parse tree: { QUERY :command 5 :utility ? :resultRelation 0 :into <> :isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable <> :jointree <> :rowMarks () :targetList <> :groupClause <> :havingQual <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}
DEBUG: ProcessUtility: SET client_encoding = 'SQL_ASCII'
DEBUG: CommitTransactionCommand
DEBUG: StartTransactionCommand
DEBUG: query: begin transaction
DEBUG: parse tree: { QUERY :command 5 :utility ? :resultRelation 0 :into <> :isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable <> :jointree <> :rowMarks () :targetList <> :groupClause <> :havingQual <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}
DEBUG: ProcessUtility: begin transaction
DEBUG: CommitTransactionCommand
DEBUG: StartTransactionCommand
DEBUG: query: create function dys_btwn_dates ( date , date ) returns int as '
BEGIN
RETURN $1 - $2;
END;
' language 'c'
DEBUG: parse tree: { QUERY :command 5 :utility ? :resultRelation 0 :into <> :isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable <> :jointree <> :rowMarks () :targetList <> :groupClause <> :havingQual <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}
DEBUG: ProcessUtility: create function dys_btwn_dates ( date , date ) returns int as '
BEGIN
RETURN $1 - $2;
END;
' language 'c'
ERROR: stat failed on file '
BEGIN
RETURN $1 - $2;
END;
': No such file or directory
DEBUG: AbortCurrentTransaction
DEBUG: proc_exit(0)
DEBUG: shmem_exit(0)
DEBUG: exit(0)
postmaster: reaping dead processes...
postmaster: CleanupProc: pid 23331 exited with status 0

!!!!!!!!!!!!!!!!!!!!!!!!!!!! XXGDB DEBUGER OUTPUT !!!!!!!!!!!!!!!!!!!!!!

Copyright 1998 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB. Type "show warranty" for details.
This GDB was configured as "i386-redhat-linux"...
(xxgdb) run
after whenever
sql error Postgres error: ERROR: stat failed on file '
BEGIN
RETURN $1 - $2;
END;
': No such file or directory
line 34.
sqlcaid = SQLCA
sqlabc = -1.998512
sqlcode = -1.998513
sqlca.sqlerrm.sqlerrml = 124
sqlca.sqlerrm.sqlerrmc = Postgres error: ERROR: stat failed on file '
BEGIN
RETURN $1 - $2;
END;
': No such file or directory
line 34.
sqlerrp = $2;
END;
': No such file or directory
line 34.
sqlerrd = -1.998512
sqlext = ry
line 34.
after create
after cusrsor

Program received signal SIGSEGV, Segmentation fault.
0x4012f777 in ECPGexecute () from /usr/lib/libecpg.so.3.0.0
(xxgdb)

!!!!!!!!!!!!!!!!!!!!!!!!!!!! PLATFORM INFO... !!!!!!!!!!!!!!!!!!!!!!

I am running Postgres 7.1 on Redhat 6.2... (I know it's old...)
Linux polaris 2.2.14-5.0 #1 Tue Mar 7 21:07:39 EST 2000 i686 unknown

test: dys_btwn_dates.cpg
/usr/local/pgsql/bin/ecpg -I/usr/local/pgsql/include -o dys_btwn_dates.c dys_btwn_dates.cpg
gcc -g3 -I/usr/include/pgsql -o dys_btwn_dates -lm -lc -lecpg -lpq dys_btwn_dates.c

No such file or directory ?!?!?!?!

What am I doing wrong????????

I REALLY don't want to write a days between dates function myself.
I would MUCH rather take advantage of the fact that Postgres uses
julian dates and therefore makes date calculations as easy as these
4 lines of code.

EXEC SQL CREATE FUNCTION dys_btwn_dates(date, date) RETURNS int AS '
BEGIN
RETURN $1 - $2;
END;
' LANGUAGE 'c';

Thanks for your help!

Best Regards,
Lynn P. Tilby
unixprgrmr(at)aol(dot)com
Ph: 480 632-8635

No file was uploaded with this report