test.php

开源MARC数据处理

		
		/*
		Test out params - works in 4.2.3 but not 4.3.0???:
		
			CREATE PROCEDURE at_date_interval 
				@days INTEGER, 
				@start VARCHAR(20) OUT, 
				@end VARCHAR(20) OUT 	
			AS 
			BEGIN 
				set @start = CONVERT(VARCHAR(20), getdate(), 101) 
				set @end =CONVERT(VARCHAR(20), dateadd(day, @days, getdate()), 101 ) 
			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)) {
			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':
		$saved = $db->debug;
		$db->debug=true;
		
		print "<h4>Testing Foreign Keys</h4>";
		$arr = $db->MetaForeignKeys('emp');
		print_r($arr);
		if (!$arr) Err("Bad MetaForeignKeys");
		print "<h4>Testing Cursor Variables</h4>";
/*
-- TEST PACKAGE
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 data_out(input IN varchar, output OUT varchar); 
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 data_out(input IN varchar, output OUT varchar) IS
	BEGIN
		output := 'Cinta Hati '||input;
	END;
END adodb;
/
*/
		$rs = $db->ExecuteCursor("BEGIN adodb.open_tab(:RS,'A%'); END;");
	
		if ($rs && !$rs->EOF) {
			print "Test 1 RowCount: ".$rs->RecordCount()."<p>";
		} else {
			print "<b>Error in using Cursor Variables 1</b><p>";
		}
		
		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);
		
		$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->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($HTTP_GET_VARS['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 '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>";
	}
	$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()."</b></p>";
				$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($HTTP_GET_VARS['hide'])) rs2html($rs);
		}
		else print "<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>";

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

	print "<p>FetchObject/FetchNextObject Test</p>";
	$rs = &$db->Execute('select * from ADOXYZ');
	
	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;
		}
	}
	
	$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
	print "<p>FetchObject/FetchNextObject Test 2</p>";
	
	$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 ($rs->fields['id'] != 1)  {Err("Error 1"); print_r($rs->fields);};
		if (trim($rs->fields['firstname']) != 'Caroline')  {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");
		else if (is_array($rs->fields) || $rs->fields) {
			Err("Error: ## fields should be set to false on EOF");
			print_r($rs->fields);
		}
	}
	
	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
	print "<p>FETCH_MODE = NUM: Should get 1, Caroline</p>";
	$rs = &$db->SelectLimit('select id,firstname from ADOXYZ order by id',1);
	if ($rs && !$rs->EOF) {
		if (isset($rs->fields['id'])) Err("FETCH_NUM has ASSOC fields");
		if ($rs->fields[0] != 1)  {Err("Error 1"); print_r($rs->fields);};
		if (trim($rs->fields[1]) != 'Caroline')  {Err("Error 2");print_r($rs->fields);};
		$rs->MoveNext();
		if (!$rs->EOF) Err("Error EOF");

	}
	$ADODB_FETCH_MODE = $savefetch;
	
	$db->debug = false;
	print "<p>GetRowAssoc Upper: Should get 1, Caroline</p>";
	$rs = &$db->SelectLimit('select id,firstname from ADOXYZ order by id',1);
	if ($rs && !$rs->EOF) {
		$arr = &$rs->GetRowAssoc();
		if ($arr['ID'] != 1) {Err("Error 1");print_r($arr);};
		if (trim($arr['FIRSTNAME']) != 'Caroline') {Err("Error 2"); print_r($arr);};
		$rs->MoveNext();
		if (!$rs->EOF) Err("Error EOF");

	}
	print "<p>GetRowAssoc Lower: Should get 1, Caroline</p>";
	$rs = &$db->SelectLimit('select id,firstname from ADOXYZ order by id',1);
	if ($rs && !$rs->EOF) {
		$arr = &$rs->GetRowAssoc(false);
		if ($arr['id'] != 1) {Err("Error 1"); print_r($arr);};
		if (trim($arr['firstname']) != 'Caroline') {Err("Error 2"); print_r($arr);};

	}
	
	print "<p>GetCol Test</p>";
	$col = $db->GetCol('select distinct firstname from adoxyz order by 1');
	if (!is_array($col)) Err("Col size is wrong");
	if (trim($col[0]) != 'Alan' or trim($col[9]) != 'Yat Sun') Err("Col elements wrong");

	$db->debug = true;
	print "<p>SelectLimit Distinct Test 1: Should see Caroline, John and Mary</p>";
	$rs = &$db->SelectLimit('select distinct * from ADOXYZ order by id',3);
	$db->debug=false;

	if ($rs && !$rs->EOF) {
		if (trim($rs->fields[1]) != 'Caroline') Err("Error 1");
		$rs->MoveNext();
		if (trim($rs->fields[1]) != 'John') Err("Error 2");
		$rs->MoveNext();
		if (trim($rs->fields[1]) != 'Mary') Err("Error 3");
		$rs->MoveNext();