Re: Statement Handle Attribute Precision

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 <[email protected] >
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.