3 * ADOdb PostgreSQL 6.4 driver
5 * This file is part of ADOdb, a Database Abstraction Layer library for PHP.
8 * @link https://adodb.org Project's web site and documentation
9 * @link https://github.com/ADOdb/ADOdb Source code and issue tracker
11 * The ADOdb Library is dual-licensed, released under both the BSD 3-Clause
12 * and the GNU Lesser General Public Licence (LGPL) v2.1 or, at your option,
13 * any later version. This means you can use it in proprietary products.
14 * See the LICENSE.md file distributed with this source code for details.
15 * @license BSD-3-Clause
16 * @license LGPL-2.1-or-later
18 * @copyright 2000-2013 John Lim
19 * @copyright 2014 Damien Regad, Mark Newnham and the ADOdb community
22 // security - hide paths
23 if (!defined('ADODB_DIR')) die();
25 class ADODB_postgres64
extends ADOConnection
{
26 var $databaseType = 'postgres64';
27 var $dataProvider = 'postgres';
28 var $hasInsertID = true;
29 var $_resultid = false;
30 var $concat_operator='||';
31 var $metaDatabasesSQL = "select datname from pg_database where datname not in ('template0','template1') order by 1";
32 var $metaTablesSQL = "select tablename,'T' from pg_tables where tablename not like 'pg\_%'
33 and tablename not in ('sql_features', 'sql_implementation_info', 'sql_languages',
34 'sql_packages', 'sql_sizing', 'sql_sizing_profiles')
36 select viewname,'V' from pg_views where viewname not like 'pg\_%'";
37 //"select tablename from pg_tables where tablename not like 'pg_%' order by 1";
38 var $isoDates = true; // accepts dates in ISO format
39 var $sysDate = "CURRENT_DATE";
40 var $sysTimeStamp = "CURRENT_TIMESTAMP";
41 var $blobEncodeType = 'C';
42 var $metaColumnsSQL = "SELECT a.attname,t.typname,a.attlen,a.atttypmod,a.attnotnull,a.atthasdef,a.attnum
43 FROM pg_class c, pg_attribute a,pg_type t
44 WHERE relkind in ('r','v') AND (c.relname='%s' or c.relname = lower('%s')) and a.attname not like '....%%'
45 AND a.attnum > 0 AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum";
47 // used when schema defined
48 var $metaColumnsSQL1 = "SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum
49 FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n
50 WHERE relkind in ('r','v') AND (c.relname='%s' or c.relname = lower('%s'))
51 and c.relnamespace=n.oid and n.nspname='%s'
52 and a.attname not like '....%%' AND a.attnum > 0
53 AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum";
55 // get primary key etc -- from Freek Dijkstra
56 var $metaKeySQL = "SELECT ic.relname AS index_name, a.attname AS column_name,i.indisunique AS unique_key, i.indisprimary AS primary_key
57 FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a
58 WHERE bc.oid = i.indrelid AND ic.oid = i.indexrelid
59 AND (i.indkey[0] = a.attnum OR i.indkey[1] = a.attnum OR i.indkey[2] = a.attnum OR i.indkey[3] = a.attnum OR i.indkey[4] = a.attnum OR i.indkey[5] = a.attnum OR i.indkey[6] = a.attnum OR i.indkey[7] = a.attnum)
60 AND a.attrelid = bc.oid AND bc.relname = '%s'";
62 var $hasAffectedRows = true;
63 var $hasLimit = false; // set to true for pgsql 7 only. support pgsql/mysql SELECT * FROM TABLE LIMIT 10
64 // below suggested by Freek Dijkstra
65 var $true = 'TRUE'; // string that represents TRUE for a database
66 var $false = 'FALSE'; // string that represents FALSE for a database
67 var $fmtDate = "'Y-m-d'"; // used by DBDate() as the default date format used by the database
68 var $fmtTimeStamp = "'Y-m-d H:i:s'"; // used by DBTimeStamp as the default timestamp fmt.
69 var $hasMoveFirst = true;
71 var $_genIDSQL = "SELECT NEXTVAL('%s')";
72 var $_genSeqSQL = "CREATE SEQUENCE %s START %s";
73 var $_dropSeqSQL = "DROP SEQUENCE %s";
74 var $metaDefaultsSQL = "SELECT d.adnum as num, d.adsrc as def from pg_attrdef d, pg_class c where d.adrelid=c.oid and c.relname='%s' order by d.adnum";
75 var $random = 'random()'; /// random function
76 var $autoRollback = true; // apparently pgsql does not autorollback properly before php 4.3.4
77 // http://bugs.php.net/bug.php?id=25404
79 var $uniqueIisR = true;
80 var $_bindInputArray = false; // requires postgresql 7.3+ and ability to modify database
81 var $disableBlobs = false; // set to true to disable blob checking, resulting in 2-5% improvement in performance.
83 /** @var int $_pnum Number of the last assigned query parameter {@see param()} */
86 // The last (fmtTimeStamp is not entirely correct:
87 // PostgreSQL also has support for time zones,
88 // and writes these time in this format: "2001-03-01 18:59:26+02".
89 // There is no code for the "+02" time zone information, so I just left that out.
90 // I'm not familiar enough with both ADODB as well as Postgres
91 // to know what the concequences are. The other values are correct (wheren't in 0.94)
95 * Retrieve Server information.
96 * In addition to server version and description, the function also returns
98 * @param bool $detailed If true, retrieve detailed version string (executes
99 * a SQL query) in addition to the version number
100 * @return array|bool Server info or false if version could not be retrieved
101 * e.g. if there is no active connection
103 function ServerInfo($detailed = true)
105 if (empty($this->version
['version'])) {
106 // We don't have a connection, so we can't retrieve server info
107 if (!$this->_connectionID
) {
111 $version = pg_version($this->_connectionID
);
112 $this->version
= array(
113 // If PHP has been compiled with PostgreSQL 7.3 or lower, then
114 // server version is not set so we use pg_parameter_status()
115 // which includes logic to obtain values server_version
116 'version' => isset($version['server'])
118 : pg_parameter_status($this->_connectionID
, 'server_version'),
119 'client' => $version['client'],
120 'description' => null,
123 if ($detailed && $this->version
['description'] === null) {
124 $this->version
['description'] = $this->GetOne('select version()');
127 return $this->version
;
130 function IfNull( $field, $ifNull )
132 return " coalesce($field, $ifNull) ";
135 // get the last id - never tested
136 function pg_insert_id($tablename,$fieldname)
138 $result=pg_query($this->_connectionID
, 'SELECT last_value FROM '. $tablename .'_'. $fieldname .'_seq');
140 $arr = @pg_fetch_row
($result,0);
141 pg_free_result($result);
142 if (isset($arr[0])) return $arr[0];
148 * Warning from http://www.php.net/manual/function.pg-getlastoid.php:
149 * Using a OID as a unique identifier is not generally wise.
150 * Unless you are very careful, you might end up with a tuple having
151 * a different OID if a database must be reloaded.
155 protected function _insertID($table = '', $column = '')
157 if (!is_resource($this->_resultid
) ||
get_resource_type($this->_resultid
) !== 'pgsql result') return false;
158 $oid = pg_last_oid($this->_resultid
);
159 // to really return the id, we need the table and column-name, else we can only return the oid != id
160 return empty($table) ||
empty($column) ?
$oid : $this->GetOne("SELECT $column FROM $table WHERE oid=".(int)$oid);
163 function _affectedrows()
165 if (!is_resource($this->_resultid
) ||
get_resource_type($this->_resultid
) !== 'pgsql result') return false;
166 return pg_affected_rows($this->_resultid
);
173 function BeginTrans()
175 if ($this->transOff
) return true;
176 $this->transCnt +
= 1;
177 return pg_query($this->_connectionID
, 'begin '.$this->_transmode
);
180 function RowLock($tables,$where,$col='1 as adodbignore')
182 if (!$this->transCnt
) $this->BeginTrans();
183 return $this->GetOne("select $col from $tables where $where for update");
186 // returns true/false.
187 function CommitTrans($ok=true)
189 if ($this->transOff
) return true;
190 if (!$ok) return $this->RollbackTrans();
192 $this->transCnt
-= 1;
193 return pg_query($this->_connectionID
, 'commit');
196 // returns true/false
197 function RollbackTrans()
199 if ($this->transOff
) return true;
200 $this->transCnt
-= 1;
201 return pg_query($this->_connectionID
, 'rollback');
204 function MetaTables($ttype=false,$showSchema=false,$mask=false)
206 $info = $this->ServerInfo();
207 if ($info['version'] >= 7.3) {
208 $this->metaTablesSQL
= "
209 select table_name,'T' from information_schema.tables where table_schema not in ( 'pg_catalog','information_schema')
211 select table_name,'V' from information_schema.views where table_schema not in ( 'pg_catalog','information_schema') ";
214 $save = $this->metaTablesSQL
;
215 $mask = $this->qstr(strtolower($mask));
216 if ($info['version']>=7.3)
217 $this->metaTablesSQL
= "
218 select table_name,'T' from information_schema.tables where table_name like $mask and table_schema not in ( 'pg_catalog','information_schema')
220 select table_name,'V' from information_schema.views where table_name like $mask and table_schema not in ( 'pg_catalog','information_schema') ";
222 $this->metaTablesSQL
= "
223 select tablename,'T' from pg_tables where tablename like $mask
225 select viewname,'V' from pg_views where viewname like $mask";
227 $ret = ADOConnection
::MetaTables($ttype,$showSchema);
230 $this->metaTablesSQL
= $save;
237 * Quotes a string to be sent to the database.
239 * Relies on pg_escape_string()
240 * @link https://adodb.org/dokuwiki/doku.php?id=v5:reference:connection:qstr
242 * @param string $s The string to quote
243 * @param bool $magic_quotes This param is not used since 5.21.0.
244 * It remains for backwards compatibility.
246 * @return string Quoted string
248 function qStr($s, $magic_quotes=false)
251 return $s ?
'true' : 'false';
254 if ($this->_connectionID
) {
255 return "'" . pg_escape_string($this->_connectionID
, $s) . "'";
257 return "'" . pg_escape_string($s) . "'";
262 // Format date column in sql string given an input format that understands Y M D
263 function SQLDate($fmt, $col=false)
265 if (!$col) $col = $this->sysTimeStamp
;
266 $s = 'TO_CHAR('.$col.",'";
269 for ($i=0; $i < $len; $i++
) {
327 // handle escape characters...
330 $ch = substr($fmt,$i,1);
332 if (strpos('-/.:;, ',$ch) !== false) $s .= $ch;
333 else $s .= '"'.$ch.'"';
343 * Load a Large Object from a file
344 * - the procedure stores the object id in the table and imports the object using
345 * postgres proprietary blob handling routines
347 * contributed by Mattia Rossi mattia@technologist.com
348 * modified for safe mode by juraj chlebec
350 function UpdateBlobFile($table,$column,$path,$where,$blobtype='BLOB')
352 pg_query($this->_connectionID
, 'begin');
354 $fd = fopen($path,'r');
355 $contents = fread($fd,filesize($path));
358 $oid = pg_lo_create($this->_connectionID
);
359 $handle = pg_lo_open($this->_connectionID
, $oid, 'w');
360 pg_lo_write($handle, $contents);
361 pg_lo_close($handle);
363 // $oid = pg_lo_import ($path);
364 pg_query($this->_connectionID
, 'commit');
365 $rs = ADOConnection
::UpdateBlob($table,$column,$oid,$where,$blobtype);
371 * Deletes/Unlinks a Blob from the database, otherwise it
372 * will be left behind
374 * Returns TRUE on success or FALSE on failure.
376 * contributed by Todd Rogers todd#windfox.net
378 function BlobDelete( $blob )
380 pg_query($this->_connectionID
, 'begin');
381 $result = @pg_lo_unlink
($blob);
382 pg_query($this->_connectionID
, 'commit');
387 Heuristic - not guaranteed to work.
389 function GuessOID($oid)
391 if (strlen($oid)>16) return false;
392 return is_numeric($oid);
396 * If an OID is detected, then we use pg_lo_* to open the oid file and read the
397 * real blob from the db using the oid supplied as a parameter. If you are storing
398 * blobs using bytea, we autodetect and process it so this function is not needed.
400 * contributed by Mattia Rossi mattia@technologist.com
402 * see http://www.postgresql.org/idocs/index.php?largeobjects.html
404 * Since adodb 4.54, this returns the blob, instead of sending it to stdout. Also
405 * added maxsize parameter, which defaults to $db->maxblobsize if not defined.
407 function BlobDecode($blob,$maxsize=false,$hastrans=true)
409 if (!$this->GuessOID($blob)) return $blob;
411 if ($hastrans) pg_query($this->_connectionID
,'begin');
412 $fd = @pg_lo_open
($this->_connectionID
,$blob,'r');
414 if ($hastrans) pg_query($this->_connectionID
,'commit');
417 if (!$maxsize) $maxsize = $this->maxblobsize
;
418 $realblob = @pg_lo_read
($fd,$maxsize);
420 if ($hastrans) pg_query($this->_connectionID
,'commit');
425 * Encode binary value prior to DB storage.
427 * See https://www.postgresql.org/docs/current/static/datatype-binary.html
429 * NOTE: SQL string literals (input strings) must be preceded with two
430 * backslashes due to the fact that they must pass through two parsers in
431 * the PostgreSQL backend.
433 * @param string $blob
435 function BlobEncode($blob)
437 return pg_escape_bytea($this->_connectionID
, $blob);
440 // assumes bytea for blob, and varchar for clob
441 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
443 if ($blobtype == 'CLOB') {
444 return $this->Execute("UPDATE $table SET $column=" . $this->qstr($val) . " WHERE $where");
446 // do not use bind params which uses qstr(), as blobencode() already quotes data
447 return $this->Execute("UPDATE $table SET $column='".$this->BlobEncode($val)."'::bytea WHERE $where");
450 function OffsetDate($dayFraction,$date=false)
452 if (!$date) $date = $this->sysDate
;
453 else if (strncmp($date,"'",1) == 0) {
454 $len = strlen($date);
455 if (10 <= $len && $len <= 12) $date = 'date '.$date;
456 else $date = 'timestamp '.$date;
460 return "($date+interval'".($dayFraction * 1440)." minutes')";
461 #return "($date+interval'$dayFraction days')";
465 * Generate the SQL to retrieve MetaColumns data
466 * @param string $table Table name
467 * @param string $schema Schema name (can be blank)
468 * @return string SQL statement to execute
470 protected function _generateMetaColumnsSQL($table, $schema)
473 return sprintf($this->metaColumnsSQL1
, $table, $table, $schema);
476 return sprintf($this->metaColumnsSQL
, $table, $table, $schema);
480 // for schema support, pass in the $table param "$schema.$tabname".
481 // converts field names to lowercase, $upper is ignored
482 // see PHPLens Issue No: 14018 for more info
483 function MetaColumns($table,$normalize=true)
485 global $ADODB_FETCH_MODE;
489 $this->_findschema($table,$schema);
491 if ($normalize) $table = strtolower($table);
493 $save = $ADODB_FETCH_MODE;
494 $ADODB_FETCH_MODE = ADODB_FETCH_NUM
;
495 if ($this->fetchMode
!== false) $savem = $this->SetFetchMode(false);
497 $rs = $this->Execute($this->_generateMetaColumnsSQL($table, $schema));
498 if (isset($savem)) $this->SetFetchMode($savem);
499 $ADODB_FETCH_MODE = $save;
504 if (!empty($this->metaKeySQL
)) {
505 // If we want the primary keys, we have to issue a separate query
506 // Of course, a modified version of the metaColumnsSQL query using a
507 // LEFT JOIN would have been much more elegant, but postgres does
508 // not support OUTER JOINS. So here is the clumsy way.
510 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC
;
512 $rskey = $this->Execute(sprintf($this->metaKeySQL
,($table)));
513 // fetch all result in once for performance.
514 $keys = $rskey->GetArray();
515 if (isset($savem)) $this->SetFetchMode($savem);
516 $ADODB_FETCH_MODE = $save;
523 if (!empty($this->metaDefaultsSQL
)) {
524 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC
;
525 $sql = sprintf($this->metaDefaultsSQL
, ($table));
526 $rsdef = $this->Execute($sql);
527 if (isset($savem)) $this->SetFetchMode($savem);
528 $ADODB_FETCH_MODE = $save;
531 while (!$rsdef->EOF
) {
532 $num = $rsdef->fields
['num'];
533 $s = $rsdef->fields
['def'];
534 if (strpos($s,'::')===false && substr($s, 0, 1) == "'") { /* quoted strings hack... for now... fixme */
536 $s = substr($s, 0, strlen($s) - 1);
543 ADOConnection
::outp( "==> SQL => " . $sql);
550 $fld = new ADOFieldObject();
551 $fld->name
= $rs->fields
[0];
552 $fld->type
= $rs->fields
[1];
553 $fld->max_length
= $rs->fields
[2];
554 $fld->attnum
= $rs->fields
[6];
556 if ($fld->max_length
<= 0) $fld->max_length
= $rs->fields
[3]-4;
557 if ($fld->max_length
<= 0) $fld->max_length
= -1;
558 if ($fld->type
== 'numeric') {
559 $fld->scale
= $fld->max_length
& 0xFFFF;
560 $fld->max_length
>>= 16;
563 // 5 hasdefault; 6 num-of-column
564 $fld->has_default
= ($rs->fields
[5] == 't');
565 if ($fld->has_default
) {
566 $fld->default_value
= $rsdefa[$rs->fields
[6]];
570 $fld->not_null
= $rs->fields
[4] == 't';
574 if (is_array($keys)) {
575 foreach($keys as $key) {
576 if ($fld->name
== $key['column_name'] AND $key['primary_key'] == 't')
577 $fld->primary_key
= true;
578 if ($fld->name
== $key['column_name'] AND $key['unique_key'] == 't')
579 $fld->unique
= true; // What name is more compatible?
583 if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM
) $retarr[] = $fld;
584 else $retarr[($normalize) ?
strtoupper($fld->name
) : $fld->name
] = $fld;
596 function param($name, $type='C')
599 // Reset parameter number if $name is falsy
601 if ($name === false) {
602 // and don't return placeholder if false (see #380)
607 return '$' . ++
$this->_pnum
;
610 function MetaIndexes ($table, $primary = FALSE, $owner = false)
612 global $ADODB_FETCH_MODE;
615 $this->_findschema($table,$schema);
617 if ($schema) { // requires pgsql 7.3+ - pg_namespace used.
619 SELECT c.relname as "Name", i.indisunique as "Unique", i.indkey as "Columns"
620 FROM pg_catalog.pg_class c
621 JOIN pg_catalog.pg_index i ON i.indexrelid=c.oid
622 JOIN pg_catalog.pg_class c2 ON c2.oid=i.indrelid
624 WHERE (c2.relname=\'%s\' or c2.relname=lower(\'%s\'))
625 and c.relnamespace=c2.relnamespace
626 and c.relnamespace=n.oid
627 and n.nspname=\'%s\'';
630 SELECT c.relname as "Name", i.indisunique as "Unique", i.indkey as "Columns"
631 FROM pg_catalog.pg_class c
632 JOIN pg_catalog.pg_index i ON i.indexrelid=c.oid
633 JOIN pg_catalog.pg_class c2 ON c2.oid=i.indrelid
634 WHERE (c2.relname=\'%s\' or c2.relname=lower(\'%s\'))';
637 if ($primary == FALSE) {
638 $sql .= ' AND i.indisprimary=false;';
641 $save = $ADODB_FETCH_MODE;
642 $ADODB_FETCH_MODE = ADODB_FETCH_NUM
;
643 if ($this->fetchMode
!== FALSE) {
644 $savem = $this->SetFetchMode(FALSE);
647 $rs = $this->Execute(sprintf($sql,$table,$table,$schema));
649 $this->SetFetchMode($savem);
651 $ADODB_FETCH_MODE = $save;
653 if (!is_object($rs)) {
658 $col_names = $this->MetaColumnNames($table,true,true);
659 // 3rd param is use attnum,
660 // see https://sourceforge.net/p/adodb/bugs/45/
662 while ($row = $rs->FetchRow()) {
664 foreach (explode(' ', $row[2]) as $col) {
665 $columns[] = $col_names[$col];
668 $indexes[$row[0]] = array(
669 'unique' => ($row[1] == 't'),
670 'columns' => $columns
677 * Connect to a database.
680 * $db->Connect("host=host1 user=user1 password=secret port=4341");
681 * $db->Connect('host1:4341', 'user1', 'secret');
683 * @param string $str pg_connect() Connection string or Hostname[:port]
684 * @param string $user (Optional) The username to connect as.
685 * @param string $pwd (Optional) The password to connect with.
686 * @param string $db (Optional) The name of the database to start in when connected.
687 * @param int $ctype Connection type
688 * @return bool|null True if connected successfully, false if connection failed, or
689 * null if the PostgreSQL extension is not loaded.
691 function _connect($str, $user='', $pwd='', $db='', $ctype=0)
693 if (!function_exists('pg_connect')) {
697 $this->_errorMsg
= false;
699 // If $user, $pwd and $db are all null, then $str is a pg_connect()
700 // connection string. Otherwise we expect it to be a hostname,
701 // with optional port separated by ':'
702 if ($user ||
$pwd ||
$db) {
705 $host = explode(':', $str);
707 $conn['host'] = $host[0];
709 if (isset($host[1])) {
710 $conn['port'] = (int)$host[1];
711 } elseif (!empty($this->port
)) {
712 $conn['port'] = $this->port
;
715 $conn['user'] = $user;
716 $conn['password'] = $pwd;
717 // @TODO not sure why we default to 'template1', pg_connect() uses the username when dbname is empty
718 $conn['dbname'] = $db ?
: 'template1';
720 // Generate connection string
722 foreach ($conn as $param => $value) {
723 // Escaping single quotes and backslashes per pg_connect() documentation
724 $str .= $param . "='" . addcslashes($value, "'\\") . "' ";
728 if ($ctype === 1) { // persistent
729 $this->_connectionID
= pg_pconnect($str);
731 if ($ctype === -1) { // nconnect, we trick pgsql ext by changing the connection str
734 if (empty($ncnt)) $ncnt = 1;
737 $str .= str_repeat(' ',$ncnt);
739 $this->_connectionID
= pg_connect($str);
741 if ($this->_connectionID
=== false) return false;
742 $this->Execute("set datestyle='ISO'");
744 $info = $this->ServerInfo(false);
746 if (version_compare($info['version'], '7.1', '>=')) {
747 $this->_nestedSQL
= true;
750 # PostgreSQL 9.0 changed the default output for bytea from 'escape' to 'hex'
751 # PHP does not handle 'hex' properly ('x74657374' is returned as 't657374')
752 # https://bugs.php.net/bug.php?id=59831 states this is in fact not a bug,
753 # so we manually set bytea_output
754 if (!empty($this->connection
->noBlobs
)
755 && version_compare($info['version'], '9.0', '>=')
756 && version_compare($info['client'], '9.2', '<')
758 $this->Execute('set bytea_output=escape');
764 function _nconnect($argHostname, $argUsername, $argPassword, $argDatabaseName)
766 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabaseName,-1);
769 // returns true or false
772 // $db->PConnect("host=host1 user=user1 password=secret port=4341");
773 // $db->PConnect('host1','user1','secret');
774 function _pconnect($str,$user='',$pwd='',$db='')
776 return $this->_connect($str,$user,$pwd,$db,1);
780 // returns queryID or false
781 function _query($sql,$inputarr=false)
784 $this->_errorMsg
= false;
787 It appears that PREPARE/EXECUTE is slower for many queries.
789 For query executed 1000 times:
790 "select id,firstname,lastname from adoxyz
791 where firstname not like ? and lastname not like ? and id = ?"
793 with plan = 1.51861286163 secs
794 no plan = 1.26903700829 secs
796 $plan = 'P'.md5($sql);
799 foreach($inputarr as $v) {
800 if ($execp) $execp .= ',';
802 if (strncmp($v,"'",1) !== 0) $execp .= $this->qstr($v);
808 if ($execp) $exsql = "EXECUTE $plan ($execp)";
809 else $exsql = "EXECUTE $plan";
812 $rez = @pg_execute
($this->_connectionID
,$exsql);
814 # Perhaps plan does not exist? Prepare/compile plan.
816 foreach($inputarr as $v) {
817 if ($params) $params .= ',';
819 $params .= 'VARCHAR';
820 } else if (is_integer($v)) {
821 $params .= 'INTEGER';
826 $sqlarr = explode('?',$sql);
830 foreach($sqlarr as $v) {
834 $s = "PREPARE $plan ($params) AS ".substr($sql,0,strlen($sql)-2);
836 $rez = pg_execute($this->_connectionID
,$s);
837 //echo $this->ErrorMsg();
840 $rez = pg_execute($this->_connectionID
,$exsql);
843 $rez = pg_query($this->_connectionID
,$sql);
845 // check if no data returned, then no need to create real recordset
846 if ($rez && pg_num_fields($rez) <= 0) {
847 if (is_resource($this->_resultid
) && get_resource_type($this->_resultid
) === 'pgsql result') {
848 pg_free_result($this->_resultid
);
850 $this->_resultid
= $rez;
857 function _errconnect()
859 if (defined('DB_ERROR_CONNECT_FAILED')) return DB_ERROR_CONNECT_FAILED
;
860 else return 'Database connection failed';
863 /* Returns: the last error message from previous database operation */
866 if ($this->_errorMsg
!== false) {
867 return $this->_errorMsg
;
870 if (!empty($this->_resultid
)) {
871 $this->_errorMsg
= @pg_result_error
($this->_resultid
);
872 if ($this->_errorMsg
) {
873 return $this->_errorMsg
;
877 if (!empty($this->_connectionID
)) {
878 $this->_errorMsg
= @pg_last_error
($this->_connectionID
);
880 $this->_errorMsg
= $this->_errconnect();
883 return $this->_errorMsg
;
888 $e = $this->ErrorMsg();
890 return ADOConnection
::MetaError($e);
895 // returns true or false
898 if ($this->transCnt
) $this->RollbackTrans();
899 if ($this->_resultid
) {
900 @pg_free_result
($this->_resultid
);
901 $this->_resultid
= false;
903 @pg_close
($this->_connectionID
);
904 $this->_connectionID
= false;
910 * Maximum size of C field
914 return 1000000000; // should be 1 Gb?
918 * Maximum size of X field
922 return 1000000000; // should be 1 Gb?
928 /*--------------------------------------------------------------------------------------
929 Class Name: Recordset
930 --------------------------------------------------------------------------------------*/
932 class ADORecordSet_postgres64
extends ADORecordSet
{
934 var $databaseType = "postgres64";
937 function __construct($queryID, $mode=false)
939 if ($mode === false) {
940 global $ADODB_FETCH_MODE;
941 $mode = $ADODB_FETCH_MODE;
945 case ADODB_FETCH_NUM
: $this->fetchMode
= PGSQL_NUM
; break;
946 case ADODB_FETCH_ASSOC
:$this->fetchMode
= PGSQL_ASSOC
; break;
948 case ADODB_FETCH_DEFAULT
:
949 case ADODB_FETCH_BOTH
:
950 default: $this->fetchMode
= PGSQL_BOTH
; break;
952 $this->adodbFetchMode
= $mode;
954 // Parent's constructor
955 parent
::__construct($queryID);
958 function GetRowAssoc($upper = ADODB_ASSOC_CASE
)
960 if ($this->fetchMode
== PGSQL_ASSOC
&& $upper == ADODB_ASSOC_CASE_LOWER
) {
961 return $this->fields
;
963 $row = ADORecordSet
::GetRowAssoc($upper);
969 global $ADODB_COUNTRECS;
970 $qid = $this->_queryID
;
971 $this->_numOfRows
= ($ADODB_COUNTRECS)? @pg_num_rows
($qid):-1;
972 $this->_numOfFields
= @pg_num_fields
($qid);
974 // cache types for blob decode check
975 // apparently pg_field_type actually performs an sql query on the database to get the type.
976 if (empty($this->connection
->noBlobs
))
977 for ($i=0, $max = $this->_numOfFields
; $i < $max; $i++
) {
978 if (pg_field_type($qid,$i) == 'bytea') {
979 $this->_blobArr
[$i] = pg_field_name($qid,$i);
984 function fields($colname)
986 if ($this->fetchMode
!= PGSQL_NUM
) {
987 return @$this->fields
[$colname];
991 $this->bind
= array();
992 for ($i=0; $i < $this->_numOfFields
; $i++
) {
993 $o = $this->FetchField($i);
994 $this->bind
[strtoupper($o->name
)] = $i;
997 return $this->fields
[$this->bind
[strtoupper($colname)]];
1000 function fetchField($fieldOffset = 0)
1002 // offsets begin at 0
1004 $o = new ADOFieldObject();
1005 $o->name
= @pg_field_name
($this->_queryID
, $fieldOffset);
1006 $o->type
= @pg_field_type
($this->_queryID
, $fieldOffset);
1007 $o->max_length
= @pg_field_size
($this->_queryID
, $fieldOffset);
1011 function _seek($row)
1013 return @pg_fetch_row
($this->_queryID
,$row);
1016 function _decode($blob)
1018 if ($blob === NULL) return NULL;
1019 // eval('$realblob="'.str_replace(array('"','$'),array('\"','\$'),$blob).'";');
1020 return pg_unescape_bytea($blob);
1023 function _fixblobs()
1025 if ($this->fetchMode
== PGSQL_NUM ||
$this->fetchMode
== PGSQL_BOTH
) {
1026 foreach($this->_blobArr
as $k => $v) {
1027 $this->fields
[$k] = ADORecordSet_postgres64
::_decode($this->fields
[$k]);
1030 if ($this->fetchMode
== PGSQL_ASSOC ||
$this->fetchMode
== PGSQL_BOTH
) {
1031 foreach($this->_blobArr
as $k => $v) {
1032 $this->fields
[$v] = ADORecordSet_postgres64
::_decode($this->fields
[$v]);
1037 // 10% speedup to move MoveNext to child class
1041 $this->_currentRow++
;
1042 if ($this->_numOfRows
< 0 ||
$this->_numOfRows
> $this->_currentRow
) {
1043 $this->fields
= @pg_fetch_array
($this->_queryID
,$this->_currentRow
,$this->fetchMode
);
1044 if (is_array($this->fields
) && $this->fields
) {
1045 if (isset($this->_blobArr
)) $this->_fixblobs();
1049 $this->fields
= false;
1058 if ($this->_currentRow
>= $this->_numOfRows
&& $this->_numOfRows
>= 0)
1061 $this->fields
= @pg_fetch_array
($this->_queryID
,$this->_currentRow
,$this->fetchMode
);
1063 if ($this->fields
&& isset($this->_blobArr
)) $this->_fixblobs();
1065 return (is_array($this->fields
));
1070 if (!is_resource($this->_queryID
)
1071 ||
get_resource_type($this->_queryID
) != 'pgsql result'
1075 return pg_free_result($this->_queryID
);
1078 function MetaType($t,$len=-1,$fieldobj=false)
1080 if (is_object($t)) {
1082 $t = $fieldobj->type
;
1083 $len = $fieldobj->max_length
;
1085 switch (strtoupper($t)) {
1086 case 'MONEY': // stupid, postgres expects money to be a string
1097 if ($len <= $this->blobSize
) return 'C';
1102 case 'IMAGE': // user defined type
1103 case 'BLOB': // user defined type
1104 case 'BIT': // This is a bit string, not a single bit, so don't return 'L'
1117 case 'TIMESTAMP WITHOUT TIME ZONE':
1130 if (isset($fieldobj) &&
1131 empty($fieldobj->primary_key
) && (!$this->connection
->uniqueIisR ||
empty($fieldobj->unique
))) return 'I';
1138 return ADODB_DEFAULT_METATYPE
;