DBD::mysql retrieving column collation

Hi folks,

I've written a schema sync script which allows me to make schema updates
on multiple servers without disturbing data, and I've just added the
ability to check column types but can't see what the collation type is
from a DBD::mysql statement handle!

Is it possible to retrieve a column's collation from a statement handle?

I need to know if a column is binary or not - eg. 'varchar(255) binary'
or just 'varchar(255)'

I'm about to resort to parsing the output of 'show create table' as I
can't see any other way!

cheers
John


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql retrieving column collation

At 1:08 PM +0100 7/2/07, John ORourke wrote:
>Hi folks,
>
>I've written a schema sync script which allows me to make schema
>updates on multiple servers without disturbing data, and I've just
>added the ability to check column types but can't see what the
>collation type is from a DBD::mysql statement handle!
>
>Is it possible to retrieve a column's collation from a statement handle?
>
>I need to know if a column is binary or not - eg. 'varchar(255)
>binary' or just 'varchar(255)'
>
>I'm about to resort to parsing the output of 'show create table' as
>I can't see any other way!

In the C API, you can distinguish binary from non-binary strings by
checking whether the charset_nr value is 63 (binary) or <> 63 (non-binary).
However, I don't think that DBD::mysql exposes this member of the column
metadata structures.

If you resort to parsing SHOW output, you might find the output of
SHOW COLUMNS easier to deal with, because it's in row-and-column
format. SHOW FULL COLUMNS displays an additional Collation column,
which is non-NULL for non-binary string columns. (For binary string
data types such as BINARY or VARBINARY or BLOB, it's NULL.)

Example:

mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`c1` char(10) DEFAULT NULL,
`c2` char(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
`c3` binary(10) DEFAULT NULL,
`t` text,
`b` blob
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show full columns from t\G
*************************** 1. row ***************************
Field: c1
Type: char(10)
Collation: latin1_swedish_ci
Null: YES
Key:
Default: NULL
Extra:
Privileges: select,insert,update,references
Comment:
*************************** 2. row ***************************
Field: c2
Type: char(10)
Collation: latin1_bin
Null: YES
Key:
Default: NULL
Extra:
Privileges: select,insert,update,references
Comment:
*************************** 3. row ***************************
Field: c3
Type: binary(10)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Privileges: select,insert,update,references
Comment:
*************************** 4. row ***************************
Field: t
Type: text
Collation: latin1_swedish_ci
Null: YES
Key:
Default: NULL
Extra:
Privileges: select,insert,update,references
Comment:
*************************** 5. row ***************************
Field: b
Type: blob
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Privileges: select,insert,update,references
Comment:
5 rows in set (0.05 sec)

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql retrieving column collation

Paul DuBois wrote:
> If you resort to parsing SHOW output, you might find the output of
> SHOW COLUMNS easier to deal with, because it's in row-and-column
> format. SHOW FULL COLUMNS displays an additional Collation column,
Thanks! I wasn't aware of SHOW FULL COLUMNS.

However I'm now successfully using the new information_schema database
like this:

"select data_type,collation_name from information_schema.columns where
table_schema=? and table_name=? and column_name=?"

Then if collation_name =~/bin/ it's a binary column.

cheers
John


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql retrieving column collation

At 6:59 AM +0100 7/6/07, John ORourke wrote:
>Paul DuBois wrote:
>>If you resort to parsing SHOW output, you might find the output of
>>SHOW COLUMNS easier to deal with, because it's in row-and-column
>>format. SHOW FULL COLUMNS displays an additional Collation column,
>Thanks! I wasn't aware of SHOW FULL COLUMNS.
>
>However I'm now successfully using the new information_schema
>database like this:
>
>"select data_type,collation_name from information_schema.columns
>where table_schema=? and table_name=? and column_name=?"
>
>Then if collation_name =~/bin/ it's a binary column.

Yes, you can use information_schema if you can assume MySQL 5.0
or higher. But your rule for determining binary string columns
is incorrect.

collation_name =~/bin/ is a non-binary column with a binary *collation*.
Not the same thing as a binary string. Binary strings have
a data type of binary, varbinary, or blob, and the collation
is NULL.

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql retrieving column collation

I don't know offhand if with DBI, you should be able to get this info.
It seems pretty easy to add.

regards,

Patrick

Paul DuBois wrote:

> At 6:59 AM +0100 7/6/07, John ORourke wrote:
>
>> Paul DuBois wrote:
>>
>>> If you resort to parsing SHOW output, you might find the output of
>>> SHOW COLUMNS easier to deal with, because it's in row-and-column
>>> format. SHOW FULL COLUMNS displays an additional Collation column,
>>
>> Thanks! I wasn't aware of SHOW FULL COLUMNS.
>>
>> However I'm now successfully using the new information_schema
>> database like this:
>>
>> "select data_type,collation_name from information_schema.columns
>> where table_schema=? and table_name=? and column_name=?"
>>
>> Then if collation_name =~/bin/ it's a binary column.
>
>
> Yes, you can use information_schema if you can assume MySQL 5.0
> or higher. But your rule for determining binary string columns
> is incorrect.
>
> collation_name =~/bin/ is a non-binary column with a binary *collation*.
> Not the same thing as a binary string. Binary strings have
> a data type of binary, varbinary, or blob, and the collation
> is NULL.
>


--
Patrick Galbraith, Senior Programmer
Grazr - Easy feed grazing and sharing
http://www.grazr.com

Satyam Eva Jayate - Truth Alone Triumphs
Mundaka Upanishad




--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql retrieving column collation

At 5:17 PM +0300 7/6/07, Patrick Galbraith wrote:
>I don't know offhand if with DBI, you should be able to get this
>info. It seems pretty easy to add.

Presumably it would have to be a mysql_specific statement handle attribute,
so it would need a mysql_ prefix. Perhaps $sth->{mysql_charset_nr}?
Oops, I just checked mysql.h and there is no underscore, so it
would be $sth->{mysql_charsetnr}.

If you're thinking along these lines, there are probably several other
MYSQL_FIELD members that could be exposed. :-) I guess that would
impact performance a bit, though.


>
>regards,
>
>Patrick
>
>Paul DuBois wrote:
>
>>At 6:59 AM +0100 7/6/07, John ORourke wrote:
>>
>>>Paul DuBois wrote:
>>>
>>>>If you resort to parsing SHOW output, you might find the output of
>>>>SHOW COLUMNS easier to deal with, because it's in row-and-column
>>>>format. SHOW FULL COLUMNS displays an additional Collation column,
>>>
>>>Thanks! I wasn't aware of SHOW FULL COLUMNS.
>>>
>>>However I'm now successfully using the new information_schema
>>>database like this:
>>>
>>>"select data_type,collation_name from information_schema.columns
>>>where table_schema=? and table_name=? and column_name=?"
>>>
>>>Then if collation_name =~/bin/ it's a binary column.
>>
>>
>>Yes, you can use information_schema if you can assume MySQL 5.0
>>or higher. But your rule for determining binary string columns
>>is incorrect.
>>
>>collation_name =~/bin/ is a non-binary column with a binary *collation*.
>>Not the same thing as a binary string. Binary strings have
>>a data type of binary, varbinary, or blob, and the collation
>>is NULL.
>>
>
>
>--
>Patrick Galbraith, Senior Programmer Grazr - Easy feed grazing and sharing
>http://www.grazr.com
>Satyam Eva Jayate - Truth Alone Triumphs
>Mundaka Upanishad


--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql retrieving column collation

Paul DuBois wrote:

> At 5:17 PM +0300 7/6/07, Patrick Galbraith wrote:
>
>> I don't know offhand if with DBI, you should be able to get this
>> info. It seems pretty easy to add.
>
>
> Presumably it would have to be a mysql_specific statement handle
> attribute,
> so it would need a mysql_ prefix. Perhaps $sth->{mysql_charset_nr}?
> Oops, I just checked mysql.h and there is no underscore, so it
> would be $sth->{mysql_charsetnr}.
>
> If you're thinking along these lines, there are probably several other
> MYSQL_FIELD members that could be exposed. :-) I guess that would
> impact performance a bit, though.
>
Yeah, though I can see this tidbit of info useful - I think I've had the
occasion where I also needed it.


>>
>> regards,
>>
>> Patrick
>>
>> Paul DuBois wrote:
>>
>>> At 6:59 AM +0100 7/6/07, John ORourke wrote:
>>>
>>>> Paul DuBois wrote:
>>>>
>>>>> If you resort to parsing SHOW output, you might find the output of
>>>>> SHOW COLUMNS easier to deal with, because it's in row-and-column
>>>>> format. SHOW FULL COLUMNS displays an additional Collation column,
>>>>
>>>>
>>>> Thanks! I wasn't aware of SHOW FULL COLUMNS.
>>>>
>>>> However I'm now successfully using the new information_schema
>>>> database like this:
>>>>
>>>> "select data_type,collation_name from information_schema.columns
>>>> where table_schema=? and table_name=? and column_name=?"
>>>>
>>>> Then if collation_name =~/bin/ it's a binary column.
>>>
>>>
>>>
>>> Yes, you can use information_schema if you can assume MySQL 5.0
>>> or higher. But your rule for determining binary string columns
>>> is incorrect.
>>>
>>> collation_name =~/bin/ is a non-binary column with a binary
>>> *collation*.
>>> Not the same thing as a binary string. Binary strings have
>>> a data type of binary, varbinary, or blob, and the collation
>>> is NULL.
>>>
>>
>>
>> --
>> Patrick Galbraith, Senior Programmer Grazr - Easy feed grazing and
>> sharing
>> http://www.grazr.com
>> Satyam Eva Jayate - Truth Alone Triumphs
>> Mundaka Upanishad
>
>
>


--
Patrick Galbraith, Senior Programmer
Grazr - Easy feed grazing and sharing
http://www.grazr.com

Satyam Eva Jayate - Truth Alone Triumphs
Mundaka Upanishad




--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org