Results 1 to 9 of 9

Thread: mssql_num_rows()

  1. #1
    Join Date
    Jul 2004
    Posts
    153
    Rep Power
    0

    Default mssql_num_rows()

    Guys I'm at my wit's end...
    I have stored procedure which works as it should by that I mean it produces the expected result when executed...

    Within the SP a temp table is defined...such as this

    Code:
    CREATE TABLE #TMPTBL_MATERIAL
    (
    		xNumber VARCHAR(50)
    )
    
    
    DECLARE @BUILD VARCHAR(3000)
    
    SET @BUILD = '
    INSERT INTO #TMPTBL_MATERIAL(xNumber)
    SELECT DISTINCT i.poNumber
    FROM dbo.po_items i
    WHERE
    i.itemNumber IN ('+ @material + ')
    AND i.dateCreated = (
    	select Max(dateCreated) from dbo.po_items where itemNumber = i.itemNumber
    )'
    
    EXEC(@BUILD)
    this temp table is later populated using a insert into statement which is executed as dynamic statement using EXEC(@sqlStr). Suffice to say the table is populated as expected.
    The temp table is then used in a join for the main query within the same SP. This also is executed fine when run directly within sql server.

    However when the SP is executed from (PHP) code...mssql_num_rows($result) always returns 1 which is obviously incorrect. Only 1 record is being retrieved from the result, furthermore the record that is accessed never turns out to be the first/top record.

    Is there anyone out there who has had this problem or have any idea what could be root cause. I need a solution ASAP.

  2. #2
    Join Date
    May 2004
    Posts
    168
    Rep Power
    0

    Default

    It seems that the record you are retrieving will always be the record with the most recent createdate. Using date as a criteria rarely every produces more than one record unless the entries were created in the same millisecond. I do not have SQL on this machine and will check further once i'm at a machine. Let me know if that corrected your issue.
    ||| Knowledge is Power |||

  3. #3
    Join Date
    Jun 2007
    Posts
    678
    Rep Power
    0

    Default

    I would be very helpful if you'd post the php code.


    As soon as you make something idiot proof, Nature makes better idiots!!!

    Think big, think smart, think linux

  4. #4
    Join Date
    Jul 2004
    Posts
    153
    Rep Power
    0

    Default

    @fresh...you seem to have forgotten the point I made that the SP when run from sql server does return the current result set ALL the time. You made a keen observation about the createdate however it is immaterial to the actual problem...the @material parameter is string of comma separate values which is used as part of selection criteria within the dynamic statement...yes it will return the last row for each comma separated value at it works perfectly with SQL. From a database standpoint I believe the query code is sound, however there is something within it that PHP doesn't like or can't process.

    As for posting the PHP code, I thought about it and I'll see what portions of it can safely be presented here. I must point out that if I change the query which is executed from PHP expected result is produced. Likewise if i remove certain areas of the SP it works however those sections are the critical elements.

    Thanks for the response though...

  5. #5
    Join Date
    Dec 2002
    Posts
    500
    Rep Power
    0

    Default

    i think its returning the result of exec(build) which is 1 and not necessarily the result of executing the script inside 'build'
    Cultured in Aggression and Koding like a Warrior!!
    “Common sense is instinct. Enough of it is genius.” - George Bernard Shaw.
    "The significant problems we face cannot be solved by the same level of thinking that created them." - Albert Einstein

  6. #6
    Join Date
    Jul 2004
    Posts
    153
    Rep Power
    0

    Default

    Mint - I honestly don't know...each statement correctly output/traces the number of records affected/selected. i.e after the exec(@build) the appropriate affected count is given, like wise when the temp table is joined to the other tables it reports the correct number when executed directly...
    The problem persists even when NOROWCOUNT is set to ON
    I'm not confused as where does PHP retrieve its row count from.

    I going to try something a bit later and see what happens.

  7. #7
    Join Date
    Dec 2002
    Posts
    500
    Rep Power
    0

    Default

    why not try explicitly extracting the row count and return it in an output parameter, something like
    Code:
    create proc someTopSecretProcedure(
       @material varchar(500),
       @RecordCount int output,
    )
    as
    
    CREATE TABLE #TMPTBL_MATERIAL
    (
    		xNumber VARCHAR(50)
    )
    
    declare @Build nvarchar(3000)
    
    select @Build = 'INSERT INTO #TMPTBL_MATERIAL(xNumber)
    SELECT DISTINCT i.poNumber FROM dbo.po_items i WHERE i.itemNumber IN ('+ @material + ') AND i.dateCreated = (select Max(dateCreated) from dbo.po_items where itemNumber = i.itemNumber); 
    select @RecordCount = @@RowCount;'
    
    --EXEC(@BUILD)
    exec sp_executesql @Build, '@RecordCount output', @RecordCount output
    go
    if you cant use output parameters then do a
    Code:
    select @RecordCount as RowsAffected
    as the last line of the procedure... and declare @RecordCount inside the stored proc

    additionally, if you are not using the data in the temp able then use a table variable
    Code:
    declare @tblMaterial table (xNumber VARCHAR(50) primary key)
    Last edited by icymint3; Jun 4, 2010 at 02:07 PM.
    Cultured in Aggression and Koding like a Warrior!!
    “Common sense is instinct. Enough of it is genius.” - George Bernard Shaw.
    "The significant problems we face cannot be solved by the same level of thinking that created them." - Albert Einstein

  8. #8
    Join Date
    Jul 2004
    Posts
    153
    Rep Power
    0

    Default

    I am not entirely interested in the actual rowcount of the result set.
    The issue is this when the SP is executed it generates x number of records, however result is retrieved through the PHP call, it only contains 1 record. So not only does the mssql_num_rows() return 1 the actual result resource only contains 1 record.

    With your example even if the retrieve the output variable with the actual row count, if I iterate through the result I will hit nothing beyond the first index.

    Thanks though...i don't think i was clear with my issue.

  9. #9
    Join Date
    Jul 2004
    Posts
    153
    Rep Power
    0

    Default

    Mint offered some good debugging ideas which reminded me of the solution...turns out that the issue was with the parameters. From PHP the parameters seem to pass some unprintable characters to the SP. I had this issue some time ago but didn't remember. To solve it I have to create a function which cleans the parameters before being sent to the db

Posting Permissions

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