note 89827 added to ref.pdo-dblib
| From: | tris+php at tfconsulting dot com dot au | Date: | Wed, 25 Mar 2009 03:03:49 +0000 |
| Subject: | note 89827 added to ref.pdo-dblib | ||
| Groups: | php.notes | ||
| Request: | Send a blank email to php-notes+get-152411@lists.php.net to get a copy of this message | ||
If you are struggling with
'Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using
DB-Library (such as ISQL) or ODBC version 3.7 or earlier.'
when trying to do 'SELECT *' queries:
Obviously the best way to deal with this is change your unicode fields to the ascii equivalent
(ntext > text), but if that is not an option here is an implementation based on the info at http://www.rochester.edu/IT/web/WebHelp/mssql/limitations.html
which checks the data types of the fields and casts them as neccesary.
<?php
class mssql {
private static $statement=null;
private static $typemap=array(
'ntext' => 'text',
'bigint' => 'real',
'decimal' => 'real',
'float' => 'real',
'numeric' => 'real'
);
public static function all_fields($table) {
if(self::$statement==null) {
$db=pdodb::instance(); // or however you get your global instance
$query="SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME=?";
self::$statement=$db->prepare($query);
}
self::$statement->execute(array($table));
$fields=array();
$need_cast=false;
while($field=self::$statement->fetch()) {
$field_quoted=self::quote_field($field['COLUMN_NAME']);
if(isset(self::$typemap[$field['DATA_TYPE']])) {
$fields[]='CAST('.$field_quoted.' AS
'.self::$typemap[$field['DATA_TYPE']].') AS '.$field_quoted;
$need_cast=true;
} else $fields[]=$field_quoted;
}
return ($need_cast) ? implode(', ',$fields) : '*';
}
public static function quote_field($field) {
$pos=strpos($field,' ');
return ($pos===false) ? $field : '['.$field.']';
}
}
$db=pdodb:instance(); // singleton PDO instance
$stmt=$db->prepare('SELECT '.mssql::all_fields('My_Table').' FROM
My_Table');
$stmt->execute();
...
?>
Essentially mssql::all_fields($table) returns '*' if it can, otherwise the fields listed
with the relevant casts in place e.g. 'NoteIdent, Owner, CAST(Note AS text) AS Note'
----
Server IP: 92.48.74.199
Probable Submitter: 217.155.213.85
----
Manual Page -- http://www.php.net/manual/en/ref.pdo-dblib.php
Edit -- https://master.php.net/note/edit/89827
Del: integrated -- https://master.php.net/note/delete/89827/integrated
Del: useless -- https://master.php.net/note/delete/89827/useless
Del: bad code -- https://master.php.net/note/delete/89827/bad+code
Del: spam -- https://master.php.net/note/delete/89827/spam
Del: non-english -- https://master.php.net/note/delete/89827/non-english
Del: in docs -- https://master.php.net/note/delete/89827/in+docs
Del: other reasons-- https://master.php.net/note/delete/89827
Reject -- https://master.php.net/note/reject/89827
Search -- https://master.php.net/manage/user-notes.php