Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

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

by bliako (Parson)
on Aug 10, 2018 at 08:17 UTC ( #1220180=note: print w/replies, xml ) Need Help??


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

I'm hacking a DBI abstraction, and I'm not sure about the side-effects of binding 42 vs "42" to a placeholder.

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? If DB column was declared as INT then don't quote (and possibly warn user of a type mismatch if any), else quote.

Having said that I read in Programming the Perl DBI:

It's equally simple to specify multiple bind values within one statement, since bind_ param( ) takes the index, starting from 1, of the parameter to bind the given value to. For example:

    $sth = $dbh->prepare( "
                SELECT name, location
                FROM megaliths
                WHERE name = ?
                AND mapref = ?
                AND type LIKE ?
            " );
    $sth->bind_param( 1, "Avebury" );
    $sth->bind_param( 2, $mapreference );
    $sth->bind_param( 3, "%Stone Circle%" );

You may have noticed that we haven't called the quote( ) method on the values. Bind values are passed to the database separately from the SQL statement,[50] so there's no need to "wrap up" the value in SQL quoting rules.

    [50] This is not strictly true, since some drivers emulate placeholders by doing a textual replacement of the placeholders with bind values before passing the SQL to the database. Such drivers use Perl's internal information to guess whether each value needs quoting or not. Refer to the driver documentation for more information. [emphasis mine]
  • Comment on Re^3: Distiguishing arguments: number-strings vs real integer

Replies are listed 'Best First'.
Re^4: Distiguishing arguments: number-strings vs real integer
by LanX (Archbishop) on Aug 10, 2018 at 14:02 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 ; 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

      > 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://1220180]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (6)
As of 2020-04-05 02:00 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    The most amusing oxymoron is:
















    Results (33 votes). Check out past polls.

    Notices?