Re: DBD::ODBC SQL Server Stored Procedure
Full code please. On Wed, Aug 29, 2012 at 12:01 PM, Gorrebeeck, Robert gorrebeec...@cvty.com wrote: All I am having an issue with retrieving output parameters from a SQL Server stored procedure. Here is the definition of the stored procedure PROCEDURE [TestProcess] @Id NUMERIC(20,0), @RefID CHAR(36) OUTPUT, @ErrorIDINT OUTPUT And here is my perl code #bind the input and output parameters $sth-bind_param(1, $clm_tracking_id); $sth-bind_param_inout(2, \$ID, DBI::SQL_CHAR); $sth-bind_param_inout(3, \$ErrorId, DBI::SQL_INT); $sth-execute(); $sth-finish; print $ID\n; print $ErrorId\n; When I run the stored procedure by itself , I get values back, but when I run it from my perl script, the values are empty. Not sure what is going on - any advice would be greatly appreciated Thanks Robert Gorrebeeck Email Confidentiality Notice: The information contained in this transmission is confidential, proprietary or privileged and may be subject to protection under the law, including the Health Insurance Portability and Accountability Act (HIPAA). The message is intended for the sole use of the individual or entity to whom it is addressed. If you are not the intended recipient, you are notified that any use, distribution or copying of the message is strictly prohibited and may subject you to criminal or civil penalties. If you received this transmission in error, please contact the sender immediately by replying to this email and delete the material from any computer. -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
RE: DBD::ODBC SQL Server Stored Procedure
Let me add one more bit of information I am calling another stored procedure from the original stored procedure. This is when I lose my values. If the account loc variable is Null I set the ErrorId -1 - and that gets returned to the perl script - it is when I call the CreateFeed stored procedure that I lose the output values The stored proc (as is it bellows I know is incorrect but just for illustration purposes) See below ROCEDURE [CreateReferralDataProcess] @TrackingId NUMERIC(20,0), @RefID CHAR(36) OUTPUT, @RefErrorID CHAR(36) OUTPUT AS SET NOCOUNT ON SELECT@AccountLoc = ACCOUNT_LOC FROMACCOUNT_DETAILS ACCOUNT_DETAILS WHERE ID = @TrackingID --if we dont have an account loc then error out and return to the perl script IF @AccountLoc IS NULL OR @AccountLoc =0 BEGIN SELECT @ErrorID -1 SELECT @RefErrorID = CONVERT(CHAR(36), @ErrorID) SELECT @RefID = CONVERT(CHAR(36), -1) RETURN END ELSE BEGIN --Now that we have all of the account info we can start to create the referral EXEC ChangePoint.dbo.CreateFeed @AccountLoc, @ReferralID OUTPUT, @ErrorID OUTPUT --Lets check the return value of the ErrorId IF @ErrorID 0 BEGIN SELECT @RefErrorID = RTRIM(CONVERT(CHAR(36), @ErrorID)) END ELSE BEGIN --This means that we got a valid referral id back --Convert so we can return the value to our script SELECT @RefID = RTRIM(CONVERT(CHAR(36), @ReferralID)) END END RETURN SET NOCOUNT OFF Robert Gorrebeeck Sr. Apps Dev Analyst Coventry Workers Comp Services Solutions to Restore Health and Productivity Office: (972) 473-2942 gorrebeec...@cvty.commailto:gorrebeec...@cvty.com www.coventrywcs.comhttp://www.coventrywcs.com From: Gorrebeeck, Robert Sent: Wednesday, August 29, 2012 2:02 PM To: beginners@perl.org Subject: DBD::ODBC SQL Server Stored Procedure All I am having an issue with retrieving output parameters from a SQL Server stored procedure. Here is the definition of the stored procedure PROCEDURE [TestProcess] @Id NUMERIC(20,0), @RefID CHAR(36) OUTPUT, @ErrorIDINT OUTPUT And here is my perl code #bind the input and output parameters $sth-bind_param(1, $clm_tracking_id); $sth-bind_param_inout(2, \$ID, DBI::SQL_CHAR); $sth-bind_param_inout(3, \$ErrorId, DBI::SQL_INT); $sth-execute(); $sth-finish; print $ID\n; print $ErrorId\n; When I run the stored procedure by itself , I get values back, but when I run it from my perl script, the values are empty. Not sure what is going on - any advice would be greatly appreciated Thanks Robert Gorrebeeck Email Confidentiality Notice: The information contained in this transmission is confidential, proprietary or privileged and may be subject to protection under the law, including the Health Insurance Portability and Accountability Act (HIPAA). The message is intended for the sole use of the individual or entity to whom it is addressed. If you are not the intended recipient, you are notified that any use, distribution or copying of the message is strictly prohibited and may subject you to criminal or civil penalties. If you received this transmission in error, please contact the sender immediately by replying to this email and delete the material from any computer.
RE: DBD for SQL Server?
-Original Message- From: Richard Crawford [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 12, 2004 12:40 AM To: [EMAIL PROTECTED] Subject: DBD for SQL Server? Is there a DBD for SQL Server so that I can connect to our SQL Server 2000 database with DBI? I've hunted all over CPAN but I can't seem to find one. I would try DBD::ODBC José. DISCLAIMER This e-mail and any attachment thereto may contain information which is confidential and/or protected by intellectual property rights and are intended for the sole use of the recipient(s) named above. Any use of the information contained herein (including, but not limited to, total or partial reproduction, communication or distribution in any form) by other persons than the designated recipient(s) is prohibited. If you have received this e-mail in error, please notify the sender either by telephone or by e-mail and delete the material from any computer. Thank you for your cooperation. For further information about Proximus mobile phone services please see our website at http://www.proximus.be or refer to any Proximus agent. -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] http://learn.perl.org/ http://learn.perl.org/first-response
Re: DBD for SQL Server?
--- Richard Crawford [EMAIL PROTECTED] wrote: Is there a DBD for SQL Server so that I can connect to our SQL Server 2000 database with DBI? I've hunted all over CPAN but I can't seem to find one. Windows uses the Open Database Connectivity (ODBC) Model. You can connect to an MS-SQL server using Win32::ODBC, available at http://search.cpan.org/~gsar/libwin32-0.191/ODBC/ODBC.pm More info - perldoc Win32::ODBC HTH, Alok __ Do you Yahoo!? Yahoo! Movies - Buy advance tickets for 'Shrek 2' http://movies.yahoo.com/showtimes/movie?mid=1808405861 -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] http://learn.perl.org/ http://learn.perl.org/first-response
RE: DBD for SQL Server?
-Original Message- From: Alok Bhatt [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 12, 2004 11:49 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: DBD for SQL Server? --- Richard Crawford [EMAIL PROTECTED] wrote: Is there a DBD for SQL Server so that I can connect to our SQL Server 2000 database with DBI? I've hunted all over CPAN but I can't seem to find one. Windows uses the Open Database Connectivity (ODBC) Model. You can connect to an MS-SQL server using Win32::ODBC, available at Then you will no longer be using DBI API (what OP wanted) ! The advantage of DBD::ODBC (or DBD::ADO ) Is the portability of your code since you Will be using the same api syntax regarless the The database driver (DBD) you are dealing with behind DBI. The connection is always my $dbh=DBI-connect(...); Not a new api like: $Data = new Win32::ODBC(...); Which obviously will not work if you need To use your same code on a database running on unix box. HTH, José. DISCLAIMER This e-mail and any attachment thereto may contain information which is confidential and/or protected by intellectual property rights and are intended for the sole use of the recipient(s) named above. Any use of the information contained herein (including, but not limited to, total or partial reproduction, communication or distribution in any form) by other persons than the designated recipient(s) is prohibited. If you have received this e-mail in error, please notify the sender either by telephone or by e-mail and delete the material from any computer. Thank you for your cooperation. For further information about Proximus mobile phone services please see our website at http://www.proximus.be or refer to any Proximus agent. -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] http://learn.perl.org/ http://learn.perl.org/first-response
Re: DBD for SQL Server?
NYIMI Jose (BMB) wrote: -Original Message- From: Alok Bhatt [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 12, 2004 11:49 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: DBD for SQL Server? --- Richard Crawford [EMAIL PROTECTED] wrote: Is there a DBD for SQL Server so that I can connect to our SQL Server 2000 database with DBI? I've hunted all over CPAN but I can't seem to find one. Windows uses the Open Database Connectivity (ODBC) Model. You can connect to an MS-SQL server using Win32::ODBC, available at Then you will no longer be using DBI API (what OP wanted) ! The advantage of DBD::ODBC (or DBD::ADO ) Is the portability of your code since you Will be using the same api syntax regarless the The database driver (DBD) you are dealing with behind DBI. The connection is always my $dbh=DBI-connect(...); Not a new api like: $Data = new Win32::ODBC(...); Which obviously will not work if you need To use your same code on a database running on unix box. HTH, José. I've learned that in order to use DBD::ODBC I will still need to install an ODBC driver on the machine (which is actually running Solaris 9). Not a problem, if I can find one. -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] http://learn.perl.org/ http://learn.perl.org/first-response
Re: DBD for SQL Server?
On Tue, 2004-05-11 at 18:40, Richard Crawford wrote: Is there a DBD for SQL Server so that I can connect to our SQL Server 2000 database with DBI? I've hunted all over CPAN but I can't seem to find one. Richard, I'm running a mod_perl site connected to a SQL Server 2000 backend via DBD::ODBC with a driver from OpenlinkSW. Go to http://www.openlinksw.com. They will let you download a full working driver, but it has a 30 license. If you need more than 30 days you'll have to redownload the driver and it'll email you another 30 day license. It took us about 6 months of testing before we bought the driver for about $600, buti it was well worth it. They'll also give you the iODBC SDK (driver manager) and phone support even if you're evaluating. Lot's of praise on the phone support as they worked with me for 5 days and had a developer helping me as I was having trouble getting it interfaced with mod_perl for some weird reason. Long story short mod_perl was only seeing unixODBC (another driver manager) and so I had use unixODBC instead of iODBC. No biggie, it was tracking this down that was the problem. Hope this helps, Kevin -- Kevin Old [EMAIL PROTECTED] -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] http://learn.perl.org/ http://learn.perl.org/first-response
RE : DBD for SQL Server?
-Message d'origine- De : Richard Crawford [mailto:[EMAIL PROTECTED] Envoyé : mercredi 12 mai 2004 22:17 À : NYIMI Jose (BMB) Cc : Alok Bhatt; [EMAIL PROTECTED] Objet : Re: DBD for SQL Server? NYIMI Jose (BMB) wrote: -Original Message- From: Alok Bhatt [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 12, 2004 11:49 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: DBD for SQL Server? --- Richard Crawford [EMAIL PROTECTED] wrote: Is there a DBD for SQL Server so that I can connect to our SQL Server 2000 database with DBI? I've hunted all over CPAN but I can't seem to find one. Windows uses the Open Database Connectivity (ODBC) Model. You can connect to an MS-SQL server using Win32::ODBC, available at Then you will no longer be using DBI API (what OP wanted) ! The advantage of DBD::ODBC (or DBD::ADO ) Is the portability of your code since you Will be using the same api syntax regarless the The database driver (DBD) you are dealing with behind DBI. The connection is always my $dbh=DBI-connect(...); Not a new api like: $Data = new Win32::ODBC(...); Which obviously will not work if you need To use your same code on a database running on unix box. HTH, José. I've learned that in order to use DBD::ODBC I will still need to install an ODBC driver on the machine (which is actually running Solaris 9). Not a problem, if I can find one. http://www.unixodbc.org/ José. -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] http://learn.perl.org/ http://learn.perl.org/first-response
