CODEWITHSUNDEEP
phpsqlodbcsqlanywhere
Iman Yasmin
Iman Yasmin

Reputation: 447

Retrieving Data of Stored Procedure Using ODBC and PHP

I'm trying to retrieve values of out parameter by using ODBC and PHP. I've been searching online but to no avail. There are a lot of resolves for PDO and mysqli and I only found this for odbc. I'm kind of lost by the parameter and the operation involve.

I'm able to make the connection to the database but the error always pops up. I couldn't figure them out.

[Sybase][ODBC Driver] Invalid string or buffer length

Any suggestion?

<?php

$conn=odbc_connect("dsn", " ", " ");

if (!$conn)
{
exit("Connection Failed: " . $conn);
}

$sql=odbc_prepare("CALL ndTblUser (@varUserId,@varUserPwd)"); 
$stmt=odbc_execute($sql, "SELECT @varUserId as UserId, @varUserPwd as UserPwd");

$rs=odbc_exec($conn,$stmt);

if (!$rs)
{
exit("Error : " . odbc_errormsg());
}
echo "<table><tr>";
echo "<th>Id</th>";
echo "<th>Password</th></tr>";

while (odbc_fetch_row($rs))
{
$UserId=odbc_result_all($rs,"UserId");
$UserPwd=odbc_result_all($rs,"UserPwd");
echo "<tr><td>$UserId</td>";
echo "<td>$UserPwd</td></tr>";
}

odbc_close($conn);
?>

Upvotes: 7

Views: 1972

Answers (2)

Iman Yasmin
Iman Yasmin

Reputation: 447

I've finally found the solution.

I've got a lot of misconception of stored procedure when come to retrieving the data for this question. Even the simplest one.

Instead of retrieving the data of the called parameters, the called parameter is used to call other result parameters. For example here, UserId and UserPwd is called to display the UserName and LoginStatus.

This is the correct code for retrieving the data of result parameter UserName and LoginStatus and based on a variable parameter of UserId and UserPwd.

<?php

$conn=odbc_connect("dsn", " ", " ");

if (!$conn)
{
  exit("Connection Failed : " . $conn);
}

$stmt=odbc_exec($conn,"CALL ndTblUser (".$_POST['UserId'].",'".$_POST['UserPwd']."')");

if (!$stmt)
{
  "Error : " . odbc_errormsg();
}

if (odbc_fetch_row($stmt))
{
  $UserName=odbc_result($stmt,"UserName");
  $LoginStatus=odbc_result($stmt,"LoginStatus");
}


if($LoginStatus==1)
{
  echo odbc_result($stmt,"UserName");
  echo odbc_result($stmt,"LoginStatus");
}

Upvotes: 1

user2620460
user2620460

Reputation: 188

Now I'm just about guessing, but if the $UserId and $UserPwd in the actual query is supposed to be columns, try to remove the $-sign from them. Later on in the code you're trying to get them as column names with out the $-sign, so I'm not sure if that's what's causing the error. They're not PHP variables until $UserId=odbc_result($rs,"UserId");.

Upvotes: 1

Related Questions