current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote -1 down vote favorite
1

Help me to execute stored procedure with parameters using PHP ADODB, MSSQ.

$stmt = $db->PrepareSP('VM_SEARCH_SMSC');//stored procedure VM_SEARCH_SMSC
$search = 'test';
$string = 'SearchString';
$db->InParameter($stmt,$search,$string);

//return values.
$data = array();
$data['SNo'] = 0;
$data['SNSCID'] = 0;
$data['SMSCNAME'] = 0;
$data['MODE'] = '';
$data['ISACTIVE'] = 0;
$data['FIELD1'] = 0;
$data['ISBIND'] = 0;
$data['BNDOTHERRSRC'] = '';
$data['THRPT'] = '';

$db->OutParameter($statement, $data['SNo'], 'SNo');
$db->OutParameter($statement, $data['SNSCID'], 'SNSCID');
$db->OutParameter($statement, $data['SMSCNAME'], 'SMSCNAME');
$db->OutParameter($statement, $data['MODE'], 'MODE');
$db->OutParameter($statement, $data['ISACTIVE'], 'ISACTIVE');
$db->OutParameter($statement, $data['FIELD1'], 'FIELD1');
$db->OutParameter($statement, $data['ISBIND'], 'ISBIND');
$db->OutParameter($statement, $data['BNDOTHERRSRC'], 'BNDOTHERRSRC');
$db->OutParameter($statement, $data['THRPT'], 'THRPT');

$result = $db->Execute($stmt);
if (!$result){
    print $db->ErrorMsg();
    echo '<br /><br />';
}

Error:

SQLState: 42000 Error Code: 201 Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Procedure or function 'VM_SEARCH_SMSC' expects parameter '@SearchString', which was not supplied.

Somebody help me out

share|improve this question
    
possible duplicate of stackoverflow.com/questions/9038004/… – ughai May 1 at 5:56
    
There is no solution on the given link "ughai" – chandoo May 1 at 6:21
    
you can check the value returned by InParameter and see if it is actually binding value or silently failing? – ughai May 1 at 6:27
    
As per my investigation it seems that ADODB driver for MSSQL does not support stored procedure with parameters. Use sqlsrv bind parameter. – chandoo May 1 at 11:28
up vote 0 down vote accepted

As per my investigation it seems that ADODB driver for MSSQL does not support stored procedure with parameters. Use sqlsrv.

$serverName = "XX.XX.XX.XXX";
$connectionInfo = array( "Database"=>"XXXXX", "UID"=>"XXXXX", "PWD"=>"XXX","ConnectionPooling" => "1","MultipleActiveResultSets"=>'0');

$conn = sqlsrv_connect( $serverName, $connectionInfo );
if( $conn === false ) {
    die( print_r( sqlsrv_errors(), true));
}    

$stmt = "{ CALL VM_SEARCH_SMSC (?)}"; //calling stored procedure with single parameter.
$search='t'; //search string
$params = array($search);
$result = sqlsrv_query( $conn, $stmt,$params,array('Scrollable' => 'buffered')); //add Scrollable to get sqlsrv_num_rows.
echo $row_count = sqlsrv_num_rows( $result );
echo '<br>';

if( $result === false) {
    die( print_r( sqlsrv_errors(), true) );
} else{
while ($row = sqlsrv_fetch_array($result)) {
    // This never prints.
    echo "row:<br>"; var_dump($row); echo "<br><br>";
}
}
//print_r($row);
sqlsrv_free_stmt( $stmt);

This is working with sqlsrv.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.