perl_dbi_debugging_advice_ODBC

This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.



Subject: Re: Calling stored procedure in DBD::ODBC
From: Chris Winters <cwinters@intes.net>
Date: Tue, 6 Jun 2000 08:49:22 -0400


Rekha Murthy (RekhaM@cnet10.cnet.com) [000606 01:36]: wrote:


> I am trying to call a stored procedure from my script. I am using DBD::ODBC.
> My database server is MS SQL server 7.0. Can anyone tell me how to call a
> stored procedure using DBD::ODBC. I have enclosed my script. Where am I
> going wrong?
> 

> ########### My script ############
> 
> $sth = $dbh->prepare('EXEC Ins_ProjNew "$in{$profile_creator}"
> "$in{$proj_mgr}" "$in{$project_sponsor}"  "$in{$project_name}"
> "$project_status" "$in{$project_short_description}"
> "$in{$project_long_description}" "$in{$status_summary}"
> "$in{global_percent_complete}" "$in{$edit_password}"
> "$in{$password_protect}" "$in{$view_password}" "$lifecycle_status"
> "$last_modified_date" "$start_deliverable" "$start_date" "$end_deliverable"
> "$end_date" "$in{$product_associated}" "$usr_profile_creator"
> "$profile_creator_role" "$usr_proj_mgr" "$project_manager_role"
> "$usr_project_sponsor" "$project_sponsor_role" "$owner_org_id" "$owner_role"
> "$sponsor_org_id" "$sponsor_role" ') 
> 		or die "Couldn't execute stored procedure : " .
> $dbh->errstr;
> 
> $ins_proj = $sth->execute();
> 
> #############################################################

If your stored procedure returns a resultset (rows), then you can
fetch from it just like any other SQL statement.

Additionally, I don't think your statement below is doing what yout
hink it will do. Since you enclosed the entire string in single
quotes, no interpolation will be done and the value
'$in{$profile_creator}' will be passed to the stored procedure rather
than the value of the key $profile_creator in the %in hash.

I'd strongly recommend using 'RaiseError => 1' in your $dbh
initialization (or setting it before you execute this statement:
$dbh->{RaiseError} = 1), wrapping the entire thing in an eval {} block
and checking the value of $@ afterward. It's much cleaner. Something
like: 

 my $sql = qq(EXEC Ins_ProjNew "$in{$profile_creator}"
  "$in{$proj_mgr}" "$in{$project_sponsor}"  "$in{$project_name}"
  "$project_status" "$in{$project_short_description}"
  "$in{$project_long_description}" "$in{$status_summary}"
  "$in{global_percent_complete}" "$in{$edit_password}"
  "$in{$password_protect}" "$in{$view_password}" "$lifecycle_status"
  "$last_modified_date" "$start_deliverable" "$start_date" "$end_deliverable"
  "$end_date" "$in{$product_associated}" "$usr_profile_creator"
  "$profile_creator_role" "$usr_proj_mgr" "$project_manager_role"
  "$usr_project_sponsor" "$project_sponsor_role" "$owner_org_id" "$owner_role"
  "$sponsor_org_id" "$sponsor_role");
 my ( $sth );
 eval {
   $sth = $dbh->prepare( $sql );
   $sth->execute;
 };
 die "Cannot prepare/execute:\nSQL: $sql\nError: $@" if ( $@ );
 
===

Subject: RE: Calling stored procedure in DBD::ODBC
From: NIEMANRA@pjm.com
Date: Tue, 6 Jun 2000 08:38:55 -0400 

Here is a sample where I am calling MS SQL Server "sp_Password" to change
someone's password from a PERL script:

#
#  set the input to the sp
#
$execinput="$passwd, $newpasswd";
if ($debug) { print DEBUG "Input to the stored procedure is: $execinput  \n" };
eval {
if ($debug) { print DEBUG "Issuing the Stored Procedure to SQL Server  \n" };
my $sel = $dbh->prepare( "exec sp_password $execinput" );

$sel->execute( ) ; 
       };
if ($@) {
          print  "Cannot execute stored procedure. Error is $@ \n" ;
          print  DEBUG "Cannot execute stored procedure. Error is $@ \n" ;
      };

===
Subject: Re: Calling stored procedure in DBD::ODBC
From: "Brendan Fagan" <Bfagan@harthosp.org>
Date: Tue, 06 Jun 2000 10:26:34 -0400

Rekha,

When using the DBD::ODBC driver with SQL Server 7.0 you must
consider that the stored procedure must be called with the
database information unless you are connected specifically
to that database.

By default, your DSN will set as default to the "master"
database.  Unless your DSN is specifying a different
database as default or you are connecting with DBI to a
specific database, you must specify the full schema of the
stored procedure.  For example:

If my procedure "sp_my_procedure" is in the "Northwind"
database then I need to specify as: "exec
Northwind..sp_my_procedure"

Normally, the full schema would include the database owner,
but is not necessary.  For example: "exec
Northwind.dbo.sp_my_procedure"

Lastly, parameters to a stored procedure must be separated
with a comma and numeric parameters (aside from date) do not
require quotes.

===

the rest of The Pile (a partial mailing list archive)

doom@kzsu.stanford.edu