test.php

Typo3, 开源里边最强大的

			END
			GO
		*/
		$db->debug=1;
		$stmt = $db->PrepareSP('at_date_interval');
		$days = 10;
		$begin_date = '';
		$end_date = '';
		$db->InParameter($stmt,$days,'days', 4, SQLINT4); 
		$db->OutParameter($stmt,$begin_date,'start', 20, SQLVARCHAR ); 
		$db->OutParameter($stmt,$end_date,'end', 20, SQLVARCHAR ); 
		$db->Execute($stmt);
		if (empty($begin_date) or empty($end_date) or $begin_date == $end_date) {
			Err("MSSQL SP Test for OUT Failed");
			print "begin=$begin_date end=$end_date<p>";
		} else print "(Today +10days) = (begin=$begin_date end=$end_date)<p>";
	
		$db->debug = $saved;
		break;
	case 'oci8': 
	case 'oci8po':
		
		if (0) {
		$t = getmicrotime();
		$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
		$arr = $db->GetArray('select * from abalone_tree');
		$arr = $db->GetArray('select * from abalone_tree');
		$arr = $db->GetArray('select * from abalone_tree');
		echo "<p>t = ",getmicrotime() - $t,"</p>";
		die();
		}
		
		# cleanup
		$db->Execute("delete from photos where id=99 or id=1");
		$db->Execute("insert into photos (id) values(1)");
		$db->Execute("update photos set photo=null,descclob=null where id=1");
		
		$saved = $db->debug;
		$db->debug=true;
		
		

		/*
		CREATE TABLE PHOTOS
		(
		  ID           NUMBER(16),
		  PHOTO        BLOB,
		  DESCRIPTION  VARCHAR2(4000 BYTE),
		  DESCCLOB     CLOB
		);
		
		INSERT INTO PHOTOS (ID) VALUES(1);
		*/
		$s = '';
		for ($i = 0; $i <= 500; $i++) {
			$s .= '1234567890';
		}
		
		$sql = "INSERT INTO photos ( ID, photo) ".
			"VALUES ( :id, empty_blob() )".
			" RETURNING photo INTO :xx";

		
		$blob_data = $s;
		$id = 99;
		
 		$stmt = $db->PrepareSP($sql);
		$db->InParameter($stmt, $id, 'id');
		$blob = $db->InParameter($stmt, $s, 'xx',-1, OCI_B_BLOB);
		$db->StartTrans();
		$result = $db->Execute($stmt);
		$db->CompleteTrans();
		
		$s2= $db->GetOne("select photo from photos where id=99");
		echo "<br>---$s2";
		if ($s !== $s2) Err("insert blob does not match");

		print "<h4>Testing Blob: size=".strlen($s)."</h4>";
		$ok = $db->Updateblob('photos','photo',$s,'id=1');
		if (!$ok) Err("Blob failed 1");
		else {
			$s2= $db->GetOne("select photo from photos where id=1");
			if ($s !== $s2) Err("updateblob does not match");
		}
		
		print "<h4>Testing Clob: size=".strlen($s)."</h4>";
		$ok = $db->UpdateClob('photos','descclob',$s,'id=1');
		if (!$ok) Err("Clob failed 1");
		else {
			$s2= $db->GetOne("select descclob from photos where id=1");
			if ($s !== $s2) Err("updateclob does not match");
		}
		
		
		$s = '';
		$s2 = '';
		print "<h4>Testing Foreign Keys</h4>";
		$arr = $db->MetaForeignKeys('emp','scott');
		print_r($arr);
		if (!$arr) Err("Bad MetaForeignKeys");
/*
-- TEST PACKAGE
-- "Set scan off" turns off substitution variables. 
Set scan off; 

CREATE OR REPLACE PACKAGE Adodb AS
TYPE TabType IS REF CURSOR RETURN TAB%ROWTYPE;
PROCEDURE open_tab (tabcursor IN OUT TabType,tablenames IN VARCHAR);
PROCEDURE open_tab2 (tabcursor IN OUT TabType,tablenames IN OUT VARCHAR) ;
PROCEDURE data_out(input IN VARCHAR, output OUT VARCHAR);
PROCEDURE data_in(input IN VARCHAR);
PROCEDURE myproc (p1 IN NUMBER, p2 OUT NUMBER);
END Adodb;
/


CREATE OR REPLACE PACKAGE BODY Adodb AS
PROCEDURE open_tab (tabcursor IN OUT TabType,tablenames IN VARCHAR) IS
	BEGIN
		OPEN tabcursor FOR SELECT * FROM TAB WHERE tname LIKE tablenames;
	END open_tab;

	PROCEDURE open_tab2 (tabcursor IN OUT TabType,tablenames IN OUT VARCHAR) IS
	BEGIN
		OPEN tabcursor FOR SELECT * FROM TAB WHERE tname LIKE tablenames;
		tablenames := 'TEST';
	END open_tab2;

PROCEDURE data_out(input IN VARCHAR, output OUT VARCHAR) IS
	BEGIN
		output := 'Cinta Hati '||input;
	END;
	
PROCEDURE data_in(input IN VARCHAR) IS
	ignore varchar(1000);
	BEGIN
		ignore := input;
	END;

PROCEDURE myproc (p1 IN NUMBER, p2 OUT NUMBER) AS
BEGIN
p2 := p1;
END;
END Adodb;
/

*/

		print "<h4>Testing Cursor Variables</h4>";
		$rs = $db->ExecuteCursor("BEGIN adodb.open_tab(:zz,'A%'); END;",'zz');
	
		if ($rs && !$rs->EOF) {
			$v = $db->GetOne("SELECT count(*) FROM tab where tname like 'A%'");
			if ($v == $rs->RecordCount()) print "Test 1 RowCount: OK<p>";
			else Err("Test 1 RowCount ".$rs->RecordCount().", actual = $v");
		} else {
			print "<b>Error in using Cursor Variables 1</b><p>";
		}
		$rs->Close();
		
		print "<h4>Testing Stored Procedures for oci8</h4>";
		
		$stmt = $db->PrepareSP("BEGIN adodb.data_out(:a1, :a2); END;");
		$a1 = 'Malaysia';
		//$a2 = ''; # a2 doesn't even need to be defined!
		$db->InParameter($stmt,$a1,'a1');
		$db->OutParameter($stmt,$a2,'a2');
		$rs = $db->Execute($stmt);
		if ($rs) {
			if ($a2 !== 'Cinta Hati Malaysia') print "<b>Stored Procedure Error: a2 = $a2</b><p>";
			else echo  "OK: a2=$a2<p>";
		} else {
			print "<b>Error in using Stored Procedure IN/Out Variables</b><p>";
		}
		
		$tname = 'A%';
		
		$stmt = $db->PrepareSP('select * from tab where tname like :tablename');
		$db->Parameter($stmt,$tname,'tablename');
		$rs = $db->Execute($stmt);
		rs2html($rs);
		
		$stmt = $db->PrepareSP("begin adodb.data_in(:a1); end;");
		$db->InParameter($stmt,$a1,'a1');
		$db->Execute($stmt);
		
		$db->debug = $saved;
		break;
	
	default:
		break;
	}
	$arr = array(
		array(1,'Caroline','Miranda'),
		array(2,'John','Lim'),
		array(3,'Wai Hun','See')
	);
	//$db->debug=1;
	print "<p>Testing Bulk Insert of 3 rows</p>";

	$sql = "insert into ADOXYZ (id,firstname,lastname) values (".$db->Param('0').",".$db->Param('1').",".$db->Param('2').")";
	$db->StartTrans();
	$db->Execute($sql,$arr);
	$db->CompleteTrans();
	$rs = $db->Execute('select * from ADOXYZ order by id');
	if (!$rs || $rs->RecordCount() != 3) Err("Bad bulk insert");
	
	rs2html($rs);
	
	$db->Execute('delete from ADOXYZ');
		
	print "<p>Inserting 50 rows</p>";

	for ($i = 0; $i < 5; $i++) {	

	$time = $db->DBDate(time());
	if (empty($_GET['hide'])) $db->debug = true;
	switch($db->databaseType){
	case 'mssqlpo':
	case 'mssql':
		$sqlt = "CREATE TABLE mytable (
  row1 INT  IDENTITY(1,1) NOT NULL,
  row2 varchar(16),
  PRIMARY KEY  (row1))";
  		//$db->debug=1;
  		if (!$db->Execute("delete from mytable")) 
			$db->Execute($sqlt);
			
		$ok = $db->Execute("insert into mytable (row2) values ('test')");
		$ins_id=$db->Insert_ID();
		echo "Insert ID=";var_dump($ins_id);
		if ($ins_id == 0) Err("Bad Insert_ID()");
		$ins_id2 = $db->GetOne("select row1 from mytable");
		if ($ins_id != $ins_id2) Err("Bad Insert_ID() 2");
		
		$arr = array(0=>'Caroline',1=>'Miranda');
		$sql = "insert into ADOXYZ (id,firstname,lastname,created) values ($i*10+0,?,?,$time)";
		break;
	case 'mysqli':
	case 'mysqlt':
	case 'mysql':
		$sqlt = "CREATE TABLE `mytable` (
  `row1` int(11) NOT NULL auto_increment,
  `row2` varchar(16) NOT NULL default '',
  PRIMARY KEY  (`row1`),
  KEY `myindex` (`row1`,`row2`)
) ";
		if (!$db->Execute("delete from mytable")) 
			$db->Execute($sqlt);
			
		$ok = $db->Execute("insert into mytable (row2) values ('test')");
		$ins_id=$db->Insert_ID();
		echo "Insert ID=";var_dump($ins_id);
		if ($ins_id == 0) Err("Bad Insert_ID()");
		$ins_id2 = $db->GetOne("select row1 from mytable");
		if ($ins_id != $ins_id2) Err("Bad Insert_ID() 2");
		
	default:
		$arr = array(0=>'Caroline',1=>'Miranda');
		$sql = "insert into ADOXYZ (id,firstname,lastname,created) values ($i*10+0,?,?,$time)";
		break;
	
	case 'oci8':
	case 'oci805':
		$arr = array('first'=>'Caroline','last'=>'Miranda');
		$amt = rand() % 100;
		$sql = "insert into ADOXYZ (id,firstname,lastname,created) values ($i*10+0,:first,:last,$time)";		
		break;
	}
	if ($i & 1) {
		$sql = $db->Prepare($sql);
	}
	$rs = $db->Execute($sql,$arr);
		
	if ($rs === false) Err( 'Error inserting with parameters');
	else $rs->Close();
	$db->debug = false;
	$db->Execute("insert into ADOXYZ (id,firstname,lastname,created) values ($i*10+1,'John','Lim',$time)");
	/*$ins_id=$db->Insert_ID();
	echo "Insert ID=";var_dump($ins_id);*/
	if ($db->databaseType == 'mysql') if ($ins_id == 0) Err('Bad Insert_ID');
	$db->Execute("insert into ADOXYZ (id,firstname,lastname,created) values ($i*10+2,'Mary','Lamb',$time )");
	$db->Execute("insert into ADOXYZ (id,firstname,lastname,created) values ($i*10+3,'George','Washington',$time )");
	$db->Execute("insert into ADOXYZ (id,firstname,lastname,created) values ($i*10+4,'Mr. Alan','Tam',$time )");
	$db->Execute("insert into ADOXYZ (id,firstname,lastname,created) values ($i*10+5,'Alan',".$db->quote("Turing'ton").",$time )");
	$db->Execute("insert into ADOXYZ (id,firstname,lastname,created)values ($i*10+6,'Serena','Williams',$time )");
	$db->Execute("insert into ADOXYZ (id,firstname,lastname,created) values ($i*10+7,'Yat Sun','Sun',$time )");
	$db->Execute("insert into ADOXYZ (id,firstname,lastname,created) values ($i*10+8,'Wai Hun','See',$time )");
	$db->Execute("insert into ADOXYZ (id,firstname,lastname,created) values ($i*10+9,'Steven','Oey',$time )");
	} // for
	if (1) {
	$db->debug=1;
	$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
	$cnt = $db->GetOne("select count(*) from ADOXYZ");
	$rs = $db->Execute('update ADOXYZ set id=id+1');	
	if (!is_object($rs)) {
		print_r($rs);
		err("Update should return object");
	} 
	if (!$rs) err("Update generated error");
	
	$nrows = $db->Affected_Rows();   
	if ($nrows === false) print "<p><b>Affected_Rows() not supported</b></p>";
	else if ($nrows != $cnt)  print "<p><b>Affected_Rows() Error: $nrows returned (should be 50) </b></p>";
	else print "<p>Affected_Rows() passed</p>";
	}
	
	if ($db->dataProvider == 'oci8')  $array = array('zid'=>1,'zdate'=>date('Y-m-d',time()));
	else $array=array(1,date('Y-m-d',time()));
	
	
	#$array = array(1,date('Y-m-d',time()));
	$id = $db->GetOne("select id from ADOXYZ 
		where id=".$db->Param('zid')." and created>=".$db->Param('ZDATE')."",
		$array);
	if ($id != 1) Err("Bad bind; id=$id");
	else echo "<br>Bind date/integer 1 passed";
	
	$array =array(1,$db->BindDate(time()));
	$id = $db->GetOne("select id from ADOXYZ 
		where id=".$db->Param('0')." and created>=".$db->Param('1')."",
		$array);
	if ($id != 1) Err("Bad bind; id=$id");
	else echo "<br>Bind date/integer 2 passed";
	
	$db->debug = false;
	$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
 //////////////////////////////////////////////////////////////////////////////////////////
	
	$rs = $db->Execute("select * from ADOXYZ where firstname = 'not known'");
	if (!$rs ||  !$rs->EOF) print "<p><b>Error on empty recordset</b></p>";
	else if ($rs->RecordCount() != 0) {
		print "<p><b>Error on RecordCount. Should be 0. Was ".$rs->RecordCount()."</b></p>"; 
		print_r($rs->fields);
	}
	if ($db->databaseType !== 'odbc') {
		$rs = &$db->Execute("select id,firstname,lastname,created,".$db->random." from ADOXYZ order by id");
		if ($rs) {
			if ($rs->RecordCount() != 50) {
				print "<p><b>RecordCount returns ".$rs->RecordCount().", should be 50</b></p>";
				adodb_pr($rs->GetArray());
				$poc = $rs->PO_RecordCount('ADOXYZ');
				if ($poc == 50) print "<p> &nbsp; &nbsp; PO_RecordCount passed</p>";
				else print "<p><b>PO_RecordCount returns wrong value: $poc</b></p>";
			} else print "<p>RecordCount() passed</p>";
			if (isset($rs->fields['firstname'])) print '<p>The fields columns can be indexed by column name.</p>';
			else {
				Err( '<p>The fields columns <i>cannot</i> be indexed by column name.</p>');
				print_r($rs->fields);
			}
			if (empty($_GET['hide'])) rs2html($rs);
		}
		else print "<p><b>Error in Execute of SELECT with random</b></p>";
	}
	$val = $db->GetOne("select count(*) from ADOXYZ");
	 if ($val == 50) print "<p>GetOne returns ok</p>";
	 else print "<p><b>Fail: GetOne returns $val</b></p>";

	 echo "<b>GetRow Test</b>";
	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
	$val1 = $db->GetRow("select count(*) from ADOXYZ");
	$val2 =& $db->GetRow("select count(*) from ADOXYZ");
	 if ($val1[0] == 50 and sizeof($val1) == 1 and $val2[0] == 50 and sizeof($val2) == 1) print "<p>GetRow returns ok</p>";
	 else {
	 	print_r($val);
	 	print "<p><b>Fail: GetRow returns {$val2[0]}</b></p>";
	}

	print "<p>FetchObject/FetchNextObject Test</p>";
	$rs = $db->Execute('select * from ADOXYZ');
	if ($rs) {
		if (empty($rs->connection)) print "<b>Connection object missing from recordset</b></br>";
		
		while ($o = $rs->FetchNextObject()) { // calls FetchObject internally
			if (!is_string($o->FIRSTNAME) || !is_string($o->LASTNAME)) {
				print_r($o);
				print "<p><b>Firstname is not string</b></p>";
				break;
			}
		}
	} else {
		print "<p><b>Failed rs</b></p>";
		die("<p>ADOXYZ table cannot be read - die()");
	}
	$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
	print "<p>FetchObject/FetchNextObject Test 2</p>";
	#$db->debug=99;
	$rs = $db->Execute('select * from ADOXYZ');
	if (empty($rs->connection)) print "<b>Connection object missing from recordset</b></br>";
	print_r($rs->fields);
	while ($o = $rs->FetchNextObject()) { // calls FetchObject internally
		if (!is_string($o->FIRSTNAME) || !is_string($o->LASTNAME)) {
			print_r($o);
			print "<p><b>Firstname is not string</b></p>";
			break;
		}
	}
	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
	
	$savefetch = $ADODB_FETCH_MODE;
	$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
	
	print "<p>CacheSelectLimit  Test...</p>";
	$db->debug=1;
	$rs = $db->CacheSelectLimit('select  id, firstname from  ADOXYZ order by id',2);
	
	if ($rs && !$rs->EOF) {
		if (isset($rs->fields[0])) {
			Err("ASSOC has numeric fields");
			print_r($rs->fields);
		}
		if ($rs->fields['id'] != 1)  {Err("Error"); print_r($rs->fields);};
		if (trim($rs->fields['firstname']) != 'Caroline')  {print Err("Error 2"); print_r($rs->fields);};
		
		$rs->MoveNext();
		if ($rs->fields['id'] != 2)  {Err("Error 3"); print_r($rs->fields);};
		$rs->MoveNext();
		if (!$rs->EOF) {
			Err("Error EOF");
			print_r($rs);
		}
	}
	
	print "<p>FETCH_MODE = ASSOC: Should get 1, Caroline</p>";
	$rs = &$db->SelectLimit('select id,firstname from ADOXYZ order by id',2);
	if ($rs && !$rs->EOF) {
		if (ADODB_ASSOC_CASE == 2) {
			$id = 'ID';
			$fname = 'FIRSTNAME';
		}else {
			$id = 'id';
			$fname = 'firstname';
		}
		if ($rs->fields[$id] != 1)  {Err("Error 1"); print_r($rs->fields);};
		if (trim($rs->fields[$fname]) != 'Caroline')  {Err("Error 2"); print_r($rs->fields);};