Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    5

    Unanswered: LAMP-MSSQL odbc mssql cannot execute Procedure

    Hi

    I'm developing using LAMP and connect to MSSQL using freetds and odbc_mssql

    OS: Ubuntu 11.10 with LAMP
    MSSQL: Windows 2003 - SQL 2000 Standard and SQL 2005 Standard
    Adodb: latest 516 (just download it yesterday)

    freetds.conf
    [MSSQL]
    host = 192.168.1.100
    port = 1433
    tds version = 8.0

    odbc.ini
    [MSSQL]
    Driver = FreeTDS
    Servername = MSSQL
    Database = meDB

    This how I connect
    $db = ADONewConnection('odbc_mssql');
    $db->Connect('MSSQL','sa','8');

    For Select,Update,Insert, and Delete it work flawlessly and no charset problem.

    Yesterday I tried using procedure but it always return error.
    I create a simple procedure which do nothin just return the value i Input.

    create procedure sp_debug
    @id as int,
    @seconid as int,
    @out as int OUTPUT
    as
    set @out = @id + @secondid

    Then I call from php like this
    $val1 = 1;
    $val2 = 2;
    $stmt = $db->PrepareSP("sp_debug");
    $db->InParameter($stmt,$val1,"id");
    $db->InParameter($stmt,$val2,"secondid");
    $db->OutParameter($stmt,$val3,"out");
    $db->Execute($stmt);

    When I execute above procedure I got this error
    37000: [unixODBC][FreeTDS][SQL Server]Procedure or function 'sp_debug' expects parameter '@id', which not supplied

    seems InParamter not working...

    Can anybody help me

  2. #2
    Join Date
    Mar 2010
    Posts
    5

    Partial Solution

    Hi

    I manage to successfully call the procedure, but seems odbc doesn't allow in and out parameters

    $rs = $db->Execute("EXEC sp_debug 1,2");
    var_dump($rs); -> you can see the result here

    by the way returning result is done by adding "select" from procedure

    create procedure sp_debug
    @id as int,
    @seconid as int,
    as
    declare @out as int
    set @out = @id + @secondid
    select @out as outid --> remember to set the alias

    But there is another problem when I add transaction is not woking, help me plz.....

    create procedure sp_debug
    @id as int,
    @seconid as int,
    as
    begin transaction
    declare @out as int
    set @out = @id + @secondid
    select @out as outid --> remember to set the alias
    commit transaction

    TranError:
    IF @@TRANCOUNT <> 0
    ROLLBACK TRANSACTION
    RETURN

  3. #3
    Join Date
    Mar 2010
    Posts
    5

    Solved

    Hmm why no body answering my question ??

    I found the solution though.

    To execute procedure you need to use the 'call' , 'exec' not working
    and when using adodb with [unixODBC][FreeTDS][MSSQL] Prepare and PrepareSP not working either

    You just call it with {call sp_debug (2,3)} ---> the curly bracket is required
    So to call below procedure you need to write it like

    PHP Code:
    $rs $conn->Execute("{call sp_debug (2,3)}");
    $obj $rs->FetchNextObject();
    echo 
    $obj->OUTPUT 
    unixodbc/freetds doesn't support output parameters so we use select ( remember to give an alias ) at the end of our procedure as workaround
    Code:
    create procedure sp_debug
    @id as int,
    @seconid as int,
    as
    begin
    declare @out as int
    set @out = @id + @secondid
    select @out as output 
    end
    Tested working with procedure with transaction no problem.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •