dimanche 28 juin 2015

PHP and MSSQL get last inserted ID. Working on VM Work server but not on HostGator shared server

I have a function called dbInsert() that I use at work on our own windows servers with MS SQL. It works great to get the last inserted row ID. But when I use it on host gator shared plan, it always returns 0. I literally copy and pasted the functions.

Can anyone see issues with my functions that might effect its cross server compatibility?

We are using MS SQL 2003 at work and 2008 R2 on HostGator.

Does anyone know of limitations on HostGator shared plans? I spoke to them and they say this is a coding issue and they dont help with that. Its really annoying, I have tried for 3 days, my site build is on hold until I get this figured out. Also, no log errors.

I have found a work around doing the select Scope_identity and insert in two completely separate queries, but It scares me that there might be another insert by another user in between those two taking place.

Here are the relevant functions.

Insert:

 /**
 * Inserts the sql query and returns the ID of the Row Inserted.
 *
 * @param string $IncomingSql The sql query that you want to execute.
 * @return int/string returns the ID of inserted row, or 0 if no result.
 */
function dbInsert($_IncomingSql)
{
    $sql=$_IncomingSql.'; SELECT SCOPE_IDENTITY();';
    $Result=dbQuery($sql);
    sqlsrv_next_result($Result);
    sqlsrv_fetch($Result);

    $stmt = sqlsrv_get_field($Result, 0);
    if($stmt >0)
    {    
        return $stmt;
    }
    else {
        return 0;
    }
}

dbQuery:

/**
 * This function is designed to catch SQL errors and dispese to the appropriate channels.
 * It can send error emails or display on screen at the time of error.
 * All functions accessing the database need to go through this function in order to catch errors in a standardized way for all pages.
 *
 * @param string $_IncomingSql The sql query that you want to execute.
 * @Param string $_Cursor OPTIONAL PARAMETER - This is the cursor type for scrolling the result set. More Info: http://ift.tt/1IEfih6
 * @Return resource/bool
 */
function dbQuery($_IncomingSql)
{
    $Result=sqlsrv_query(CONN, $_IncomingSql);
    //Catch sql errors on query
    if($Result===false) {
        if(($errors=sqlsrv_errors())!=null) {
            CatchSQLErrors($errors, $_IncomingSql);
        }
    }
    return $Result;
}

CatchSQLErrors:

function CatchSQLErrors($errors, $_IncomingSql)
{
    foreach($errors as $error)
    {
        //error display
        $Path='http://'.$_SERVER['HTTP_HOST'].$_SERVER['REQUEST_URI'];
        $Err='<strong>SQL ERROR<br/></strong>'.Format($_IncomingSql).'<br /><span style="font-weight: 600;">Error: </span> '.$error['message'];
        if(ON_SCREEN_ERRORS===TRUE) {
            err($Err);
        }
        $Err=Format($_IncomingSql).'<br /><span style="font-weight: 600;">Error: </span> '.$error['message'];
        if(SEND_ERROR_EMAILS===TRUE) {
            gfErrEmail($Err, $Path, 'SQL Error');
        }
    }
    return 0;
}

Aucun commentaire:

Enregistrer un commentaire