Skip to Content.
Sympa Menu

freetds - [freetds] freetds 0.61 - dbrpcsend looping forever with stored proc with ou tput params

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Thomas Melzer (ToMeSoft)" <tmelzer AT tomesoft.de>
  • To: freetds AT lists.ibiblio.org
  • Subject: [freetds] freetds 0.61 - dbrpcsend looping forever with stored proc with ou tput params
  • Date: Tue, 13 May 2003 18:14:36 +0200

I'm having a problem with freetds 0.61 (and also with several snapshots from
cvs)
using php 4.3.1 mssql extension on freebsd 4.7p10.
Im calling a stored procedure with output parameters and return values from
php.
The php script keeps running forever.
Tracing down the problem seem to show that the call is running the
loop withing dbrpcsend forever.
The same php script and stored procedure accessed from a windows
machine with ms db-library runs fine.
Has anybody expected a similar behaviour an got a solution ?

Here my environment:
On my webserver:
FreeBSD 4.7P10
Apache 1.3.27
mod_php 4.3.1
FreeTDS 0.61

on my sqlserver:
Windows 2000 SP3
MSSQL 7.0 SP4

Here the Stored procedure:

CREATE PROCEDURE sb_InsertMerchant
@merchantid INTEGER OUTPUT,
@serviceid INTEGER,
@company VARCHAR(100),
@name VARCHAR(120),
@owner VARCHAR(120),
@owner2 VARCHAR(120),
@street VARCHAR(160),
@zip VARCHAR(5),
@city VARCHAR(100),
@phone VARCHAR(40),
@fax VARCHAR(40),
@email VARCHAR(130),
@contact VARCHAR(200),
@waranty VARCHAR(200),
@tt TINYINT,
@brand_a TINYINT = 0,
@brand_b TINYINT = 0,
@brand_c TINYINT = 0
AS
DECLARE @password VARCHAR(50)
DECLARE @fetch_stat INTEGER

BEGIN TRANSACTION

SELECT @merchantid = 0

IF @serviceid = 0
BEGIN
INSERT INTO clients(custno, zip, active, regdate,
custgroup)
VALUES ('ABCDEFGH', @zip, 0, GETDATE(), NULL)

SELECT @merchantid = @@IDENTITY

SET @password = LEFT(@city, 1) + CAST(@merchantid +
100000 AS VARCHAR(10))

UPDATE clients
SET custpassword = @password
WHERE id = @merchantid
END
ELSE
BEGIN
DECLARE passwd_cursor CURSOR
LOCAL
FAST_FORWARD
FOR
SELECT custpassword
FROM clients
WHERE id = @serviceid

OPEN passwd_cursor

FETCH NEXT
FROM passwd_cursor
INTO @password

SET @fetch_stat = @@FETCH_STATUS

CLOSE passwd_cursor
DEALLOCATE passwd_cursor

IF @fetch_stat = 0
BEGIN
SELECT @merchantid = @serviceid
END
ELSE
BEGIN
ROLLBACK
RETURN(1)
END
END

IF @merchantid <> 0
BEGIN
INSERT INTO sbmerchant(
id,company,street,zip,city,phone,
fax,email,contact,waranty,
custpassword,tt,name,owner,owner2
)
VALUES (

@merchantid,@company,@street,@zip,@city,@phone,
@fax,@email,@contact,@waranty,
@password,@tt,@name,@owner,@owner2
)

IF @brand_a <> 0
INSERT INTO sbmerchant_brand(merchantid,
brand)
VALUES (@merchantid, 'A')

IF @brand_b <> 0
INSERT INTO sbmerchant_brand(merchantid,
brand)
VALUES (@merchantid, 'B')

IF @brand_c <> 0
INSERT INTO sbmerchant_brand(merchantid,
brand)
VALUES (@merchantid, 'C')

COMMIT

RETURN(0)
END
ELSE
BEGIN
ROLLBACK
RETURN(2)
END


here a snippet of my php code

....

$spcmd = mssql_init("sb_InsertMerchant", $db) or die
( "db stored proc init - " . mssql_get_last_message() );

mssql_bind($spcmd, "RETVAL", $cmdresult, SQLINT4);
mssql_bind($spcmd, "@merchantid", $merchantid,
SQLINT4, true);
mssql_bind($spcmd, "@serviceid", $serviceid,
SQLINT4);
mssql_bind($spcmd, "@company", $company, SQLVARCHAR,
false, false, 100);
mssql_bind($spcmd, "@name", $name, SQLVARCHAR,
false, false, 120);
mssql_bind($spcmd, "@owner", $owner, SQLVARCHAR,
false, false, 120);
mssql_bind($spcmd, "@owner2", $owner2, SQLVARCHAR,
false, false, 120);
mssql_bind($spcmd, "@street", $street, SQLVARCHAR,
false, false, 160);
mssql_bind($spcmd, "@zip", $zip, SQLVARCHAR, false,
false, 5);
mssql_bind($spcmd, "@city", $city, SQLVARCHAR,
false, false, 100);
mssql_bind($spcmd, "@phone", $phone, SQLVARCHAR,
false, false, 40);
mssql_bind($spcmd, "@fax", $fax, SQLVARCHAR, false,
false, 40);
mssql_bind($spcmd, "@email", $email, SQLVARCHAR,
false, false, 40);
mssql_bind($spcmd, "@contact", $contact, SQLVARCHAR,
false, false, 200);
mssql_bind($spcmd, "@waranty", $waranty, SQLVARCHAR,
false, false, 200);
mssql_bind($spcmd, "@tt", $tt, SQLINT1);
mssql_bind($spcmd, "@brand_a", $brand_a, SQLINT1);
mssql_bind($spcmd, "@brand_b", $brand_b, SQLINT1);

mssql_execute($spcmd) or die ( "db stored proc
execute - " . mssql_get_last_message() );

...

Thanks in advance
-- Thomas Melzer




  • [freetds] freetds 0.61 - dbrpcsend looping forever with stored proc with ou tput params, Thomas Melzer (ToMeSoft), 05/13/2003

Archive powered by MHonArc 2.6.24.

Top of Page