Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Re^4: Distiguishing arguments: number-strings vs real integer

by LanX (Archbishop)
on Aug 10, 2018 at 14:02 UTC ( #1220196=note: print w/replies, xml ) Need Help??


in reply to Re^3: Distiguishing arguments: number-strings vs real integer
in thread Distiguishing arguments: number-strings vs real integer

> Isn't irrelevant what Perl thinks of a variable's type when DB already knows/was told what the type of that column should be at create time?

some testing on MariaDB

CREATE TABLE `t_test_type` ( `f_num` INT(11) NULL DEFAULT NULL, `f_str` CHAR(50) NULL DEFAULT NULL ) ENGINE=MyISAM ; select * from t_test_type; 1;1 2;2 3;3 select * from t_test_type where f_str < "2.0"; 1;1 2;2 select * from t_test_type where f_str < 2.0; 1;1

Cheers Rolf
(addicted to the Perl Programming Language :)
Wikisyntax for the Monastery FootballPerl is like chess, only without the dice

Replies are listed 'Best First'.
Re^5: Distiguishing arguments: number-strings vs real integer
by mr_ron (Hermit) on Aug 12, 2018 at 00:28 UTC
    > Isn't irrelevant what Perl thinks of a variable's type when DB already knows/was told what the type of that column should be at create time? some testing on MariaDB
    CREATE TABLE `t_test_type` ( `f_num` INT(11) NULL DEFAULT NULL, `f_str` CHAR(50) NULL DEFAULT NULL ) ENGINE=MyISAM ;
    Ron

    It looks like you could get what you want from the ansi information_schema and a query like the one below that worked for me under MySQL. I have read that not all DBMS support information_schema.

    > SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 't_test_type' AND column_name IN ('f_num', 'f_str') +; +-------------+-----------+ | column_name | data_type | +-------------+-----------+ | f_num | int | | f_str | char | +-------------+-----------+ 2 rows in set (0.00 sec)

    DBI has a column_info method that seems to guarantee DATA_TYPE code and TYPE_NAME fields, if implemented by the driver which should be true for MariaDB based (only) on looking at the source.

    #!/usr/bin/env perl use warnings; use strict; use DBI; my $dsn = "DBI:mysql:database=mysql"; my $dbh = DBI->connect($dsn, ... , #user name and password { RaiseError => 1 } ) or die $DBI::errstr; my $info = $dbh->column_info( undef, 'mysql', 't_test_type', '%' )->fetchall_hashref('COLUMN_NAME'); while (my ($col, $col_info) = each %$info) { print "$col $col_info->{ DATA_TYPE } $col_info->{ TYPE_NAME }\n"; }
    Output:
    f_str 1 CHAR f_num 4 INT
    Ron
      Yeah sure.

      I already thought about warning the user if he is comparing strings with integers or vice versa.

      But as I said I want to play safe not to loose any information in the process.

      SQL is far too diverse to rely on assumptions, from what I know is DBI highly depended on the quality of the underlying DBDs, like emulating placeholders where not available.

      Cheers Rolf
      (addicted to the Perl Programming Language :)
      Wikisyntax for the Monastery FootballPerl is like chess, only without the dice

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1220196]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (3)
As of 2020-03-29 07:02 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    To "Disagree to disagree" means to:









    Results (169 votes). Check out past polls.

    Notices?