I am trying to do a simple select on a db2 table using php and unixodbc, but get the following error:
Warning: odbc_exec(): SQL error: [unixODBC][IBM][CLI Driver][DB2/LINUX] SQL0204N "INET.INSUR_SURVEY" is an undefined name. SQLSTATE=42704 , SQL state S0002 in SQLExecDirect in /home/prgjr1/public_html/ra-apps/inc/db2conn.php on line 123
Here is my code:
Code:
$DB2= new mydb2;
$sql="SELECT survey_nme, to_char(begin_dte, 'mm/dd/yyyy') MYBEGINDTE,
to_char(end_dte, 'mm/dd/yyyy') MYENDDTE, status_ind, email_id
from inet.insur_survey";
$DB2->my_sel($sql);
I have a db2conn.php file that has all the functions to connect to db2, here is the code I have for that:
Code:
if (!defined($inc_db2conn))
{
$inc_db2conn=1;
class mydb2
{ //CLASS DEFN. for mydb to make selects easy...and quiet
// USAGE: (1)
// include ("db2conn.php");
// $conn = new mydb;
// $conn->my_conn();
// $sel_stmt = "Select something from somewhere...";
// $conn->my_stmt($sel_stmt);
// for ($i=0;$i < $conn->numrows;$i++)
// {
// print $conn->results["SOMECOLUMNNAME1"][$i];
// print ", ";
// print $conn->results["SOMECOLUMNNAME2"][$i];
// }
// $conn->my_close();
// (2)
// include ("db2conn.php");
// $conn = new mydb;
// $sel_stmt = "Select something from somewhere...";
// $conn->my_sel($sel_stmt);
// for (.....refer to above)
//
// **note no my_close is needed b/c my_sel does it all.
var $c; // new connection to mysql db
var $sqlstmt; //database object created as result of query
var $querystr; //string containing query passed in
var $results; //Results array resulting from any query
var $res; //Results resource returned by executing odbc query
var $numrows; //Number of rows affected from query
var $row; //Row Object containg column values of query
var $i; //Current row pointer
var $dsn; //Value containing alias to db2 subsystem db2test, db2car, db2prod
var $user; //User id used to authenticate to db2
var $password; //Password for said user
var $ORA_ERR; //odbc error message number
var $ORA_ERR_DESC; //odbc error mesage
#function mydb2($db, $uid, $pwd)
#{
# global $my_db_userid, $my_db_passwd, $my_db_instance;
# this->dsn = $my_db_instance;
# $this->user = $my_db_userid;
# $this->password = $my_db_passwd;
#}
function _handle_connection_failure()
{
global $SCRIPT_FILENAME,
$SERVER_ADDR,
$SERVER_NAME,
$SCRIPT_NAME;
$date=date("Y-m-d H:i:s");
$offensive_statement=preg_replace("'[\r\n\s]+'", " ", $this->querystr);
$oracle_error=preg_replace("'[\r\n\s]+'", " ", odbc_errormsg());
print("<pre>
Hi there. My name is '$SERVER_NAME' at '$SERVER_ADDR'.
The date is $date.
I'm writing you today because I've found a Database error in the web page
'$SCRIPT_NAME', in the file '$file', specifically on line '$line'.
As it turns out, the offensive statement was as follows...
'$offensive_statement'
... and the error that the Database gave me was ...
'$oracle_error'.
Hey, though, you have a FANTASTIC day!!
</pre>");
exit;
}
function my_err($stmt)
{
$this->ORA_ERR = odbc_error();
$this->ORA_ERR_DESC = "SQL: (" . $stmt . ") ERR: " . odbc_errormsg();
}
function my_conn()
{ //Create database connection using bg-id
global $my_db2_userid, $my_db2_passwd, $my_db2_instance;
echo "instance: " . $my_db2_instance . "<br>userid: " . $my_db2_userid . "<br>password: " . $my_db2_passwd . "<br>";
error_reporting(E_ERROR | E_WARNING | E_PARSE);
$this->i = 0;
$this->c = odbc_connect("$my_db2_instance","$my_db2_userid","$my_db2_passwd") or
$this->_handle_connection_failure();
error_reporting(E_ERROR | E_WARNING | E_PARSE);
}
function my_upd($stmt)
{ //Execute passed in SQL and Return $results
$this->querystr = $stmt;
error_reporting(E_ERROR | E_WARNING | E_PARSE);
$this->my_conn();
$this->res = odbc_prepare($this->c, $stmt) or die (odbc_errormsg());
$rc = odbc_execute($this->res) or die (odbc_errormsg());
$this->my_err($stmt);
$this->numrows = odbc_num_rows($this->res);
if ( eregi('insert', $stmt))
{
if (( $this->numrows == 0 ) && ( $this->ORA_ERR == 1 ))
{
print "Insert failed! Try an UPDATE! \n";
}
}
$this->my_close();
}
function my_sel($stmt)
{ //Execute passed in SQL and Return $results
$this->querystr = $stmt;
error_reporting(E_ERROR | E_WARNING | E_PARSE);
$this->my_conn();
print ("<br>SQL_EXEC: (" . $stmt . ")");
$this->res = odbc_exec($this->c, $stmt);
$this->my_err($stmt);
if (!$this->res) $this->_handle_connection_failure();
$this->make_results($this->res);
$this->my_close();
error_reporting(E_ERROR | E_WARNING | E_PARSE);
}
function make_results($res)
{//Make an associative array of the results and Return $results
error_reporting(E_ERROR | E_WARNING | E_PARSE);
$i = 0;
$j = 0;
unset($temp_fieldnames);
while(odbc_fetch_row($this->res))
{
//Build tempory
for ($j = 1; $j <= odbc_num_fields($this->res); $j++)
{
$field_name = odbc_field_name($this->res, $j);
//*debug* print ("<br>Making Results: Field name: (" . $field_name . ")");
$temp_fieldnames[$j] = $field_name;
$this->results[$field_name][$i] = odbc_result($this->res, $field_name);
//*debug* print ("<br>Making Results: Field Value (" . $this->results[$field_name][$i] . ")");
}
//$this->results[$i] = $ar;
$i++;
}
$this->numrows = $i;
}
function my_sqlbnd()
{ //Execute bind and sqlstatement, dynamic args. (SQL, VARNAME, VARVALUE, ...)
error_reporting(E_ERROR | E_WARNING | E_PARSE);
$this->my_conn();
$stmt = func_get_arg (0);
#$numargs = func_num_args();
$strarg = func_get_arg(1);
$args = split(DELIM, $strarg);
$numargs = count($args);
$z = 0;
//*debug* print "Array count is: " . $numargs;
if ((($numargs) % 2) <> 0)
{
print "Sorry, invalid # arguments: " . $numargs . "\n";
}
else
{
//Find all occurences of Host Variables (whole words starting with ":" and ending on a word boundry) in the sql stmt
preg_match_all("/:[A-Za-z0-9_]+?\b/", $stmt, $bindarray);
//*debug* print_r ($bindarray[0]);
//Replace all occurences of host variables in string with ODBC compliant "?"'s
$stmt = str_replace($bindarray[0], "?", $stmt);
//*debug* print ("<br>SQL: (" . $stmt . ")");
//Complete foreach loop to create array that can be passed to odbc_execute as values for ?'s
$hostvar_val_arr = array();
foreach ($bindarray[0] as $hostvar) {
//*debug* print("<br>Looking for hostvar (" . $hostvar . ")");
$arr_key = array_search(substr($hostvar, 1), $args);
//*debug* print ("<br>array key: (" . $arr_key . ")");
array_push($hostvar_val_arr, $args[$arr_key + 1]);
}
//*debug* print ("<br><br>HOSTVAR: --->");
//*debug* print_r ($hostvar_val_arr);
//Prepare and execute the newly formed SQL and value array
$this->res = odbc_prepare($this->c, $stmt) or die ("PREPARE: odbc error: " . odbc_errormsg());
$rc = odbc_execute($this->res, $hostvar_val_arr ) or die ("EXECUTE: odbc error: " .odbc_errormsg());
$this->my_err($stmt);
$this->numrows = odbc_num_rows($this->res);
$this->my_close();
} // end if not enough ARGS
error_reporting(E_ERROR | E_WARNING | E_PARSE);
}
function my_stmt($stmt)
{ //Execute passed in SQL and Return $results
$this->querystr = $stmt;
error_reporting(E_ERROR | E_WARNING | E_PARSE);
$this->my_conn();
echo "calling odbc_exec($this->c, $stmt)";
$this->res = @odbc_exec($this->c, $stmt) or die(odbc_errormsg());
echo "yipee, we got past the execute statement";
$this->numrows = odbc_num_rows ($this->res);
$this->make_results($this->res);
$this->my_close();
}
function my_close()
{ //Close mysql connection
error_reporting(E_ERROR | E_WARNING | E_PARSE);
odbc_free_result($this->res);
odbc_Close($this->c);
}
}//end of class
} //end of ifdef statement
Could someone please help me? Eventually I'll need to call a stored procedure to do this, so if anyone could help me with that too, that would be great!!!
05-19-2006, 01:03 PM
Linear Mode
