Context Navigation

source: trunk/phpgwapi/inc/adodb/tests/test.php @ 2

Revision 2, 49.5 KB checked in by niltonneto, 17 years ago (diff)
Removida todas as tags usadas pelo CVS ($Id, $Source). Primeira versão no CVS externo.
Property svn:eol-style set to `native` Property svn:executable set to ``*

Line
1	<?php
2	/*
3	V4.51 29 July 2004 (c) 2000-2004 John Lim (jlim@natsoft.com.my). All rights reserved.
4	Released under both BSD license and Lesser GPL library license.
5	Whenever there is any discrepancy between the two licenses,
6	the BSD license will take precedence.
7	Set tabs to 4 for best viewing.
8
9	Latest version is available at http://adodb.sourceforge.net
10	*/
11
12	error_reporting(E_ALL);
13
14	$ADODB_FLUSH = true;
15
16	define('ADODB_ASSOC_CASE',0);
17
18	if (PHP_VERSION < 5) include_once('../adodb-pear.inc.php');
19	//--------------------------------------------------------------------------------------
20	//define('ADODB_ASSOC_CASE',1);
21	//
22	function Err($msg)
23	{
24	print "<b>$msg</b><br>";
25	flush();
26	}
27
28	function CheckWS($conn)
29	{
30	global $ADODB_EXTENSION;
31
32	include_once('../session/adodb-session.php');
33	if (defined('CHECKWSFAIL')){ echo " TESTING $conn ";flush();}
34	$saved = $ADODB_EXTENSION;
35	$db = ADONewConnection($conn);
36	$ADODB_EXTENSION = $saved;
37	if (headers_sent()) {
38	print "<p><b>White space detected in adodb-$conn.inc.php or include file...</b></p>";
39	//die();
40	}
41	}
42
43	function do_strtolower(&$arr)
44	{
45	foreach($arr as $k => $v) {
46	$arr[$k] = strtolower($v);
47	}
48	}
49
50
51	function CountExecs($db, $sql, $inputarray)
52	{
53	global $EXECS; $EXECS++;
54	}
55
56	function CountCachedExecs($db, $secs2cache, $sql, $inputarray)
57	{
58	global $CACHED; $CACHED++;
59	}
60
61	// the table creation code is specific to the database, so we allow the user
62	// to define their own table creation stuff
63
64	function testdb(&$db,$createtab="create table ADOXYZ (id int, firstname char(24), lastname char(24), created date)")
65	{
66	GLOBAL $ADODB_vers,$ADODB_CACHE_DIR,$ADODB_FETCH_MODE, $HTTP_GET_VARS,$ADODB_COUNTRECS;
67
68	?> <form method=GET>
69	</p>
70	<table width=100% ><tr><td bgcolor=beige> </td></tr></table>
71	</p>
72	<?php
73	$create =false;
74	/*$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
75
76	$rs = $db->Execute('select lastname,firstname,lastname,id from adoxyz');
77	$arr = $rs->GetAssoc();
78	echo "<pre>";print_r($arr);
79	die();*/
80
81	GLOBAL $EXECS, $CACHED;
82
83	$EXECS = 0;
84	$CACHED = 0;
85	//$db->Execute("drop table adodb_logsql");
86	if ((rand()%3) == 0) @$db->Execute("delete from adodb_logsql");
87	$db->debug=1;
88
89	$db->fnExecute = 'CountExecs';
90	$db->fnCacheExecute = 'CountCachedExecs';
91
92	if (empty($_GET['nolog'])) {
93	echo "<h3>SQL Logging enabled</h3>";
94	$db->LogSQL();/*
95	$sql =
96	"SELECT t1.sid, t1.sid, t1.title, t1.hometext, t1.notes, t1.aid, t1.informant,
97	t2.url, t2.email, t1.catid, t3.title, t1.topic, t4.topicname, t4.topicimage,
98	t4.topictext, t1.score, t1.ratings, t1.counter, t1.comments, t1.acomm
99	FROM `nuke_stories` `t1`, `nuke_authors` `t2`, `nuke_stories_cat` `t3`, `nuke_topics` `t4`
100	WHERE ((t2.aid=t1.aid) AND (t3.catid=t1.catid) AND (t4.topicid=t1.topic)
101	AND ((t1.alanguage='german') OR (t1.alanguage='')) AND (t1.ihome='0'))
102	ORDER BY t1.time DESC";
103	$db->SelectLimit($sql);
104	echo $db->ErrorMsg();*/
105	}
106	$ADODB_CACHE_DIR = dirname(TempNam('/tmp','testadodb'));
107	$db->debug = false;
108	//print $db->UnixTimeStamp('2003-7-22 23:00:00');
109
110	$phpv = phpversion();
111	if (defined('ADODB_EXTENSION')) $ext = '   Extension '.ADODB_EXTENSION.' installed';
112	else $ext = '';
113	print "<h3>ADODB Version: $ADODB_vers Host: <i>$db->host</i>   Database: <i>$db->database</i>   PHP: $phpv $ext</h3>";
114	flush();
115	$arr = $db->ServerInfo();
116	print_r($arr);
117	echo "<br>";
118	$e = error_reporting(E_ALL-E_WARNING);
119	flush();
120
121	$tt = $db->Time();
122	if ($tt == 0) echo '<br><b>$db->Time failed</b>';
123	else echo "<br>db->Time: ".date('d-m-Y H:i:s',$tt);
124	echo '<br>';
125
126	echo "Date=",$db->UserDate('2002-04-07'),'<br>';
127	print "<i>date1</i> (1969-02-20) = ".$db->DBDate('1969-2-20');
128	print "<br><i>date1</i> (1999-02-20) = ".$db->DBDate('1999-2-20');
129	print "<br><i>date1.1</i> 1999 = ".$db->DBDate("'1999'");
130	print "<br><i>date2</i> (1970-1-2) = ".$db->DBDate(24*3600)."<p>";
131	print "<i>ts1</i> (1999-02-20 13:40:50) = ".$db->DBTimeStamp('1999-2-20 1:40:50 pm');
132	print "<br><i>ts1.1</i> (1999-02-20 13:40:00) = ".$db->DBTimeStamp('1999-2-20 13:40');
133	print "<br><i>ts2</i> (1999-02-20) = ".$db->DBTimeStamp('1999-2-20');
134	print "<br><i>ts3</i> (1970-1-2 +/- timezone) = ".$db->DBTimeStamp(24*3600);
135	print "<br> Fractional TS (1999-2-20 13:40:50.91): ".$db->DBTimeStamp($db->UnixTimeStamp('1999-2-20 13:40:50.91+1'));
136	$dd = $db->UnixDate('1999-02-20');
137	print "<br>unixdate</i> 1999-02-20 = ".date('Y-m-d',$dd)."<p>";
138	print "<br><i>ts4</i> =".($db->UnixTimeStamp("19700101000101")+8*3600);
139	print "<br><i>ts5</i> =".$db->DBTimeStamp($db->UnixTimeStamp("20040110092123"));
140	print "<br><i>ts6</i> =".$db->UserTimeStamp("20040110092123");
141	print "<br><i>ts7</i> =".$db->DBTimeStamp("20040110092123");
142	flush();
143	// mssql too slow in failing bad connection
144	if (false && $db->databaseType != 'mssql') {
145	print "<p>Testing bad connection. Ignore following error msgs:<br>";
146	$db2 = ADONewConnection();
147	$rez = $db2->Connect("bad connection");
148	$err = $db2->ErrorMsg();
149	print "<i>Error='$err'</i></p>";
150	if ($rez) print "<b>Cannot check if connection failed.</b> The Connect() function returned true.</p>";
151	}
152	error_reporting($e);
153	flush();
154
155	//$ADODB_COUNTRECS=false;
156	$rs=$db->Execute('select * from adoxyz order by id');
157
158	//print_r($rs);
159	//OCIFetchStatement($rs->_queryID,$rez,0,-1);//,OCI_ASSOC \| OCI_FETCHSTATEMENT_BY_ROW);
160	//print_r($rez);
161	//die();
162	if($rs === false) $create = true;
163	else $rs->Close();
164
165	//if ($db->databaseType !='vfp') $db->Execute("drop table ADOXYZ");
166
167	if ($create) {
168	if (false && $db->databaseType == 'ibase') {
169	print "<b>Please create the following table for testing:</b></p>$createtab</p>";
170	return;
171	} else {
172	$db->debug = 1;
173	$e = error_reporting(E_ALL-E_WARNING);
174	$db->Execute($createtab);
175	error_reporting($e);
176	}
177	}
178	$rs = &$db->Execute("delete from ADOXYZ"); // some ODBC drivers will fail the drop so we delete
179	if ($rs) {
180	if(! $rs->EOF) print "<b>Error: </b>RecordSet returned by Execute('delete...') should show EOF</p>";
181	$rs->Close();
182	} else print "err=".$db->ErrorMsg();
183
184	print "<p>Test select on empty table, FetchField when EOF, and GetInsertSQL</p>";
185	$rs = &$db->Execute("select id,firstname from ADOXYZ where id=9999");
186	if ($rs && !$rs->EOF) print "<b>Error: </b>RecordSet returned by Execute(select...') on empty table should show EOF</p>";
187	if ($rs->EOF && ($o = $rs->FetchField(0))) {
188	$record['id'] = 99;
189	$record['firstname'] = 'John';
190	$sql = $db->GetInsertSQL($rs, $record);
191	if ($sql != "INSERT INTO ADOXYZ ( id, firstname ) VALUES ( 99, 'John' )") Err("GetInsertSQL does not work on empty table");
192	} else {
193	Err("FetchField does not work on empty recordset, meaning GetInsertSQL will fail...");
194	}
195	if ($rs) $rs->Close();
196	flush();
197	//$db->debug=true;
198	print "<p>Testing Commit: ";
199	$time = $db->DBDate(time());
200	if (!$db->BeginTrans()) {
201	print '<b>Transactions not supported</b></p>';
202	if ($db->hasTransactions) Err("hasTransactions should be false");
203	} else { /* COMMIT */
204	if (!$db->hasTransactions) Err("hasTransactions should be true");
205	if ($db->transCnt != 1) Err("Invalid transCnt = $db->transCnt (should be 1)");
206	$rs = $db->Execute("insert into ADOXYZ (id,firstname,lastname,created) values (99,'Should Not','Exist (Commit)',$time)");
207	if ($rs && $db->CommitTrans()) {
208	$rs->Close();
209	$rs = &$db->Execute("select * from ADOXYZ where id=99");
210	if ($rs === false \|\| $rs->EOF) {
211	print '<b>Data not saved</b></p>';
212	$rs = &$db->Execute("select * from ADOXYZ where id=99");
213	print_r($rs);
214	die();
215	} else print 'OK</p>';
216	if ($rs) $rs->Close();
217	} else {
218	if (!$rs) {
219	print "<b>Insert failed</b></p>";
220	$db->RollbackTrans();
221	} else print "<b>Commit failed</b></p>";
222	}
223	if ($db->transCnt != 0) Err("Invalid transCnt = $db->transCnt (should be 0)");
224
225	/* ROLLBACK */
226	if (!$db->BeginTrans()) print "<p><b>Error in BeginTrans</b>()</p>";
227	print "<p>Testing Rollback: ";
228	$db->Execute("insert into ADOXYZ (id,firstname,lastname,created) values (100,'Should Not','Exist (Rollback)',$time)");
229	if ($db->RollbackTrans()) {
230	$rs = $db->Execute("select * from ADOXYZ where id=100");
231	if ($rs && !$rs->EOF) print '<b>Fail: Data should rollback</b></p>';
232	else print 'OK</p>';
233	if ($rs) $rs->Close();
234	} else
235	print "<b>Commit failed</b></p>";
236
237	$rs = &$db->Execute('delete from ADOXYZ where id>50');
238	if ($rs) $rs->Close();
239
240	if ($db->transCnt != 0) Err("Invalid transCnt = $db->transCnt (should be 0)");
241	}
242
243	if (1) {
244	print "<p>Testing MetaDatabases()</p>";
245	print_r( $db->MetaDatabases());
246
247	print "<p>Testing MetaTables() and MetaColumns()</p>";
248	$a = $db->MetaTables();
249	if ($a===false) print "<b>MetaTables not supported</b></p>";
250	else {
251	print "Array of tables and views: ";
252	foreach($a as $v) print " ($v) ";
253	print '</p>';
254	}
255
256	$a = $db->MetaTables('VIEW');
257	if ($a===false) print "<b>MetaTables not supported (views)</b></p>";
258	else {
259	print "Array of views: ";
260	foreach($a as $v) print " ($v) ";
261	print '</p>';
262	}
263
264	$a = $db->MetaTables(false,false,'aDo%');
265	if ($a===false) print "<b>MetaTables not supported (mask)</b></p>";
266	else {
267	print "Array of ado%: ";
268	foreach($a as $v) print " ($v) ";
269	print '</p>';
270	}
271
272	$a = $db->MetaTables('TABLE');
273	if ($a===false) print "<b>MetaTables not supported</b></p>";
274	else {
275	print "Array of tables: ";
276	foreach($a as $v) print " ($v) ";
277	print '</p>';
278	}
279
280	$db->debug=1;
281	$a = $db->MetaColumns('ADOXYZ');
282	if ($a===false) print "<b>MetaColumns not supported</b></p>";
283	else {
284	print "<p>Columns of ADOXYZ: <font size=1><br>";
285	foreach($a as $v) {print_r($v); echo "<br>";}
286	echo "</font>";
287	}
288
289	print "<p>Testing MetaIndexes</p>";
290
291	$a = $db->MetaIndexes(('ADOXYZ'),true);
292	if ($a===false) print "<b>MetaIndexes not supported</b></p>";
293	else {
294	print "<p>Indexes of ADOXYZ: <font size=1><br>";
295	adodb_pr($a);
296	echo "</font>";
297	}
298	print "<p>Testing MetaPrimaryKeys</p>";
299	$a = $db->MetaPrimaryKeys('ADOXYZ');
300	var_dump($a);
301	}
302	$rs = &$db->Execute('delete from ADOXYZ');
303	if ($rs) $rs->Close();
304
305	$db->debug = false;
306
307
308	switch ($db->databaseType) {
309	case 'postgres7':
310	case 'postgres64':
311	case 'postgres':
312	case 'ibase':
313	print "<p>Encode=".$db->BlobEncode("abc\0d\"'
314	ef")."</p>";//'
315
316	print "<p>Testing Foreign Keys</p>";
317	$arr = $db->MetaForeignKeys('adoxyz',false,true);
318	print_r($arr);
319	if (!$arr) Err("Bad MetaForeignKeys");
320	break;
321
322	case 'odbc_mssql':
323	case 'mssqlpo':
324	print "<p>Testing Foreign Keys</p>";
325	$arr = $db->MetaForeignKeys('Orders',false,true);
326	print_r($arr);
327	if (!$arr) Err("Bad MetaForeignKeys");
328	if ($db->databaseType == 'odbc_mssql') break;
329
330	case 'mssql':
331
332
333	/*
334	ASSUME Northwind available...
335
336	CREATE PROCEDURE SalesByCategory
337	@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'
338	AS
339	IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'
340	BEGIN
341	SELECT @OrdYear = '1998'
342	END
343
344	SELECT ProductName,
345	TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
346	FROM [Order Details] OD, Orders O, Products P, Categories C
347	WHERE OD.OrderID = O.OrderID
348	AND OD.ProductID = P.ProductID
349	AND P.CategoryID = C.CategoryID
350	AND C.CategoryName = @CategoryName
351	AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
352	GROUP BY ProductName
353	ORDER BY ProductName
354	GO
355	*/
356	print "<h4>Testing Stored Procedures for mssql</h4>";
357	$saved = $db->debug;
358	$db->debug=true;
359
360	$cat = 'Dairy Products';
361	$yr = '1998';
362
363	$stmt = $db->PrepareSP('SalesByCategory');
364	$db->InParameter($stmt,$cat,'CategoryName');
365	$db->InParameter($stmt,$yr,'OrdYear');
366	$rs = $db->Execute($stmt);
367	rs2html($rs);
368
369	$cat = 'Grains/Cereals';
370	$yr = 1998;
371
372	$stmt = $db->PrepareSP('SalesByCategory');
373	$db->InParameter($stmt,$cat,'CategoryName');
374	$db->InParameter($stmt,$yr,'OrdYear');
375	$rs = $db->Execute($stmt);
376	rs2html($rs);
377
378	/*
379	Test out params - works in 4.2.3 and 4.3.3 but not 4.3.0:
380
381	CREATE PROCEDURE at_date_interval
382	@days INTEGER,
383	@start VARCHAR(20) OUT,
384	@end VARCHAR(20) OUT
385	AS
386	BEGIN
387	set @start = CONVERT(VARCHAR(20), getdate(), 101)
388	set @end =CONVERT(VARCHAR(20), dateadd(day, @days, getdate()), 101 )
389	END
390	GO
391	*/
392	$db->debug=1;
393	$stmt = $db->PrepareSP('at_date_interval');
394	$days = 10;
395	$begin_date = '';
396	$end_date = '';
397	$db->InParameter($stmt,$days,'days', 4, SQLINT4);
398	$db->OutParameter($stmt,$begin_date,'start', 20, SQLVARCHAR );
399	$db->OutParameter($stmt,$end_date,'end', 20, SQLVARCHAR );
400	$db->Execute($stmt);
401	if (empty($begin_date) or empty($end_date)) {
402	Err("MSSQL SP Test for OUT Failed");
403	print "begin=$begin_date end=$end_date<p>";
404	} else print "(Today +10days) = (begin=$begin_date end=$end_date)<p>";
405
406	$db->debug = $saved;
407	break;
408	case 'oci8':
409	case 'oci8po':
410	$saved = $db->debug;
411	$db->debug=true;
412
413
414	/*
415	CREATE TABLE PHOTOS
416	(
417	ID NUMBER(16),
418	PHOTO BLOB,
419	DESCRIPTION VARCHAR2(4000 BYTE),
420	DESCCLOB CLOB
421	);
422
423	INSERT INTO PHOTOS (ID) VALUES(1);
424	*/
425	$s = '';
426	for ($i = 0; $i <= 500; $i++) {
427	$s .= '1234567890';
428	}
429
430
431	print "<h4>Testing Blob: size=".strlen($s)."</h4>";
432	$ok = $db->Updateblob('photos','photo',$s,'id=1');
433	if (!$ok) Err("Blob failed 1");
434	else {
435	$s2= $db->GetOne("select photo from photos where id=1");
436	if ($s !== $s2) Err("updateblob does not match");
437	}
438
439	print "<h4>Testing Clob: size=".strlen($s)."</h4>";
440	$ok = $db->UpdateClob('photos','descclob',$s,'id=1');
441	if (!$ok) Err("Clob failed 1");
442	else {
443	$s2= $db->GetOne("select descclob from photos where id=1");
444	if ($s !== $s2) Err("updateclob does not match");
445	}
446
447
448	$s = '';
449	$s2 = '';
450	print "<h4>Testing Foreign Keys</h4>";
451	$arr = $db->MetaForeignKeys('emp');
452	print_r($arr);
453	if (!$arr) Err("Bad MetaForeignKeys");
454	print "<h4>Testing Cursor Variables</h4>";
455	/*
456	-- TEST PACKAGE
457
458	CREATE OR REPLACE PACKAGE Adodb AS
459	TYPE TabType IS REF CURSOR RETURN TAB%ROWTYPE;
460	PROCEDURE open_tab (tabcursor IN OUT TabType,tablenames IN VARCHAR);
461	PROCEDURE open_tab2 (tabcursor IN OUT TabType,tablenames IN OUT VARCHAR) ;
462	PROCEDURE data_out(input IN VARCHAR, output OUT VARCHAR);
463
464	PROCEDURE myproc (p1 IN NUMBER, p2 OUT NUMBER);
465	END Adodb;
466	/
467
468
469	CREATE OR REPLACE PACKAGE BODY Adodb AS
470	PROCEDURE open_tab (tabcursor IN OUT TabType,tablenames IN VARCHAR) IS
471	BEGIN
472	OPEN tabcursor FOR SELECT * FROM TAB WHERE tname LIKE tablenames;
473	END open_tab;
474
475	PROCEDURE open_tab2 (tabcursor IN OUT TabType,tablenames IN OUT VARCHAR) IS
476	BEGIN
477	OPEN tabcursor FOR SELECT * FROM TAB WHERE tname LIKE tablenames;
478	tablenames := 'TEST';
479	END open_tab2;
480
481	PROCEDURE data_out(input IN VARCHAR, output OUT VARCHAR) IS
482	BEGIN
483	output := 'Cinta Hati '\|\|input;
484	END;
485
486	PROCEDURE myproc (p1 IN NUMBER, p2 OUT NUMBER) AS
487	BEGIN
488	p2 := p1;
489	END;
490	END Adodb;
491	/
492
493
494	*/
495	$rs = $db->ExecuteCursor("BEGIN adodb.open_tab(:zz,'A%'); END;",'zz');
496
497	if ($rs && !$rs->EOF) {
498	$v = $db->GetOne("SELECT count(*) FROM tab where tname like 'A%'");
499	if ($v == $rs->RecordCount()) print "Test 1 RowCount: OK<p>";
500	else Err("Test 1 RowCount ".$rs->RecordCount().", actual = $v");
501	} else {
502	print "<b>Error in using Cursor Variables 1</b><p>";
503	}
504
505	print "<h4>Testing Stored Procedures for oci8</h4>";
506
507	$stmt = $db->PrepareSP("BEGIN adodb.data_out(:a1, :a2); END;");
508	$a1 = 'Malaysia';
509	//$a2 = ''; # a2 doesn't even need to be defined!
510	$db->InParameter($stmt,$a1,'a1');
511	$db->OutParameter($stmt,$a2,'a2');
512	$rs = $db->Execute($stmt);
513	if ($rs) {
514	if ($a2 !== 'Cinta Hati Malaysia') print "<b>Stored Procedure Error: a2 = $a2</b><p>";
515	else echo "OK: a2=$a2<p>";
516	} else {
517	print "<b>Error in using Stored Procedure IN/Out Variables</b><p>";
518	}
519
520
521	$tname = 'A%';
522
523	$stmt = $db->PrepareSP('select * from tab where tname like :tablename');
524	$db->Parameter($stmt,$tname,'tablename');
525	$rs = $db->Execute($stmt);
526	rs2html($rs);
527
528	$db->debug = $saved;
529	break;
530
531	default:
532	break;
533	}
534	$arr = array(
535	array(1,'Caroline','Miranda'),
536	array(2,'John','Lim'),
537	array(3,'Wai Hun','See')
538	);
539	$db->debug=1;
540	print "<p>Testing Bulk Insert of 3 rows</p>";
541
542	$sql = "insert into ADOXYZ (id,firstname,lastname) values (".$db->Param('0').",".$db->Param('1').",".$db->Param('2').")";
543	$db->StartTrans();
544	$db->Execute($sql,$arr);
545	$db->CompleteTrans();
546	$rs = $db->Execute('select * from ADOXYZ order by id');
547	if ($rs->RecordCount() != 3) Err("Bad bulk insert");
548
549	rs2html($rs);
550
551	$db->Execute('delete from ADOXYZ');
552
553	print "<p>Inserting 50 rows</p>";
554
555	for ($i = 0; $i < 5; $i++) {
556
557	$time = $db->DBDate(time());
558	if (empty($HTTP_GET_VARS['hide'])) $db->debug = true;
559	switch($db->databaseType){
560	case 'mssqlpo':
561	case 'mssql':
562	$sqlt = "CREATE TABLE mytable (
563	row1 INT IDENTITY(1,1) NOT NULL,
564	row2 varchar(16),
565	PRIMARY KEY (row1))";
566	//$db->debug=1;
567	if (!$db->Execute("delete from mytable"))
568	$db->Execute($sqlt);
569
570	$ok = $db->Execute("insert into mytable (row2) values ('test')");
571	$ins_id=$db->Insert_ID();
572	echo "Insert ID=";var_dump($ins_id);
573	if ($ins_id == 0) Err("Bad Insert_ID()");
574	$ins_id2 = $db->GetOne("select row1 from mytable");
575	if ($ins_id != $ins_id2) Err("Bad Insert_ID() 2");
576
577	$arr = array(0=>'Caroline',1=>'Miranda');
578	$sql = "insert into ADOXYZ (id,firstname,lastname,created) values ($i*10+0,?,?,$time)";
579	break;
580
581	case 'mysql':
582	$sqlt = "CREATE TABLE `mytable` (
583	`row1` int(11) NOT NULL auto_increment,
584	`row2` varchar(16) NOT NULL default '',
585	PRIMARY KEY (`row1`),
586	KEY `myindex` (`row1`,`row2`)
587	) ";
588	if (!$db->Execute("delete from mytable"))
589	$db->Execute($sqlt);
590
591	$ok = $db->Execute("insert into mytable (row2) values ('test')");
592	$ins_id=$db->Insert_ID();
593	echo "Insert ID=";var_dump($ins_id);
594	if ($ins_id == 0) Err("Bad Insert_ID()");
595	$ins_id2 = $db->GetOne("select row1 from mytable");
596	if ($ins_id != $ins_id2) Err("Bad Insert_ID() 2");
597
598	default:
599	$arr = array(0=>'Caroline',1=>'Miranda');
600	$sql = "insert into ADOXYZ (id,firstname,lastname,created) values ($i*10+0,?,?,$time)";
601	break;
602
603	case 'oci8':
604	case 'oci805':
605	$arr = array('first'=>'Caroline','last'=>'Miranda');
606	$amt = rand() % 100;
607	$sql = "insert into ADOXYZ (id,firstname,lastname,created) values ($i*10+0,:first,:last,$time)";
608	break;
609	}
610	if ($i & 1) {
611	$sql = $db->Prepare($sql);
612	}
613	$rs = $db->Execute($sql,$arr);
614
615	if ($rs === false) Err( 'Error inserting with parameters');
616	else $rs->Close();
617	$db->debug = false;
618	$db->Execute("insert into ADOXYZ (id,firstname,lastname,created) values ($i*10+1,'John','Lim',$time)");
619	/*$ins_id=$db->Insert_ID();
620	echo "Insert ID=";var_dump($ins_id);*/
621	if ($db->databaseType == 'mysql') if ($ins_id == 0) Err('Bad Insert_ID');
622	$db->Execute("insert into ADOXYZ (id,firstname,lastname,created) values ($i*10+2,'Mary','Lamb',$time )");
623	$db->Execute("insert into ADOXYZ (id,firstname,lastname,created) values ($i*10+3,'George','Washington',$time )");
624	$db->Execute("insert into ADOXYZ (id,firstname,lastname,created) values ($i*10+4,'Mr. Alan','Tam',$time )");
625	$db->Execute("insert into ADOXYZ (id,firstname,lastname,created) values ($i*10+5,'Alan',".$db->quote("Turing'ton").",$time )");
626	$db->Execute("insert into ADOXYZ (id,firstname,lastname,created)values ($i*10+6,'Serena','Williams',$time )");
627	$db->Execute("insert into ADOXYZ (id,firstname,lastname,created) values ($i*10+7,'Yat Sun','Sun',$time )");
628	$db->Execute("insert into ADOXYZ (id,firstname,lastname,created) values ($i*10+8,'Wai Hun','See',$time )");
629	$db->Execute("insert into ADOXYZ (id,firstname,lastname,created) values ($i*10+9,'Steven','Oey',$time )");
630	} // for
631	if (1) {
632	$db->debug=1;
633	$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
634	$cnt = $db->GetOne("select count(*) from ADOXYZ");
635	$rs = $db->Execute('update ADOXYZ set id=id+1');
636	if (!is_object($rs)) {
637	print_r($rs);
638	err("Update should return object");
639	}
640	if (!$rs) err("Update generated error");
641
642	$nrows = $db->Affected_Rows();
643	if ($nrows === false) print "<p><b>Affected_Rows() not supported</b></p>";
644	else if ($nrows != $cnt) print "<p><b>Affected_Rows() Error: $nrows returned (should be 50) </b></p>";
645	else print "<p>Affected_Rows() passed</p>";
646	}
647
648
649	$array = array('zid'=>1,'zdate'=>date('Y-m-d',time()));
650	$id = $db->GetOne("select id from ADOXYZ
651	where id=".$db->Param('zid')." and created>=".$db->Param('ZDATE')."",
652	$array);
653	if ($id != 1) Err("Bad bind; id=$id");
654	else echo "<br>Bind date/integer passed";
655
656	$db->debug = false;
657
658	$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
659	//////////////////////////////////////////////////////////////////////////////////////////
660
661	$rs = $db->Execute("select * from ADOXYZ where firstname = 'not known'");
662	if (!$rs \|\| !$rs->EOF) print "<p><b>Error on empty recordset</b></p>";
663	else if ($rs->RecordCount() != 0) {
664	print "<p><b>Error on RecordCount. Should be 0. Was ".$rs->RecordCount()."</b></p>";
665	print_r($rs->fields);
666	}
667	if ($db->databaseType !== 'odbc') {
668	$rs = &$db->Execute("select id,firstname,lastname,created,".$db->random." from ADOXYZ order by id");
669	if ($rs) {
670	if ($rs->RecordCount() != 50) {
671	print "<p><b>RecordCount returns ".$rs->RecordCount()."</b></p>";
672	$poc = $rs->PO_RecordCount('ADOXYZ');
673	if ($poc == 50) print "<p>     PO_RecordCount passed</p>";
674	else print "<p><b>PO_RecordCount returns wrong value: $poc</b></p>";
675	} else print "<p>RecordCount() passed</p>";
676	if (isset($rs->fields['firstname'])) print '<p>The fields columns can be indexed by column name.</p>';
677	else {
678	Err( '<p>The fields columns <i>cannot</i> be indexed by column name.</p>');
679	print_r($rs->fields);
680	}
681	if (empty($HTTP_GET_VARS['hide'])) rs2html($rs);
682	}
683	else print "<p><b>Error in Execute of SELECT with random</b></p>";
684	}
685	$val = $db->GetOne("select count(*) from ADOXYZ");
686	if ($val == 50) print "<p>GetOne returns ok</p>";
687	else print "<p><b>Fail: GetOne returns $val</b></p>";
688
689	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
690	$val = $db->GetRow("select count(*) from ADOXYZ");
691	if ($val[0] == 50 and sizeof($val) == 1) print "<p>GetRow returns ok</p>";
692	else {
693	print_r($val);
694	print "<p><b>Fail: GetRow returns {$val[0]}</b></p>";
695	}
696
697	print "<p>FetchObject/FetchNextObject Test</p>";
698	$rs = &$db->Execute('select * from ADOXYZ');
699
700	if (empty($rs->connection)) print "<b>Connection object missing from recordset</b></br>";
701
702	while ($o = $rs->FetchNextObject()) { // calls FetchObject internally
703	if (!is_string($o->FIRSTNAME) \|\| !is_string($o->LASTNAME)) {
704	print_r($o);
705	print "<p><b>Firstname is not string</b></p>";
706	break;
707	}
708	}
709
710	$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
711	print "<p>FetchObject/FetchNextObject Test 2</p>";
712
713	$rs = &$db->Execute('select * from ADOXYZ');
714	if (empty($rs->connection)) print "<b>Connection object missing from recordset</b></br>";
715	print_r($rs->fields);
716	while ($o = $rs->FetchNextObject()) { // calls FetchObject internally
717	if (!is_string($o->FIRSTNAME) \|\| !is_string($o->LASTNAME)) {
718	print_r($o);
719	print "<p><b>Firstname is not string</b></p>";
720	break;
721	}
722	}
723	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
724
725	$savefetch = $ADODB_FETCH_MODE;
726	$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
727
728	print "<p>CacheSelectLimit Test</p>";
729	$db->debug=1;
730	$rs = $db->CacheSelectLimit(' select id, firstname from ADOXYZ order by id',2);
731	if ($rs && !$rs->EOF) {
732	if (isset($rs->fields[0])) {
733	Err("ASSOC has numeric fields");
734	print_r($rs->fields);
735	}
736	if ($rs->fields['id'] != 1) {Err("Error"); print_r($rs->fields);};
737	if (trim($rs->fields['firstname']) != 'Caroline') {print Err("Error 2"); print_r($rs->fields);};
738	$rs->MoveNext();
739	if ($rs->fields['id'] != 2) {Err("Error 3"); print_r($rs->fields);};
740	$rs->MoveNext();
741	if (!$rs->EOF) {
742	Err("Error EOF");
743	print_r($rs);
744	}
745	}
746
747	print "<p>FETCH_MODE = ASSOC: Should get 1, Caroline</p>";
748	$rs = &$db->SelectLimit('select id,firstname from ADOXYZ order by id',2);
749	if ($rs && !$rs->EOF) {
750	if (ADODB_ASSOC_CASE == 2) {
751	$id = 'ID';
752	$fname = 'FIRSTNAME';
753	}else {
754	$id = 'id';
755	$fname = 'firstname';
756	}
757	if ($rs->fields[$id] != 1) {Err("Error 1"); print_r($rs->fields);};
758	if (trim($rs->fields[$fname]) != 'Caroline') {Err("Error 2"); print_r($rs->fields);};
759	$rs->MoveNext();
760	if ($rs->fields[$id] != 2) {Err("Error 3"); print_r($rs->fields);};
761	$rs->MoveNext();
762	if (!$rs->EOF) Err("Error EOF");
763	else if (is_array($rs->fields) \|\| $rs->fields) {
764	Err("Error: ## fields should be set to false on EOF");
765	print_r($rs->fields);
766	}
767	}
768
769	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
770	print "<p>FETCH_MODE = NUM: Should get 1, Caroline</p>";
771	$rs = &$db->SelectLimit('select id,firstname from ADOXYZ order by id',1);
772	if ($rs && !$rs->EOF) {
773	if (isset($rs->fields['id'])) Err("FETCH_NUM has ASSOC fields");
774	if ($rs->fields[0] != 1) {Err("Error 1"); print_r($rs->fields);};
775	if (trim($rs->fields[1]) != 'Caroline') {Err("Error 2");print_r($rs->fields);};
776	$rs->MoveNext();
777	if (!$rs->EOF) Err("Error EOF");
778
779	}
780	$ADODB_FETCH_MODE = $savefetch;
781
782	$db->debug = false;
783	print "<p>GetRowAssoc Upper: Should get 1, Caroline</p>";
784	$rs = &$db->SelectLimit('select id,firstname from ADOXYZ order by id',1);
785	if ($rs && !$rs->EOF) {
786	$arr = &$rs->GetRowAssoc();
787	if ($arr['ID'] != 1) {Err("Error 1");print_r($arr);};
788	if (trim($arr['FIRSTNAME']) != 'Caroline') {Err("Error 2"); print_r($arr);};
789	$rs->MoveNext();
790	if (!$rs->EOF) Err("Error EOF");
791
792	}
793	print "<p>GetRowAssoc Lower: Should get 1, Caroline</p>";
794	$rs = &$db->SelectLimit('select id,firstname from ADOXYZ order by id',1);
795	if ($rs && !$rs->EOF) {
796	$arr = &$rs->GetRowAssoc(false);
797	if ($arr['id'] != 1) {Err("Error 1"); print_r($arr);};
798	if (trim($arr['firstname']) != 'Caroline') {Err("Error 2"); print_r($arr);};
799
800	}
801
802	print "<p>GetCol Test</p>";
803	$col = $db->GetCol('select distinct firstname from adoxyz order by 1');
804	if (!is_array($col)) Err("Col size is wrong");
805	if (trim($col[0]) != 'Alan' or trim($col[9]) != 'Yat Sun') Err("Col elements wrong");
806
807	$db->debug = true;
808	print "<p>SelectLimit Distinct Test 1: Should see Caroline, John and Mary</p>";
809	$rs = &$db->SelectLimit('select distinct * from ADOXYZ order by id',3);
810
811	echo "<p>Date Update Test</p>";
812	$zdate = date('Y-m-d',time()+3600*24);
813	$zdate = $db->DBDate($zdate);
814	$db->Execute("update ADOXYZ set created=$zdate where id=1");
815	$row = $db->GetRow("select created,firstname from ADOXYZ where id=1");
816	print_r($row); echo "<br>";
817
818	//$zdate = date('Y-m-d',time()+3600*24);
819	//$db->Execute("update ADOXYZ set created=? where id=2",$zdate);
820	//$zdate = $db->GetOne("select created from ADOXYZ where id=2");
821	//echo "tomorrow=",$zdate,"<br>";
822	$db->debug=false;
823
824	if ($rs && !$rs->EOF) {
825	if (trim($rs->fields[1]) != 'Caroline') Err("Error 1");
826	$rs->MoveNext();
827	if (trim($rs->fields[1]) != 'John') Err("Error 2");
828	$rs->MoveNext();
829	if (trim($rs->fields[1]) != 'Mary') Err("Error 3");
830	$rs->MoveNext();
831	if (! $rs->EOF) Err("Error EOF");
832	//rs2html($rs);
833	} else Err("Failed SelectLimit Test 1");
834
835	print "<p>SelectLimit Test 2: Should see Mary, George and Mr. Alan</p>";
836	$rs = &$db->SelectLimit('select * from ADOXYZ order by id',3,2);
837	if ($rs && !$rs->EOF) {
838	if (trim($rs->fields[1]) != 'Mary') Err("Error 1");
839	$rs->MoveNext();
840	if (trim($rs->fields[1]) != 'George')Err("Error 2");
841	$rs->MoveNext();
842	if (trim($rs->fields[1]) != 'Mr. Alan') Err("Error 3");
843	$rs->MoveNext();
844	if (! $rs->EOF) Err("Error EOF");
845	// rs2html($rs);
846	}
847	else Err("Failed SelectLimit Test 2");
848
849	print "<p>SelectLimit Test 3: Should see Wai Hun and Steven</p>";
850	$db->debug=1;
851	global $A; $A=1;
852	$rs = &$db->SelectLimit('select * from ADOXYZ order by id',-1,48);
853	$A=0;
854	if ($rs && !$rs->EOF) {
855	if (empty($rs->connection)) print "<b>Connection object missing from recordset</b></br>";
856	if (trim($rs->fields[1]) != 'Wai Hun') Err("Error 1 ".$rs->fields[1]);
857	$rs->MoveNext();
858	if (trim($rs->fields[1]) != 'Steven') Err("Error 2 ".$rs->fields[1]);
859	$rs->MoveNext();
860	if (! $rs->EOF) {
861	Err("Error EOF");
862	}
863	//rs2html($rs);
864	}
865	else Err("Failed SelectLimit Test 3");
866	$db->debug = false;
867
868
869	$rs = &$db->Execute("select * from ADOXYZ order by id");
870	print "<p>Testing Move()</p>";
871	if (!$rs)Err( "Failed Move SELECT");
872	else {
873	if (!$rs->Move(2)) {
874	if (!$rs->canSeek) print "<p>$db->databaseType: <b>Move(), MoveFirst() nor MoveLast() not supported.</b></p>";
875	else print '<p><b>RecordSet->canSeek property should be set to false</b></p>';
876	} else {
877	$rs->MoveFirst();
878	if (trim($rs->Fields("firstname")) != 'Caroline') {
879	print "<p><b>$db->databaseType: MoveFirst failed -- probably cannot scroll backwards</b></p>";
880	}
881	else print "MoveFirst() OK<BR>";
882
883	// Move(3) tests error handling -- MoveFirst should not move cursor
884	$rs->Move(3);
885	if (trim($rs->Fields("firstname")) != 'George') {
886	print '<p>'.$rs->Fields("id")."<b>$db->databaseType: Move(3) failed</b></p>";
887	} else print "Move(3) OK<BR>";
888
889	$rs->Move(7);
890	if (trim($rs->Fields("firstname")) != 'Yat Sun') {
891	print '<p>'.$rs->Fields("id")."<b>$db->databaseType: Move(7) failed</b></p>";
892	print_r($rs);
893	} else print "Move(7) OK<BR>";
894	if ($rs->EOF) Err("Move(7) is EOF already");
895	$rs->MoveLast();
896	if (trim($rs->Fields("firstname")) != 'Steven'){
897	print '<p>'.$rs->Fields("id")."<b>$db->databaseType: MoveLast() failed</b></p>";
898	print_r($rs);
899	}else print "MoveLast() OK<BR>";
900	$rs->MoveNext();
901	if (!$rs->EOF) err("Bad MoveNext");
902	if ($rs->canSeek) {
903	$rs->Move(3);
904	if (trim($rs->Fields("firstname")) != 'George') {
905	print '<p>'.$rs->Fields("id")."<b>$db->databaseType: Move(3) after MoveLast failed</b></p>";
906
907	} else print "Move(3) after MoveLast() OK<BR>";
908	}
909
910	print "<p>Empty Move Test";
911	$rs = $db->Execute("select * from ADOXYZ where id > 0 and id < 0");
912	$rs->MoveFirst();
913	if (!$rs->EOF \|\| $rs->fields) Err("Error in empty move first");
914	}
915	}
916
917	$rs = $db->Execute('select * from ADOXYZ where id = 2');
918	if ($rs->EOF \|\| !is_array($rs->fields)) Err("Error in select");
919	$rs->MoveNext();
920	if (!$rs->EOF) Err("Error in EOF (xx) ");
921	// $db->debug=true;
922	print "<p>Testing ADODB_FETCH_ASSOC and concat: concat firstname and lastname</p>";
923
924	$save = $ADODB_FETCH_MODE;
925	$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
926	if ($db->dataProvider == 'postgres') {
927	$sql = "select ".$db->Concat('cast(firstname as varchar)',$db->qstr(' '),'lastname')." as fullname,id,".$db->sysTimeStamp." as d from ADOXYZ";
928	$rs = &$db->Execute($sql);
929	} else {
930	$sql = "select distinct ".$db->Concat('firstname',$db->qstr(' '),'lastname')." as fullname,id,".$db->sysTimeStamp." as d from ADOXYZ";
931	$rs = &$db->Execute($sql);
932	}
933	if ($rs) {
934	if (empty($HTTP_GET_VARS['hide'])) rs2html($rs);
935	} else {
936	Err( "Failed Concat:".$sql);
937	}
938	$ADODB_FETCH_MODE = $save;
939	print "<hr>Testing GetArray() ";
940	//$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
941
942	$rs = &$db->Execute("select * from ADOXYZ order by id");
943	if ($rs) {
944	$arr = &$rs->GetArray(10);
945	if (sizeof($arr) != 10 \|\| trim($arr[1][1]) != 'John' \|\| trim($arr[1][2]) != 'Lim') print $arr[1][1].' '.$arr[1][2]."<b>   ERROR</b><br>";
946	else print " OK<BR>";
947	}
948
949	$arr = $db->GetArray("select x from ADOXYZ");
950	$e = $db->ErrorMsg(); $e2 = $db->ErrorNo();
951	echo "Testing error handling, should see illegal column 'x' error=<i>$e ($e2) </i><br>";
952	if (!$e \|\| !$e2) Err("Error handling did not work");
953	print "Testing FetchNextObject for 1 object ";
954	$rs = &$db->Execute("select distinct lastname,firstname from ADOXYZ where firstname='Caroline'");
955	$fcnt = 0;
956	if ($rs)
957	while ($o = $rs->FetchNextObject()) {
958	$fcnt += 1;
959	}
960	if ($fcnt == 1) print " OK<BR>";
961	else print "<b>FAILED</b><BR>";
962
963	$stmt = $db->Prepare("select * from ADOXYZ where id < 3");
964	$rs = $db->Execute($stmt);
965	if (!$rs) Err("Prepare failed");
966	else {
967	$arr = $rs->GetArray();
968	if (!$arr) Err("Prepare failed 2");
969	if (sizeof($arr) != 2) Err("Prepare failed 3");
970	}
971	print "Testing GetAssoc() ";
972	$savecrecs = $ADODB_COUNTRECS;
973	$ADODB_COUNTRECS = false;
974	//$arr = $db->GetArray("select lastname,firstname from ADOXYZ");
975	//print_r($arr);
976	print "<hr>";
977	$rs =& $db->Execute("select distinct lastname,firstname,created from ADOXYZ");
978
979	if ($rs) {
980	$arr = $rs->GetAssoc();
981	//print_r($arr);
982	if (empty($arr['See']) \|\| trim(reset($arr['See'])) != 'Wai Hun') print $arr['See']."   <b>ERROR</b><br>";
983	else print " OK 1";
984	}
985
986	$arr = &$db->GetAssoc("select distinct lastname,firstname from ADOXYZ");
987	if ($arr) {
988	//print_r($arr);
989	if (empty($arr['See']) \|\| trim($arr['See']) != 'Wai Hun') print $arr['See']."   <b>ERROR</b><br>";
990	else print " OK 2<BR>";
991	}
992	// Comment this out to test countrecs = false
993	$ADODB_COUNTRECS = $savecrecs;
994
995	for ($loop=0; $loop < 1; $loop++) {
996	print "Testing GetMenu() and CacheExecute<BR>";
997	$db->debug = true;
998	$rs = &$db->CacheExecute(4,"select distinct firstname,lastname from ADOXYZ");
999
1000	if ($rs) print 'With blanks, Steven selected:'. $rs->GetMenu('menu','Steven').'<BR>';
1001	else print " Fail<BR>";
1002	$rs = &$db->CacheExecute(4,"select distinct firstname,lastname from ADOXYZ");
1003
1004	if ($rs) print ' No blanks, Steven selected: '. $rs->GetMenu('menu','Steven',false).'<BR>';
1005	else print " Fail<BR>";
1006
1007	$rs = &$db->CacheExecute(4,"select distinct firstname,lastname from ADOXYZ");
1008	if ($rs) print ' Multiple, Alan selected: '. $rs->GetMenu('menu','Alan',false,true).'<BR>';
1009	else print " Fail<BR>";
1010	print '</p><hr>';
1011
1012	$rs = &$db->CacheExecute(4,"select distinct firstname,lastname from ADOXYZ");
1013	if ($rs) {
1014	print ' Multiple, Alan and George selected: '. $rs->GetMenu('menu',array('Alan','George'),false,true);
1015	if (empty($rs->connection)) print "<b>Connection object missing from recordset</b></br>";
1016	} else print " Fail<BR>";
1017	print '</p><hr>';
1018
1019	print "Testing GetMenu2() <BR>";
1020	$rs = &$db->CacheExecute(4,"select distinct firstname,lastname from ADOXYZ");
1021	if ($rs) print 'With blanks, Steven selected:'. $rs->GetMenu2('menu',('Oey')).'<BR>';
1022	else print " Fail<BR>";
1023	$rs = &$db->CacheExecute(4,"select distinct firstname,lastname from ADOXYZ");
1024	if ($rs) print ' No blanks, Steven selected: '. $rs->GetMenu2('menu',('Oey'),false).'<BR>';
1025	else print " Fail<BR>";
1026	}
1027
1028	$db->debug = false;
1029
1030	// phplens
1031
1032	$sql = 'select * from ADOXYZ where 0=1';
1033	echo "<p>**Testing '$sql' (phplens compat 1)</p>";
1034	$rs = &$db->Execute($sql);
1035	if (!$rs) err( "<b>No recordset returned for '$sql'</b>");
1036	if (!$rs->FieldCount()) err( "<b>No fields returned for $sql</b>");
1037	if (!$rs->FetchField(1)) err( "<b>FetchField failed for $sql</b>");
1038
1039	$sql = 'select * from ADOXYZ order by 1';
1040	echo "<p>**Testing '$sql' (phplens compat 2)</p>";
1041	$rs = &$db->Execute($sql);
1042	if (!$rs) err( "<b>No recordset returned for '$sql'<br>".$db->ErrorMsg()."</b>");
1043
1044
1045	$sql = 'select * from ADOXYZ order by 1,1';
1046	echo "<p>**Testing '$sql' (phplens compat 3)</p>";
1047	$rs = &$db->Execute($sql);
1048	if (!$rs) err( "<b>No recordset returned for '$sql'<br>".$db->ErrorMsg()."</b>");
1049
1050
1051	// Move
1052	$rs1 = &$db->Execute("select id from ADOXYZ where id <= 2 order by 1");
1053	$rs2 = &$db->Execute("select id from ADOXYZ where id = 3 or id = 4 order by 1");
1054
1055	if ($rs1) $rs1->MoveLast();
1056	if ($rs2) $rs2->MoveLast();
1057
1058	if (empty($rs1) \|\| empty($rs2) \|\| $rs1->fields[0] != 2 \|\| $rs2->fields[0] != 4) {
1059	$a = $rs1->fields[0];
1060	$b = $rs2->fields[0];
1061	print "<p><b>Error in multiple recordset test rs1=$a rs2=$b (should be rs1=2 rs2=4)</b></p>";
1062	} else
1063	print "<p>Testing multiple recordsets OK</p>";
1064
1065
1066	echo "<p> GenID test: ";
1067	for ($i=1; $i <= 10; $i++)
1068	echo "($i: ",$val = $db->GenID($db->databaseType.'abcseq6' ,5), ") ";
1069	if ($val == 0) Err("GenID not supported");
1070
1071	if ($val) {
1072	$db->DropSequence('abc_seq2');
1073	$db->CreateSequence('abc_seq2');
1074	$val = $db->GenID('abc_seq2');
1075	$db->DropSequence('abc_seq2');
1076	$db->CreateSequence('abc_seq2');
1077	$val = $db->GenID('abc_seq2');
1078	if ($val != 1) Err("Drop and Create Sequence not supported ($val)");
1079	}
1080	echo "<p>";
1081
1082	if (substr($db->dataProvider,0,3) != 'notused') { // used to crash ado
1083	$sql = "select firstnames from adoxyz";
1084	print "<p>Testing execution of illegal statement: <i>$sql</i></p>";
1085	if ($db->Execute($sql) === false) {
1086	print "<p>This returns the following ErrorMsg(): <i>".$db->ErrorMsg()."</i> and ErrorNo(): ".$db->ErrorNo().'</p>';
1087	} else
1088	print "<p><b>Error in error handling -- Execute() should return false</b></p>";
1089	} else
1090	print "<p><b>ADO skipped error handling of bad select statement</b></p>";
1091
1092	print "<p>ASSOC TEST 2<br>";
1093	$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
1094	$rs = $db->query('select * from adoxyz order by id');
1095	if ($ee = $db->ErrorMsg()) {
1096	Err("Error message=$ee");
1097	}
1098	if ($ee = $db->ErrorNo()) {
1099	Err("Error No = $ee");
1100	}
1101	print_r($rs->fields);
1102	for($i=0;$i<$rs->FieldCount();$i++)
1103	{
1104	$fld=$rs->FetchField($i);
1105	print "<br> Field name is ".$fld->name;
1106	print " ".$rs->Fields($fld->name);
1107	}
1108
1109
1110	print "<p>BOTH TEST 2<br>";
1111	if ($db->dataProvider == 'ado') {
1112	print "<b>ADODB_FETCH_BOTH not supported</b> for dataProvider=".$db->dataProvider."<br>";
1113	} else {
1114	$ADODB_FETCH_MODE = ADODB_FETCH_BOTH;
1115	$rs = $db->query('select * from adoxyz order by id');
1116	for($i=0;$i<$rs->FieldCount();$i++)
1117	{
1118	$fld=$rs->FetchField($i);
1119	print "<br> Field name is ".$fld->name;
1120	print " ".$rs->Fields($fld->name);
1121	}
1122	}
1123
1124	print "<p>NUM TEST 2<br>";
1125	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
1126	$rs = $db->query('select * from adoxyz order by id');
1127	for($i=0;$i<$rs->FieldCount();$i++)
1128	{
1129	$fld=$rs->FetchField($i);
1130	print "<br> Field name is ".$fld->name;
1131	print " ".$rs->Fields($fld->name);
1132	}
1133
1134	print "<p>ASSOC Test of SelectLimit<br>";
1135	$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
1136	$rs = $db->selectlimit('select * from adoxyz order by id',3,4);
1137	$cnt = 0;
1138	while ($rs && !$rs->EOF) {
1139	$cnt += 1;
1140	if (!isset($rs->fields['firstname'])) {
1141	print "<br><b>ASSOC returned numeric field</b></p>";
1142	break;
1143	}
1144	$rs->MoveNext();
1145	}
1146	if ($cnt != 3) print "<br><b>Count should be 3, instead it was $cnt</b></p>";
1147
1148
1149	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
1150	if ($db->sysDate) {
1151	$saved = $db->debug;
1152	$db->debug = 1;
1153	$rs = $db->Execute("select {$db->sysDate} from adoxyz where id=1");
1154	if (ADORecordSet::UnixDate(date('Y-m-d')) != $rs->UnixDate($rs->fields[0])) {
1155	print "<p><b>Invalid date {$rs->fields[0]}</b></p>";
1156	} else
1157	print "<p>Passed \$sysDate test ({$rs->fields[0]})</p>";
1158
1159	print_r($rs->FetchField(0));
1160	print time();
1161	$db->debug=$saved;
1162	} else {
1163	print "<p><b>\$db->sysDate not defined</b></p>";
1164	}
1165
1166	print "<p>Test CSV</p>";
1167	include_once('../toexport.inc.php');
1168	//$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
1169	$rs = $db->SelectLimit('select id,firstname,lastname,created,\'He, he\' he,\'"\' q from adoxyz',10);
1170
1171	print "<pre>";
1172	print rs2csv($rs);
1173	print "</pre>";
1174
1175	$rs = $db->SelectLimit('select id,firstname,lastname,created,\'The "young man", he said\' from adoxyz',10);
1176
1177	if (PHP_VERSION < 5) {
1178	print "<pre>";
1179	rs2tabout($rs);
1180	print "</pre>";
1181	}
1182	//print " CacheFlush ";
1183	//$db->CacheFlush();
1184
1185	$date = $db->SQLDate('d-m-M-Y-\QQ h:i:s A');
1186	$sql = "SELECT $date from ADOXYZ";
1187	print "<p>Test SQLDate: ".htmlspecialchars($sql)."</p>";
1188	$rs = $db->SelectLimit($sql,1);
1189	$d = date('d-m-M-Y-').'Q'.(ceil(date('m')/3.0)).date(' h:i:s A');
1190	if (!$rs) Err("SQLDate query returned no recordset");
1191	else if ($d != $rs->fields[0]) Err("SQLDate 1 failed expected: <br>act:$d <br>sql:".$rs->fields[0]);
1192
1193	$date = $db->SQLDate('d-m-M-Y-\QQ h:i:s A',$db->DBDate("1974-02-25"));
1194	$sql = "SELECT $date from ADOXYZ";
1195	print "<p>Test SQLDate: ".htmlspecialchars($sql)."</p>";
1196	$rs = $db->SelectLimit($sql,1);
1197	$ts = ADOConnection::UnixDate('1974-02-25');
1198	$d = date('d-m-M-Y-',$ts).'Q'.(ceil(date('m',$ts)/3.0)).date(' h:i:s A',$ts);
1199	if (!$rs) Err("SQLDate query returned no recordset");
1200	else if ($d != $rs->fields[0]) Err("SQLDate 2 failed expected: <br>act:$d <br>sql:".$rs->fields[0]);
1201
1202
1203	print "<p>Test Filter</p>";
1204	$db->debug = 1;
1205
1206	$rs = $db->SelectLimit('select * from ADOXYZ where id < 3 order by id');
1207
1208	$rs = RSFilter($rs,'do_strtolower');
1209	if (trim($rs->fields[1]) != 'caroline' && trim($rs->fields[2]) != 'miranda') {
1210	err('**** RSFilter failed');
1211	print_r($rs->fields);
1212	}
1213
1214	rs2html($rs);
1215
1216	$db->debug=1;
1217
1218
1219	print "<p>Test Replace</p>";
1220
1221	$ret = $db->Replace('adoxyz',
1222	array('id'=>1,'firstname'=>'Caroline','lastname'=>'Miranda'),
1223	array('id'),
1224	$autoq = true);
1225	if (!$ret) echo "<p>Error in replacing existing record</p>";
1226	else {
1227	$saved = $db->debug;
1228	$db->debug = 0;
1229	$savec = $ADODB_COUNTRECS;
1230	$ADODB_COUNTRECS = true;
1231	$rs = $db->Execute('select * FROM ADOXYZ where id=1');
1232	$db->debug = $saved;
1233	if ($rs->RecordCount() != 1) {
1234	$cnt = $rs->RecordCount();
1235	rs2html($rs);
1236	print "<b>Error - Replace failed, count=$cnt</b><p>";
1237	}
1238	$ADODB_COUNTRECS = $savec;
1239	}
1240	$ret = $db->Replace('adoxyz',
1241	array('id'=>1000,'firstname'=>'Harun','lastname'=>'Al-Rashid'),
1242	array('id','firstname'),
1243	$autoq = true);
1244	if ($ret != 2) print "<b>Replace failed: </b>";
1245	print "test A return value=$ret (2 expected) <p>";
1246
1247	$ret = $db->Replace('adoxyz',
1248	array('id'=>1000,'firstname'=>'Sherazade','lastname'=>'Al-Rashid'),
1249	'id',
1250	$autoq = true);
1251	if ($ret != 1)
1252	if ($db->dataProvider == 'ibase' && $ret == 2);
1253	else print "<b>Replace failed: </b>";
1254	print "test B return value=$ret (1 or if ibase then 2 expected) <p>";
1255
1256	print "<h3>rs2rs Test</h3>";
1257
1258	$rs = $db->Execute('select * from adoxyz order by id');
1259	$rs = $db->_rs2rs($rs);
1260	$rs->valueX = 'X';
1261	$rs->MoveNext();
1262	$rs = $db->_rs2rs($rs);
1263	if (!isset($rs->valueX)) err("rs2rs does not preserve array recordsets");
1264	if (reset($rs->fields) != 1) err("rs2rs does not move to first row");
1265
1266	/////////////////////////////////////////////////////////////
1267	include_once('../pivottable.inc.php');
1268	print "<h3>Pivot Test</h3>";
1269	$db->debug=true;
1270	$sql = PivotTableSQL(
1271	$db, # adodb connection
1272	'adoxyz', # tables
1273	'firstname', # row fields
1274	'lastname', # column fields
1275	false, # join
1276	'ID', # sum
1277	'Sum ', # label for sum
1278	'sum', # aggregate function
1279	true
1280	);
1281	$rs = $db->Execute($sql);
1282	if ($rs) rs2html($rs);
1283	else Err("Pivot sql error");
1284
1285	$db->debug=false;
1286	include_once "PEAR.php";
1287
1288	// PEAR TESTS BELOW
1289	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
1290	$pear = true;
1291	$rs = $db->query('select * from adoxyz where id>0 and id<10 order by id');
1292
1293	$i = 0;
1294	if ($rs && !$rs->EOF) {
1295	while ($arr = $rs->fetchRow()) {
1296	$i++;
1297	//print "$i ";
1298	if ($arr[0] != $i) {
1299	print_r($arr);
1300	print "<p><b>PEAR DB emulation error 1.</b></p>";
1301	$pear = false;
1302	break;
1303	}
1304	}
1305	$rs->Close();
1306	}
1307
1308
1309	if ($i != $db->GetOne('select count(*) from adoxyz where id>0 and id<10')) {
1310	print "<p><b>PEAR DB emulation error 1.1 EOF ($i)</b></p>";
1311	$pear = false;
1312	}
1313
1314	$rs = $db->limitQuery('select * from adoxyz where id>0 order by id',$i=3,$top=3);
1315	$i2 = $i;
1316	if ($rs && !$rs->EOF) {
1317
1318	while (!is_object($rs->fetchInto($arr))) {
1319	$i2++;
1320
1321	// print_r($arr);
1322	// print "$i ";print_r($arr);
1323	if ($arr[0] != $i2) {
1324	print "<p><b>PEAR DB emulation error 2.</b></p>";
1325	$pear = false;
1326	break;
1327	}
1328	}
1329	$rs->Close();
1330	}
1331	if ($i2 != $i+$top) {
1332	print "<p><b>PEAR DB emulation error 2.1 EOF (correct=$i+$top, actual=$i2)</b></p>";
1333	$pear = false;
1334	}
1335
1336	if ($pear) print "<p>PEAR DB emulation passed.</p>";
1337
1338	$rs = $db->SelectLimit("select ".$db->sysDate." from adoxyz",1);
1339	$date = $rs->fields[0];
1340	if (!$date) Err("Bad sysDate");
1341	else {
1342	$ds = $db->UserDate($date,"d m Y");
1343	if ($ds != date("d m Y")) Err("Bad UserDate: ".$ds.' expected='.date("d m Y"));
1344	else echo "Passed UserDate: $ds<p>";
1345	}
1346	$db->debug=1;
1347	if ($db->dataProvider == 'oci8')
1348	$rs = $db->SelectLimit("select to_char(".$db->sysTimeStamp.",'YYYY-MM-DD HH24:MI:SS') from adoxyz",1);
1349	else
1350	$rs = $db->SelectLimit("select ".$db->sysTimeStamp." from adoxyz",1);
1351	$date = $rs->fields[0];
1352	if (!$date) Err("Bad sysTimeStamp");
1353	else {
1354	$ds = $db->UserTimeStamp($date,"H \\h\\r\\s-d m Y");
1355	if ($ds != date("H \\h\\r\\s-d m Y")) Err("Bad UserTimeStamp: ".$ds.", correct is ".date("H \\h\\r\\s-d m Y"));
1356	else echo "Passed UserTimeStamp: $ds<p>";
1357
1358	$date = 100;
1359	$ds = $db->UserTimeStamp($date,"H \\h\\r\\s-d m Y");
1360	$ds2 = date("H \\h\\r\\s-d m Y",$date);
1361	if ($ds != $ds2) Err("Bad UserTimeStamp 2: $ds: $ds2");
1362	else echo "Passed UserTimeStamp 2: $ds<p>";
1363	}
1364	if ($db->hasTransactions) {
1365	//$db->debug=1;
1366	echo "<p>Testing StartTrans CompleteTrans</p>";
1367	$db->raiseErrorFn = false;
1368	$db->StartTrans();
1369	$rs = $db->Execute('select * from notable');
1370	$db->StartTrans();
1371	$db->BeginTrans();
1372	$db->Execute("update ADOXYZ set firstname='Carolx' where id=1");
1373	$db->CommitTrans();
1374	$db->CompleteTrans();
1375	$rez = $db->CompleteTrans();
1376	if ($rez !== false) {
1377	if (is_null($rez)) Err("Error: _transOK not modified");
1378	else Err("Error: CompleteTrans (1) should have failed");
1379	} else {
1380	$name = $db->GetOne("Select firstname from ADOXYZ where id=1");
1381	if ($name == "Carolx") Err("Error: CompleteTrans (2) should have failed");
1382	else echo "<p> -- Passed StartTrans test1 - rolling back</p>";
1383	}
1384
1385	$db->StartTrans();
1386	$db->BeginTrans();
1387	$db->Execute("update ADOXYZ set firstname='Carolx' where id=1");
1388	$db->RollbackTrans();
1389	$rez = $db->CompleteTrans();
1390	if ($rez !== true) Err("Error: CompleteTrans (1) should have succeeded");
1391	else {
1392	$name = $db->GetOne("Select firstname from ADOXYZ where id=1");
1393	if (trim($name) != "Carolx") Err("Error: CompleteTrans (2) should have succeeded, returned name=$name");
1394	else echo "<p> -- Passed StartTrans test2 - commiting</p>";
1395	}
1396	}
1397
1398	$saved = $db->debug;
1399	$db->debug=1;
1400	$cnt = _adodb_getcount($db, 'select * from ADOXYZ where firstname in (select firstname from ADOXYZ)');
1401	echo "<b>Count=</b> $cnt";
1402	$db->debug=$saved;
1403
1404	global $TESTERRS;
1405	$debugerr = true;
1406
1407	global $ADODB_LANG;$ADODB_LANG = 'fr';
1408	$db->debug = false;
1409	$TESTERRS = 0;
1410	$db->raiseErrorFn = 'adodb_test_err';
1411	global $ERRNO; // from adodb_test_err
1412	$db->Execute('select * from nowhere');
1413	$metae = $db->MetaError($ERRNO);
1414	if ($metae !== DB_ERROR_NOSUCHTABLE) print "<p><b>MetaError=".$metae." wrong</b>, should be ".DB_ERROR_NOSUCHTABLE."</p>";
1415	else print "<p>MetaError ok (".DB_ERROR_NOSUCHTABLE."): ".$db->MetaErrorMsg($metae)."</p>";
1416	if ($TESTERRS != 1) print "<b>raiseErrorFn select nowhere failed</b><br>";
1417	$rs = $db->Execute('select * from adoxyz');
1418	if ($debugerr) print " Move";
1419	$rs->Move(100);
1420	$rs->_queryID = false;
1421	if ($debugerr) print " MoveNext";
1422	$rs->MoveNext();
1423	if ($debugerr) print " $rs=false";
1424	$rs = false;
1425
1426	print "<p>SetFetchMode() tests</p>";
1427	$db->SetFetchMode(ADODB_FETCH_ASSOC);
1428	$rs = $db->SelectLimit('select firstname from adoxyz',1);
1429	// var_dump($rs->fields);
1430	if (!isset($rs->fields['firstname'])) Err("BAD FETCH ASSOC");
1431
1432	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
1433	$rs = $db->SelectLimit('select firstname from adoxyz',1);
1434	//var_dump($rs->fields);
1435	if (!isset($rs->fields['firstname'])) Err("BAD FETCH ASSOC");
1436
1437	$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
1438	$db->SetFetchMode(ADODB_FETCH_NUM);
1439	$rs = $db->SelectLimit('select firstname from adoxyz',1);
1440	if (!isset($rs->fields[0])) Err("BAD FETCH NUM");
1441
1442	print "<p>Test MetaTables again with SetFetchMode()</p>";
1443	$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
1444	$db->SetFetchMode(ADODB_FETCH_ASSOC);
1445	print_r($db->MetaTables());
1446	print "<p>";
1447	////////////////////////////////////////////////////////////////////
1448
1449	$conn = NewADOConnection($db->databaseType);
1450	$conn->raiseErrorFn = 'adodb_test_err';
1451	@$conn->PConnect('abc');
1452	if ($TESTERRS == 2) print "raiseErrorFn tests passed<br>";
1453	else print "<b>raiseErrorFn tests failed ($TESTERRS)</b><br>";
1454
1455
1456	////////////////////////////////////////////////////////////////////
1457
1458	global $nocountrecs;
1459
1460	if (isset($nocountrecs) && $ADODB_COUNTRECS) err("Error: \$ADODB_COUNTRECS is set");
1461	if (empty($nocountrecs) && $ADODB_COUNTRECS==false) err("Error: \$ADODB_COUNTRECS is not set");
1462
1463
1464	?>
1465	</p>
1466	<table width=100% ><tr><td bgcolor=beige> </td></tr></table>
1467	</p></form>
1468	<?php
1469
1470	if ($rs1) $rs1->Close();
1471	if ($rs2) $rs2->Close();
1472	if ($rs) $rs->Close();
1473	$db->Close();
1474
1475	if ($db->transCnt != 0) Err("Error in transCnt=$db->transCnt (should be 0)");
1476
1477
1478	printf("<p>Total queries=%d; total cached=%d</p>",$EXECS+$CACHED, $CACHED);
1479	}
1480
1481	function adodb_test_err($dbms, $fn, $errno, $errmsg, $p1=false, $p2=false)
1482	{
1483	global $TESTERRS,$ERRNO;
1484
1485	$ERRNO = $errno;
1486	$TESTERRS += 1;
1487	print "<i>** $dbms ($fn): errno=$errno   errmsg=$errmsg ($p1,$p2)</i><br>";
1488	}
1489
1490	//--------------------------------------------------------------------------------------
1491
1492
1493	@set_time_limit(240); // increase timeout
1494
1495	include("../tohtml.inc.php");
1496	include("../adodb.inc.php");
1497	include("../rsfilter.inc.php");
1498
1499	/* White Space Check */
1500
1501	if (isset($_SERVER['argv'][1])) {
1502	//print_r($_SERVER['argv']);
1503	$HTTP_GET_VARS[$_SERVER['argv'][1]] = 1;
1504	}
1505
1506	if (@$HTTP_SERVER_VARS['COMPUTERNAME'] == 'TIGRESS') {
1507	CheckWS('mysqlt');
1508	CheckWS('postgres');
1509	CheckWS('oci8po');
1510
1511	CheckWS('firebird');
1512	CheckWS('sybase');
1513	if (!ini_get('safe_mode')) CheckWS('informix');
1514
1515	CheckWS('ado_mssql');
1516	CheckWS('ado_access');
1517	CheckWS('mssql');
1518
1519	CheckWS('vfp');
1520	CheckWS('sqlanywhere');
1521	CheckWS('db2');
1522	CheckWS('access');
1523	CheckWS('odbc_mssql');
1524	CheckWS('firebird15');
1525	//
1526	CheckWS('oracle');
1527	CheckWS('proxy');
1528	CheckWS('fbsql');
1529	print "White Space Check complete<p>";
1530	}
1531	if (sizeof($HTTP_GET_VARS) == 0) $testmysql = true;
1532
1533
1534	foreach($HTTP_GET_VARS as $k=>$v) {
1535	//global $$k;
1536	$$k = $v;
1537	}
1538	if (strpos(PHP_VERSION,'5') === 0) {
1539	//$testaccess=1;
1540	//$testmssql = 1;
1541	//$testsqlite=1;
1542	}
1543	?>
1544	<html>
1545	<title>ADODB Testing</title>
1546	<body bgcolor=white>
1547	<H1>ADODB Test</H1>
1548
1549	This script tests the following databases: Interbase, Oracle, Visual FoxPro, Microsoft Access (ODBC and ADO), MySQL, MSSQL (ODBC, native, ADO).
1550	There is also support for Sybase, PostgreSQL.</p>
1551	For the latest version of ADODB, visit <a href=http://adodb.sourceforge.net/>adodb.sourceforge.net</a>.</p>
1552
1553	Test <a href=test4.php>GetInsertSQL/GetUpdateSQL</a>
1554	<a href=testsessions.php>Sessions</a>
1555	<a href=testpaging.php>Paging</a>
1556	<a href=test-perf.php>Perf Monitor</a><p>
1557	<?php
1558	include('./testdatabases.inc.php');
1559
1560	echo "<br>vers=",ADOConnection::Version();
1561
1562
1563	include_once('../adodb-time.inc.php');
1564	if (!isset($_GET['nd'])) adodb_date_test();
1565	?>
1566	<p><i>ADODB Database Library (c) 2000-2004 John Lim. All rights reserved. Released under BSD and LGPL.</i></p>
1567	</body>
1568	</html>

Note: See TracBrowser for help on using the repository browser.

Download in other formats: