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. ===