headers
John ORourke | 2 Jul 2007 14:08

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 <at> m.gmane.org
Permalink | Reply | 6 comments |
headers
Paul DuBois | 6 Jul 2007 02:18
Picon
Favicon

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
(Continue reading)

Permalink | Reply | 5 comments |
headers
John ORourke | 6 Jul 2007 07:59

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 <at> m.gmane.org
Permalink | Reply | 4 comments |
headers
Paul DuBois | 6 Jul 2007 15:30
Picon
Favicon

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

--

--
(Continue reading)

Permalink | Reply | 3 comments |
headers
Patrick Galbraith | 6 Jul 2007 16:17

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
(Continue reading)

Permalink | Reply | 2 comments |
headers
Paul DuBois | 6 Jul 2007 16:37
Picon
Favicon

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.
>>>
(Continue reading)

Permalink | Reply | 1 comment |
headers
Patrick Galbraith | 6 Jul 2007 16:44

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:
>>>
(Continue reading)

Permalink | Reply | 0 comments |
headers
Aruni Alahakone | 7 Jul 2007 09:04
Picon
Favicon

Problem!!

Hi,

I want to convert mysql tables to XML, and therefore
downloaded some perl modules. But i'm confused on how
to install them and use them and how to execute code
(use DBI, use strict etc). I would really appretiate f
you could send me the step by step procedure to
install Perl, connect to mysql and perform the xml
conversion. I have never used perl before, so I'm not
very familiar with it. 

Thank you very much..
Aruni Alahakone.. 

 
____________________________________________________________________________________
Sucker-punch spam with award-winning protection. 
Try the free Yahoo! Mail Beta.
http://advision.webevents.yahoo.com/mailbeta/features_spam.html

--

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules <at> m.gmane.org
Permalink | Reply | 1 comment |
headers
Jake Peavy | 8 Jul 2007 01:15
Picon

Re: Problem!!

On 7/7/07, Aruni Alahakone <teddy_aua <at> yahoo.com> wrote:
>
> Hi,
>
> I want to convert mysql tables to XML, and therefore
> downloaded some perl modules. But i'm confused on how
> to install them and use them and how to execute code
> (use DBI, use strict etc). I would really appretiate f
> you could send me the step by step procedure to
> install Perl, connect to mysql and perform the xml
> conversion. I have never used perl before, so I'm not
> very familiar with it.
>

http://www.perl.com/pub/a/1999/10/DBI.html
http://dbi.perl.org/docs/

but all of that is totally unnecessary.

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_xml

--

-- 
-jp

If you try to introduce your mother to Chuck Norris, she'll introduce you to
your biological father.
Permalink | Reply | 0 comments |
headers
Patrick Galbraith | 23 Jul 2007 14:26

Re: Problem with Perl DBD::Mysql module tests

Paul,

Thank you for your patch! I'll test it out and include it in the next 
release of DBD::mysql.

regards,

Patrick

Paul Wayper wrote:

>Hi there!
>
>I recently had to install the Perl DBD::Mysql module from CPAN (using
>perl -MCPAN -e shell).  As a good administrator I have changed the root
>MySQL password, and this causes the make tests to fail.  With a little
>digging around in t/lib.pm I found that if I set the environment
>variable DBI_PASS and run the tests it would import it.  However, this
>did not work and the $::test_password variable remained blank.  Further
>digging around found that, in the import of t/mysql.mtest it overwrites
>all the variables that have been previously set.  I made a couple of
>changes to the mtest file to preserve any previously set variables and
>include the diff here:
>
>--- ../DBD-mysql-4.005-MXzdoi/t/mysql.mtest     2007-07-20
>17:31:37.000000000 +1000
>+++ t/mysql.mtest       2007-07-23 10:53:54.000000000 +1000
> <at>  <at>  -12,13 +12,13  <at>  <at> 
>          'testpassword' => '',
>          'testsocket' => ''
(Continue reading)

Permalink | Reply | 0 comments |

Gmane