Re: Statement Handle Attribute Precision

[Josh Nijenhuis]

CREATE TABLE `testprecision` (   `recid` int(10) unsigned NOT NULL AUTO_INCREMENT,   `charnumber` char(10) NOT NULL,   `desription` varchar(35) NOT NULL   PRIMARY KEY (`recid`), ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MariaDB [information_schema]> SELECT * FROM COLUMNS WHERE TABLE_NAME='testprecision'; +---------------+--------------+---------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-------------------+------------------+------------+----------------+---------------------------------+----------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME    | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME    | COLUMN_TYPE      | COLUMN_KEY | EXTRA          | PRIVILEGES                      | COLUMN_COMMENT | +---------------+--------------+---------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-------------------+------------------+------------+----------------+---------------------------------+----------------+ | def           | devtest  | testprecision | recid       |                1 | NULL           | NO          | int       |                     NULL |                   NULL |                10 |             0 |               NULL | NULL               | NULL              | int(10) unsigned | PRI        | auto_increment | select,insert,update,references |                | | def           | devtest  | testprecision | charnumber  |                2 | NULL           | NO          | char      |                       10 |                     10 |              NULL |          NULL |               NULL | latin1             | latin1_swedish_ci | char(10)         |            |                | select,insert,update,references |                | | def           | devtest  | testprecision | desription  |                3 | NULL           | NO          | varchar   |                       35 |                     35 |              NULL |          NULL |               NULL | latin1             | latin1_swedish_ci | varchar(35)      |            |                | select,insert,update,references |                | +---------------+--------------+---------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-------------------+------------------+------------+----------------+---------------------------------+----------------+ #! /usr/bin/perl use strict; use DBI; use DBI qw(:sql_types); my (%l_attr) = ( PrintError => 1, RaiseError => 0 ); my $l_dbi = "dbi:mysql:;mysql_read_default_file=/etc/mysql/dbtest.cnf"; my $l_dbh = DBI->connect ($l_dbi, undef, undef, \%l_attr)     or return (0,"sql","Error connecting to dbtest"); my $l_sql = "SELECT * FROM testprecision WHERE 1=0 "; my $l_sth = $l_dbh->prepare($l_sql); $l_sth->execute (); my $lar_colnames = $l_sth->{'NAME'}; my $lar_coltypes = $l_sth->{'TYPE'}; my $lar_collens = $l_sth->{'PRECISION'}; my $lar_coldecs = $l_sth->{'SCALE'}; $l_sth->finish (); my $l_offset = 0; foreach (@{$lar_colnames}) {     warn "Column name: $_, Column type: $$lar_coltypes[$l_offset], Column precision: $$lar_collens[$l_offset], Column scale: $$lar_coldecs[$l_offset]";     $l_offset++; } $l_dbh->disconnect(); On 01/21/15 01:30, Michiel Beijen wrote: Can you maybe create a small sample script? -- Michiel Op dinsdag 20 januari 2015 heeft Josh Nijenhuis <j...@nijenhuis.ca> het volgende geschreven: Using mariadb 10 series, Perl 5.18 DBI 1.623 DBD-mysql 4.20 I seem to be getting 3 byte 'PRECISION' back on the statement handle for CHAR and VARCHAR field, so if its supposed to be 35 character length, we get 105 back, before some updates to our server it was returning 35 byte; so 1byte per character. Have checked that the server, table and column are all indeed latin1 and not UTF-8, even checked mariadb information_schema.COLUMNS and it says CHARACTER_OCTET_LENGTH=35 for that column. was trying to figure out how DBI gets the information to populate PRECISION? Any help would be appreciated.