ADODB Testing

Content-type: text/html X-Powered-By: PHP/4.3.8

# ADODB Test

This script tests the following databases: Interbase, Oracle, Visual FoxPro, Microsoft Access (ODBC and ADO), MySQL, MSSQL (ODBC, native, ADO). There is also support for Sybase, PostgreSQL. For the latest version of ADODB, visit adodb.sourceforge.net. Test GetInsertSQL/GetUpdateSQL Sessions Paging Perf Monitor

Access
Interbase
MSSQL
MySQL
MySQL ODBC
MySQLi

SQLite
MySQL Proxy
Oracle (oci8)
PostgreSQL
PostgreSQL ODBC

PgSQL PDO
MySQL PDO
SQLite PDO
Access PDO

DB2
VFP+ODBTP
ADO (for mssql and access)
$ADODB_COUNTRECS=false
No SQL Logging
ADOdb time test

# Connecting oci8po...

### SQL Logging enabled

### ADODB Version: V4.81 3 May 2006 (c) 2000-2006 John Lim (jlim#natsoft.com.my). All rights reserved. Released BSD & LGPL. Host: sherkhan Database: PHP: 4.3.8

Array ( [compat] => 9.2.0.0.0 [description] => Oracle9i Release 9.2.0.1.0 - Production JServer Release 9.2.0.1.0 - Production [version] => 9.2.0.1.0 )
db->Time: 19-05-2005 11:44:42
Date=2002-04-07
date1 (1969-02-20) = TO_DATE('1969-02-20','YYYY-MM-DD')
date1 (1999-02-20) = TO_DATE('1999-02-20','YYYY-MM-DD')
date1.1 1999 = TO_DATE('2005-05-19','YYYY-MM-DD')
date2 (1970-1-2) = TO_DATE('1970-01-02','YYYY-MM-DD')

ts1 (1999-02-20 13:40:50) = TO_DATE('1999-02-20, 01:40:50 AM','RRRR-MM-DD, HH:MI:SS AM')
ts1.1 (1999-02-20 13:40:00) = TO_DATE('1999-02-20, 01:04:00 PM','RRRR-MM-DD, HH:MI:SS AM')
ts2 (1999-02-20) = TO_DATE('1999-02-20, 12:00:00 AM','RRRR-MM-DD, HH:MI:SS AM')
ts3 (1970-1-2 +/- timezone) = TO_DATE('1970-01-02, 08:00:00 AM','RRRR-MM-DD, HH:MI:SS AM')
Fractional TS (1999-2-20 13:40:50.91): TO_DATE('1999-02-20, 01:40:50 PM','RRRR-MM-DD, HH:MI:SS AM')
unixdate 1999-02-20 = 1999-02-20

ts4 =61
ts5 =TO_DATE('2004-01-10, 09:21:23 AM','RRRR-MM-DD, HH:MI:SS AM')
ts6 =2004-01-10 09:21:23
ts7 =TO_DATE('2004-01-10, 09:21:23 AM','RRRR-MM-DD, HH:MI:SS AM')

Test select on empty table, FetchField when EOF, and GetInsertSQL

Testing Commit: OK

Testing Rollback: OK

Testing MetaDatabases()

AAArray ( [0] => ANONYMOUS [1] => CTXSYS [2] => DEV [3] => HR [4] => IOU [5] => JURIS10 [6] => JURIS9 [7] => MDSYS [8] => ODB [9] => ODB2 [10] => ODM [11] => ODM_MTR [12] => OE [13] => OLAPSYS [14] => ORDPLUGINS [15] => ORDSYS [16] => PM [17] => QS [18] => QS_ADM [19] => QS_CB [20] => QS_CBADM [21] => QS_CS [22] => QS_ES [23] => QS_OS [24] => QS_WS [25] => RMAN [26] => SCOTT [27] => SH [28] => SONY [29] => TOAD [30] => WKPROXY [31] => WKSYS [32] => WMSYS [33] => XDB )

Testing MetaTables() and MetaColumns()

AAArray of tables and views: (abalone2) (abalone2_tree_jl1) (abalone_tree) (address) (adodb_logsql) (adoxyz) (alertrx) (atest) (billdet) (billhdr) (binlist) (cache_jorg_121) (cache_jorg_127) (contact) (create$java$lob$table) (diary) (diary2) (dlagent) (dlagentactivity) (dlagentcampaign) (dlagentcampaignvw) (dlagentcurrentstatus) (dlagenthistory) (dlagentshift) (dlagentstats) (dlagent_collection) (dlcallhistory) (dlcampaign) (dlcampaignactivity) (dlcampaignset) (dlcampaignshift) (dlcampaign_collection) (dlkbagentstatus) (dlkbarea) (dlkbgroup) (dlkbliststatus) (dlkbmedia) (dlkboutcome) (dlkbshift) (dlkbskill) (dlkbstagevw) (dlkbstatus) (dlkbtype) (dlkbvw) (dllist) (dllisterror) (dllistsource) (dllisttemp) (dlmessage) (dlteam) (dltimedcampaignactivity) (dltrafficreport) (employee) (emp_pay_hist) (emp_perf_hist) (emp_perf_plan) (endorser) (exchange) (fclsdatextra) (fclsfacreln) (fclskb) (fclskbcatvals) (fclskbexpiry) (fclskbfacreln) (fclskbprop) (fclskbstage) (fclskbstep) (fclskbstgroup) (fclssec) (fclssecrole) (fclsstepdate) (germall) (germall_cluster) (germall_range) (germall_rules) (germall_tree) (glass) (glass_tree) (ivr) (ivrcall) (ivrcca) (ivrevents) (ivrservice) (ivr_by_hour) (ivr_by_hours) (ivr_cat) (ivr_defaults) (ivr_demo) (ivr_dim) (ivr_map) (ivr_map_hours) (ivr_rp) (ivr_tmp_demo) (java$class$md5$table) (java$options) (jcampaign) (jcasemast) (jclnt) (jclntbrch) (jclntdept) (jclntdiv) (jcontact) (jcustomer) (jdefaults) (jdiary) (jfacility) (jfirm) (jfirmbrch) (jfirmdiv) (jmail) (jmail_x) (job_market_data) (jopportunity) (jorg) (jstate) (jstepprint) (jsurvey) (jtask) (jtaskitem) (kb) (kbactivity) (kbarea) (kbcatvals) (kbcountry) (kbcurrency) (kbdomain_juris10) (kbdomain_tiger2) (kbjob) (kbjobrating) (kblang) (kbmedia) (kbproductivity) (kbq) (kbqset) (kbqstatus) (kbregion) (kbrole) (kbskill) (kbstage) (kbstatus) (kbstep) (kbsteptype) (kbsubdomain) (kbworkarea) (kbworknode) (kutu_testtable) (lens_columns) (lens_groups) (lens_log) (lens_logurl) (lens_perms) (lens_rank) (lens_users) (lens_users_collection) (lens_users_x) (mcc) (myarn) (mycard) (oldkbstage) (oldkbstep) (old_ivr_by_hour) (pay_budget_hist) (pay_budget_plan) (photos) (phplens) (phplens_lock) (plan_table) (products) (qhist) (qlog) (qsetreport) (qtest) (qtest_len) (q_1005) (q_1006) (q_1008) (q_1010) (q_1011) (q_1016) (q_1017) (q_1018) (q_1019) (q_1020) (q_1021) (q_1022) (q_1023) (q_1024) (reseller) (review_period) (role) (ruledet) (splist) (tempname) (tennis) (tennis_tree) (toad_plan_table) (transact) (transact_040321) (transalert) (transalert_22) (transrule) (vt) (vwaddress) (vwcasecontact) (vwcaserolefacreln) (vwdiary_diary2) (vwfclsfacreln_contact) (vwfclssec_fclssecrole) (vwreview_search) (vwstepdatextra) (vwtransact) (vwtransalert) (xaddress) (xcase) (xcasevvv) (xcontact) (xcurstep) (xdiary) (xfirm) (xmonitor) (xrole) (xsummstatustmp) (xx) AAArray of views: (address) (contact) (dlagentcampaignvw) (dlkbstagevw) (dlkbvw) (ivr) (ivr_by_hours) (ivr_map_hours) (products) (role) (vt) (vwaddress) (vwcasecontact) (vwcaserolefacreln) (vwdiary_diary2) (vwfclsfacreln_contact) (vwfclssec_fclssecrole) (vwreview_search) (vwstepdatextra) (vwtransact) (vwtransalert) (xcasevvv) (xdiary) (xfirm) AAArray of ado%: (adoxyz) AAArray of tables: (abalone2) (abalone2_tree_jl1) (abalone_tree) (adodb_logsql) (adoxyz) (alertrx) (atest) (billdet) (billhdr) (binlist) (cache_jorg_121) (cache_jorg_127) (create$java$lob$table) (diary) (diary2) (dlagent) (dlagentactivity) (dlagentcampaign) (dlagentcurrentstatus) (dlagenthistory) (dlagentshift) (dlagentstats) (dlagent_collection) (dlcallhistory) (dlcampaign) (dlcampaignactivity) (dlcampaignset) (dlcampaignshift) (dlcampaign_collection) (dlkbagentstatus) (dlkbarea) (dlkbgroup) (dlkbliststatus) (dlkbmedia) (dlkboutcome) (dlkbshift) (dlkbskill) (dlkbstatus) (dlkbtype) (dllist) (dllisterror) (dllistsource) (dllisttemp) (dlmessage) (dlteam) (dltimedcampaignactivity) (dltrafficreport) (employee) (emp_pay_hist) (emp_perf_hist) (emp_perf_plan) (endorser) (exchange) (fclsdatextra) (fclsfacreln) (fclskb) (fclskbcatvals) (fclskbexpiry) (fclskbfacreln) (fclskbprop) (fclskbstage) (fclskbstep) (fclskbstgroup) (fclssec) (fclssecrole) (fclsstepdate) (germall) (germall_cluster) (germall_range) (germall_rules) (germall_tree) (glass) (glass_tree) (ivrcall) (ivrcca) (ivrevents) (ivrservice) (ivr_by_hour) (ivr_cat) (ivr_defaults) (ivr_demo) (ivr_dim) (ivr_map) (ivr_rp) (ivr_tmp_demo) (java$class$md5$table) (java$options) (jcampaign) (jcasemast) (jclnt) (jclntbrch) (jclntdept) (jclntdiv) (jcontact) (jcustomer) (jdefaults) (jdiary) (jfacility) (jfirm) (jfirmbrch) (jfirmdiv) (jmail) (jmail_x) (job_market_data) (jopportunity) (jorg) (jstate) (jstepprint) (jsurvey) (jtask) (jtaskitem) (kb) (kbactivity) (kbarea) (kbcatvals) (kbcountry) (kbcurrency) (kbdomain_juris10) (kbdomain_tiger2) (kbjob) (kbjobrating) (kblang) (kbmedia) (kbproductivity) (kbq) (kbqset) (kbqstatus) (kbregion) (kbrole) (kbskill) (kbstage) (kbstatus) (kbstep) (kbsteptype) (kbsubdomain) (kbworkarea) (kbworknode) (kutu_testtable) (lens_columns) (lens_groups) (lens_log) (lens_logurl) (lens_perms) (lens_rank) (lens_users) (lens_users_collection) (lens_users_x) (mcc) (myarn) (mycard) (oldkbstage) (oldkbstep) (old_ivr_by_hour) (pay_budget_hist) (pay_budget_plan) (photos) (phplens) (phplens_lock) (plan_table) (qhist) (qlog) (qsetreport) (qtest) (qtest_len) (q_1005) (q_1006) (q_1008) (q_1010) (q_1011) (q_1016) (q_1017) (q_1018) (q_1019) (q_1020) (q_1021) (q_1022) (q_1023) (q_1024) (reseller) (review_period) (ruledet) (splist) (tempname) (tennis) (tennis_tree) (toad_plan_table) (transact) (transact_040321) (transalert) (transalert_22) (transrule) (xaddress) (xcase) (xcontact) (xcurstep) (xmonitor) (xrole) (xsummstatustmp) (xx) ----- (oci8po): select lower(cname), coltype, width, SCALE, PRECISION, NULLS, DEFAULTVAL from col where tname='ADOXYZ' order by colno ----- AA

Columns of ADOXYZ:
adofieldobject Object ( [name] => firstname [max_length] => 24 [type] => VARCHAR2 [scale] => [not_null] => [binary] => [default_value] => )
adofieldobject Object ( [name] => lastname [max_length] => 24 [type] => VARCHAR2 [scale] => [not_null] => [binary] => [default_value] => )
adofieldobject Object ( [name] => created [max_length] => 7 [type] => DATE [scale] => [not_null] => [binary] => [default_value] => )

Testing MetaIndexes

----- (oci8po): SELECT * FROM ALL_CONSTRAINTS WHERE UPPER(TABLE_NAME)='ADOXYZ' AND CONSTRAINT_TYPE='P' ----- AAMetaIndexes not supported

Testing MetaPrimaryKeys

----- (oci8po): SELECT /*+ RULE */ distinct b.column_name FROM USER_CONSTRAINTS a , USER_CONS_COLUMNS b WHERE ( UPPER(b.table_name) = ('ADOXYZ')) AND (UPPER(a.table_name) = ('ADOXYZ') and a.constraint_type = 'P') AND (a.constraint_name = b.constraint_name) ----- bool(false) ----- (oci8po): delete from ADOXYZ ----- InParameter($stmt, $php_var='99', $name='id', $maxLen=4000, $type=false); Bind: name = id InParameter($stmt, $php_var='[encoded data, 5010 characters]', $name='xx', $maxLen=-1, $type=113); Bind: name = xx Bind: descriptor has been allocated, var (xx) binded Bind: LOB has been written to temp ----- (oci8po): INSERT INTO photos ( ID, photo) VALUES ( :id, empty_blob() ) RETURNING photo INTO :xx ----- IN LOB: LOB has been saved. Smart Commit occurred ----- (oci8po): select photo from photos where id=99 -----
[encoded data, 5013 characters]

#### Testing Blob: size=5010

----- (oci8po): UPDATE photos set photo=EMPTY_BLOB() WHERE id=1 RETURNING photo INTO ? ----- name=:blob var=Object len=-1 type=113
----- (oci8po): select photo from photos where id=1 -----

#### Testing Clob: size=5010

----- (oci8po): UPDATE photos set descclob=EMPTY_CLOB() WHERE id=1 RETURNING descclob INTO ? ----- name=:blob var=Object len=-1 type=112
----- (oci8po): select descclob from photos where id=1 -----

#### Testing Foreign Keys

----- (oci8po): select constraint_name, r_owner, r_constraint_name from user_constraints where constraint_type = 'R' and table_name = 'EMP' and owner='JURIS10' ----- AABad MetaForeignKeys

#### Testing Cursor Variables

InParameter($stmt, $php_var='', $name='zz', $maxLen=-1, $type=116); ----- (oci8po): BEGIN adodb.open_tab(:zz, 'A%'); END; ----- ----- (oci8po): SELECT count(*) FROM tab where tname like 'A%' ----- Test 1 RowCount: OK

#### Testing Stored Procedures for oci8

InParameter($stmt, $php_var='Malaysia', $name='a1', $maxLen=4000, $type=false); Bind: name = a1 OutParameter($stmt, $php_var='', $name='a2', $maxLen=4000, $type=false); Bind: name = a2 ----- (oci8po): BEGIN adodb.data_out(:a1, :a2); END; ----- OK: a2=Cinta Hati Malaysia

InParameter($stmt, $php_var='A%', $name='tablename', $maxLen=4000, $type=false); Bind: name = tablename ----- (oci8po): select * from tab where tname like :tablename -----

tname | tabtype | clusterid
--- | --- | ---
ABALONE | TABLE |
ABALONE2 | TABLE |
ABALONE2_TREE_JL1 | TABLE |
ABALONE_TREE | TABLE |
ADDRESS | VIEW |
ADODB_LOGSQL | TABLE |
ADOXYZ | TABLE |
ALERTRX | TABLE |
ATEST | TABLE |

InParameter($stmt, $php_var='Malaysia', $name='a1', $maxLen=4000, $type=false); Bind: name = a1 ----- (oci8po): begin adodb.data_in(:a1); end; -----

Testing Bulk Insert of 3 rows

----- (oci8po): insert into ADOXYZ (id, firstname, lastname) values (:0, :1, :2) ----- ----- (oci8po): insert into ADOXYZ (id, firstname, lastname) values (:0, :1, :2) ----- ----- (oci8po): insert into ADOXYZ (id, firstname, lastname) values (:0, :1, :2) ----- Smart Commit occurred ----- (oci8po): select * from ADOXYZ order by id -----

id | firstname | lastname | created
--- | --- | --- | ---
1 | Caroline | Miranda |  |
2 | John | Lim |  |
3 | Wai Hun | See |  |

----- (oci8po): delete from ADOXYZ -----

Inserting 50 rows

----- (oci8po): insert into ADOXYZ (id, firstname, lastname, created) values (0*10+0, ?, ?, TO_DATE('2005-05-19', 'YYYY-MM-DD')) ----- ----- (oci8po): insert into ADOXYZ (id, firstname, lastname, created) values (1*10+0, :0, :1, TO_DATE('2005-05-19', 'YYYY-MM-DD')) ----- ----- (oci8po): insert into ADOXYZ (id, firstname, lastname, created) values (2*10+0, ?, ?, TO_DATE('2005-05-19', 'YYYY-MM-DD')) ----- ----- (oci8po): insert into ADOXYZ (id, firstname, lastname, created) values (3*10+0, :0, :1, TO_DATE('2005-05-19', 'YYYY-MM-DD')) ----- ----- (oci8po): insert into ADOXYZ (id, firstname, lastname, created) values (4*10+0, ?, ?, TO_DATE('2005-05-19', 'YYYY-MM-DD')) ----- ----- (oci8po): select count(*) from ADOXYZ ----- ----- (oci8po): update ADOXYZ set id=id+1 -----

Affected_Rows() passed

----- (oci8po): select id from ADOXYZ where id=? and created>=? -----
Bind date/integer passedBB

RecordCount returns 49

PO_RecordCount returns wrong value: 49

The fields columns cannot be indexed by column name.

Array ( [ID] => 2 [FIRSTNAME] => John [LASTNAME] => Lim [CREATED] => 2005-05-19 [ABS(MOD(DBMS_RANDOM.RANDOM,10000001)/10000000)] => .0233416 )

id | firstname | lastname | created | abs(mod(dbms_random.random,10000001)/10000000)
--- | --- | --- | --- | ---
2 | John | Lim | Thu 19, May 2005 | .0233416
3 | Mary | Lamb | Thu 19, May 2005 | .1240054
4 | George | Washington | Thu 19, May 2005 | .590735
5 | Mr. Alan | Tam | Thu 19, May 2005 | .6431706
6 | Alan | Turing'ton | Thu 19, May 2005 | .1579551
7 | Serena | Williams | Thu 19, May 2005 | .5652993
8 | Yat Sun | Sun | Thu 19, May 2005 | .8400974
9 | Wai Hun | See | Thu 19, May 2005 | .8073812
10 | Steven | Oey | Thu 19, May 2005 | .1340615
11 | Caroline | Miranda | Thu 19, May 2005 | .9993386
12 | John | Lim | Thu 19, May 2005 | .7807507
13 | Mary | Lamb | Thu 19, May 2005 | .1983442
14 | George | Washington | Thu 19, May 2005 | .9449505
15 | Mr. Alan | Tam | Thu 19, May 2005 | .5663581
16 | Alan | Turing'ton | Thu 19, May 2005 | .4669657
17 | Serena | Williams | Thu 19, May 2005 | .6451908
18 | Yat Sun | Sun | Thu 19, May 2005 | .4639599
19 | Wai Hun | See | Thu 19, May 2005 | .4443077
20 | Steven | Oey | Thu 19, May 2005 | .3388362
21 | Caroline | Miranda | Thu 19, May 2005 | .8143343

id | firstname | lastname | created | abs(mod(dbms_random.random,10000001)/10000000)
--- | --- | --- | --- | ---
22 | John | Lim | Thu 19, May 2005 | .972867
23 | Mary | Lamb | Thu 19, May 2005 | .5805857
24 | George | Washington | Thu 19, May 2005 | .0551489
25 | Mr. Alan | Tam | Thu 19, May 2005 | .0705427
26 | Alan | Turing'ton | Thu 19, May 2005 | .4666325
27 | Serena | Williams | Thu 19, May 2005 | .7516632
28 | Yat Sun | Sun | Thu 19, May 2005 | .3328669
29 | Wai Hun | See | Thu 19, May 2005 | .8351005
30 | Steven | Oey | Thu 19, May 2005 | .2147344
31 | Caroline | Miranda | Thu 19, May 2005 | .8954723
32 | John | Lim | Thu 19, May 2005 | .3072688
33 | Mary | Lamb | Thu 19, May 2005 | .2168223
34 | George | Washington | Thu 19, May 2005 | .2349472
35 | Mr. Alan | Tam | Thu 19, May 2005 | .5354307
36 | Alan | Turing'ton | Thu 19, May 2005 | .4541119
37 | Serena | Williams | Thu 19, May 2005 | .6528771
38 | Yat Sun | Sun | Thu 19, May 2005 | .5078207
39 | Wai Hun | See | Thu 19, May 2005 | .2280108
40 | Steven | Oey | Thu 19, May 2005 | .0865263
41 | Caroline | Miranda | Thu 19, May 2005 | .866602

id | firstname | lastname | created | abs(mod(dbms_random.random,10000001)/10000000)
--- | --- | --- | --- | ---
42 | John | Lim | Thu 19, May 2005 | .6110876
43 | Mary | Lamb | Thu 19, May 2005 | .0870814
44 | George | Washington | Thu 19, May 2005 | .8348281
45 | Mr. Alan | Tam | Thu 19, May 2005 | .2111793
46 | Alan | Turing'ton | Thu 19, May 2005 | .2265778
47 | Serena | Williams | Thu 19, May 2005 | .6451763
48 | Yat Sun | Sun | Thu 19, May 2005 | .6077789
49 | Wai Hun | See | Thu 19, May 2005 | .1526813
50 | Steven | Oey | Thu 19, May 2005 | .2569144

GetOne returns ok

GetRow returns ok

FetchObject/FetchNextObject Test

AA

FetchObject/FetchNextObject Test 2

BBArray ( [ID] => 4 [FIRSTNAME] => George [LASTNAME] => Washington [CREATED] => 2005-05-19 )
Notice: Undefined index: id in D:\inetpub\wwwroot\php\phplens\adodb\adodb.inc.php on line 3496
Notice: Undefined index: firstname in D:\inetpub\wwwroot\php\phplens\adodb\adodb.inc.php on line 3496
Notice: Undefined index: lastname in D:\inetpub\wwwroot\php\phplens\adodb\adodb.inc.php on line 3496
Notice: Undefined index: created in D:\inetpub\wwwroot\php\phplens\adodb\adodb.inc.php on line 3496
adofetchobj Object ( [ID] => [FIRSTNAME] => [LASTNAME] => [CREATED] => )

Firstname is not string

CacheSelectLimit Test

/tmp/0d/adodb_0d16f79a74ee76ccabfb86ca1945f9ad.cache reloaded, ttl=2913 [ select * from (SELECT /*+FIRST_ROWS*/ id, firstname from ADOXYZ order by id) where rownum