Generic Stored Procedure....
Hi,
I have the following SP.....
drop PROCEDURE dr;
CREATE PROCEDURE dr(tabname char(18))
RETURNING INT, INT, CHAR(70);
DEFINE sql_err INT;
DEFINE isam_err INT;
DEFINE error_info CHAR(70);
DEFINE err_num INT;
DEFINE extract_schema CHAR(100);
DEFINE cr_table CHAR(100);
DEFINE tbname CHAR(18);
SET DEBUG FILE TO '/tmp/trace.out';
LET sql_err = 0;
LET isam_err = 0;
LET error_info = "no errors";
LET err_num = 0;
TRACE ON;
LET tbname = tabname;
BEGIN
ON EXCEPTION IN (-310) SET err_num
IF err_num = -310 THEN
LET sql_err = -310;
LET isam_err = 999; --Not sure what the isam
for table exists is
LET error_info = "Create table failed: Table
already exists" ;
RETURN sql_err, isam_err, error_info;
END IF;
END EXCEPTION
LET extract_schema = "/opt/informix/bin/dbschema -ss
-t tabname -d dba_testing /tmp/tabname.sql -q";
SYSTEM extract_schema;
drop table tabname;
END;
END PROCEDURE;
grant execute on dr to e1817r;
When I execute this stored procedure using
execute procedure dr("t1");
I get an error saying
206: The specified table (tabname) is not in the
database.
111: ISAM error: no record found.
How do I get to use the variable tabname, that I
receive as an argument.
Pls advice.
Thanks
Abraham
__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com
Try
LET extract_schema = "/opt/informix/bin/dbschema -ss-t " || tabname || "
-d dba_testing /tmp/" || tabname || ".sql -q";
"Abraham Kir...." wrote:
> Hi,
> I have the following SP.....
> drop PROCEDURE dr;
> CREATE PROCEDURE dr(tabname char(18))
> RETURNING INT, INT, CHAR(70);>
> DEFINE sql_err INT;
> DEFINE isam_err INT;
> DEFINE error_info CHAR(70);
> DEFINE err_num INT;
> DEFINE extract_schema CHAR(100);
> DEFINE cr_table CHAR(100);
> DEFINE tbname CHAR(18);
> SET DEBUG FILE TO '/tmp/trace.out';
>
> LET sql_err = 0;
> LET isam_err = 0;
> LET error_info = "no errors";
> LET err_num = 0;
> TRACE ON;
> LET tbname = tabname;
>
> BEGIN
> ON EXCEPTION IN (-310) SET err_num
> IF err_num = -310 THEN
> LET sql_err = -310;
> LET isam_err = 999; --Not sure what the isam
> for table exists is
> LET error_info = "Create table failed: Table
> already exists" ;
> RETURN sql_err, isam_err, error_info;
> END IF;
> END EXCEPTION
> LET extract_schema = "/opt/informix/bin/dbschema -ss
> -t tabname -d dba_testing /tmp/tabname.sql -q";
> SYSTEM extract_schema;
> drop table tabname;>
> END;
> END PROCEDURE;
> grant execute on dr to e1817r;>
> When I execute this stored procedure using
> execute procedure dr("t1");>
> I get an error saying
> 206: The specified table (tabname) is not in the
> database.
> 111: ISAM error: no record found.>
> How do I get to use the variable tabname, that I
> receive as an argument.
>
> Pls advice.
>
> Thanks
> Abraham
>
> __________________________________
> Do you Yahoo!?
> The New Yahoo! Search - Faster. Easier. Bingo.
> http://search.yahoo.com