NYCPHP Meetup

[Eric Gewirtz]

Hi - I'm having issues with getting a compiled query working with PEAR
using ->prepare when I have a DATE field in my Oracle 10g table. I have
gotten the 'prepare' to work on a table with fields other then type DATE
- so I know my 'connect', 'prepare' and 'execute' with the compiled
query works.

 

Any help would be much appreciated. I have tried numerous iterations of
using 'to_date' and the date mask. Below is my code that works without a
date field and it works fine

This is the oracle table it inserts into

CREATE TABLE CUSTMAST.TESTERIC (

  FNAME VARCHAR2(20) NOT NULL,

  LNAME VARCHAR2(25) NOT NULL,

  AGE NUMBER(5,0) NOT NULL

) tablespace "WADATA04";

 

 

<?php

require_once("c:/Inetpub/wwwroot/CustMast/includeav/classDefs.php");

define('DATE_END_FUTURE','9999-12-31 23:59:59');

define('DATE_MASK_ORA','yyyy-mm-dd hh24:mi:ss');

$dateEndFuture = "'".DATE_END_FUTURE."'";

$arrData = array('Eric', 'Gewirtz', 46);

 

$dbType = "oci8";

$db_UserName = "CUSTMAST";

$db_Password = "xxxxxx";

$db_HostName =
"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxx.xxxxxx.co
m)(PORT=1521)))(CONNECT_DATA=(SID=ORCL)(SERVER=DEDICATED)))";

$dsn = "$dbType://$db_UserName:$db_Password@$db_HostName";

 

$db = DB::connect($dsn);

if (DB::isError($db)) die($db->getMessage());

 

$sql = "INSERT INTO TESTERIC (FNAME,LNAME,AGE) VALUES (?,?,?)";

echo "The prepare sql is below \n";

echo "$sql \n";

$compiled = $db->prepare($sql);

if (DB::isError($compiled)) {

            echo "PREPARE FAILED \n";

            die($db->getMessage());

}

echo "data string is \n";

var_dump($arrData);

$result = $db->execute($compiled, $arrData);

if (DB::isError($result)) {

            echo "EXECUTE FAILED \n";

            die($db->getMessage());

}

?>

 

This code does not work - it is using the Oracle to_date in the VALUES
parms

This is the oracle table it inserts in to

CREATE TABLE CUSTMAST.TESTERIC (

  FNAME VARCHAR2(20) NOT NULL,

  LNAME VARCHAR2(25) NOT NULL,

  AGE NUMBER(5,0) NOT NULL,

  BDATE DATE Not NULL

) tablespace "WADATA04";

 

<?php

require_once("c:/Inetpub/wwwroot/CustMast/includeav/classDefs.php");

define('DATE_END_FUTURE','9999-12-31 23:59:59');

define('DATE_MASK_ORA','yyyy-mm-dd hh24:mi:ss');

$dateEndFuture = "'".DATE_END_FUTURE."'";

$arrData = array('Eric', 'Gewirtz', 46,$dateEndFuture);

 

$dbType = "oci8";

$db_UserName = "CUSTMAST";

$db_Password = "xxxxxxxxx";

$db_HostName =
"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxx.xxxxxx.co
m)(PORT=1521)))(CONNECT_DATA=(SID=ORCL)(SERVER=DEDICATED)))";

$dsn = "$dbType://$db_UserName:$db_Password@$db_HostName";

 

$db = DB::connect($dsn);

if (DB::isError($db)) die($db->getMessage());

 

$sql = "INSERT INTO TESTERIC (FNAME,LNAME,AGE,BDATE) VALUES
(?,?,?,to_date(?,'".DATE_MASK_ORA."')";

echo "The prepare sql is below \n";

echo "$sql \n";

$compiled = $db->prepare($sql);

if (DB::isError($compiled)) {

            echo "PREPARE FAILED \n";

            die($db->getMessage());

}

echo "data string is \n";

var_dump($arrData);

$result = $db->execute($compiled, $arrData);

if (DB::isError($result)) {

            echo "EXECUTE FAILED \n";

            die($db->getMessage());

}

?>

 

Here is the output of this program

The prepare sql is below 

INSERT INTO TESTERIC (FNAME,LNAME,AGE,BDATE) VALUES
(?,?,?,to_date(?,'yyyy-mm-dd hh24:mi:ss') 

data string is 

array(4) {

  [0]=>

  string(4) "Eric"

  [1]=>

  string(7) "Gewirtz"

  [2]=>

  int(46)

  [3]=>

  string(21) "'9999-12-31 23:59:59'"

}

EXECUTE FAILED

 

Eric Gewirtz

SolutionOne

Phone - 845-729-7800

Fax     - 845-279-5502

egewirtz at rcn.com

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20050211/5ee10b2f/attachment.html>