is there an easy way to test for an SPL?
Rob Konikoff asked for an easier way than querying sysprocedures to test whether a stored procedure exists. Jack Parker and Colin Bull suggested dbschema -d database -f procname. Rob found dbschema always exits successfully, so he piped the output to grep for 'No procedure' and tested that result in a shell script on HP-UX 11.0 with IDS 7.2x.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Ladies and Gentlemen: Is there an easier way to test for the existence of a stored procedure than by querying the sysprocedures table for the specific procname name via sql? Rob
dbschema -d database -fprocedure
cheers
j.
----- Original Message -----
From: "Konikoff, R...." <konikoffr@BRAGG.ARMY.MIL>
To: <ids@iiug.org>
Sent: Monday, February 24, 2003 7:01 AM
Subject: is there an easy way to test for an SPL? [478]
> Ladies and Gentlemen:
>
> Is there an easier way to test for the existence of a stored procedure
than
> by querying the sysprocedures table for the specific procname name via
sql?
>
> Rob
>
>
>
dbschema
-d database -f procname [outfile]
will give you the code
dbschema -d database -f procname /dev/null
and check for the exit code will detect if it exists or not
Colin Bull
c.bull@VideoNetworks.com
> -----Original Message-----
> From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On
> Behalf Of Konikoff, R....
> Sent: 24 February 2003 12:02
> To: ids@iiug.org
> Subject: is there an easy way to test for an SPL? [478]
>
>
> Ladies and Gentlemen:
>
> Is there an easier way to test for the existence of a stored procedure than
> by querying the sysprocedures table for the specific procname name via sql?
>
> Rob
>
>
Ladies
and Gentlemen:
Thanks for the quick response. When I finally got around to plugging it in,
here is the final form. Any comments on better ways? (HPUX 11.0 with IDS
7.2x) This example searches a "testdb" for two procedures, p_aging (which
exists) and nadda (which doesn't exist). I piped it to a grep for "No
procedure" since the dbschema command has successful execution even if it
returns no procedure.
#!/usr/bin/sh
dbschema -d testdb -f p_aging | grep "No procedure"
if [ $? -eq 0 ]then
echo "Reload p_aging"
else
echo "p_aging exists"
fi
dbschema -d testdb -f nadda | grep "No procedure"
if [ $? -eq 0 ]then
echo "Reload nadda"
else
echo "nadda exists"
fi
Thanks.
Rob