DBI SQL column datatype not jiving with SQL statement requirement

Discussion in 'Perl' started by dna, Jan 16, 2004.

  1. dna

    dna Guest

    I have a sql query:

    SELECT ID, Name from tblUsers
    WHERE ID IN (10, 20, 30)

    and, as it probably is obvious to most, ID is of datatype INT

    In my perl module, using DBI, I have converted this statement to:

    SELECT ID, Name from tblUsers
    WHERE ID IN (?)

    i have a nice little for loop that will go through my passed in array of
    arguments for this query and then it will bind_param each of the arguments
    to the query.

    for (my $i = 0; $i <= $#{@$args}; $i++) {
    my $type;
    if ($args->[$i] =~ /^\d+$/) {
    $type = "SQL_INTEGER";
    } else {
    $type = "SQL_VARCHAR";
    }
    $sth->bind_param(($i+1), $args->[$i], { TYPE => $type });
    }

    the thing is, the argument that is used to populate the '?' in the query is
    a STRING as i need to make the list of possible id's dynamic. then, when i
    $sth->execute my handle that holds the query, setup with the bind_params, it
    craps out.

    this seems like an obvious error, but does anyone have any idea of how to
    get around it without having to do a loop and test each ID individually (not
    very efficient)?

    perhaps i'm simply missing something. any input would be appreciated!!!

    Thanks in advance,
    Dan
     
    dna, Jan 16, 2004
    #1
    1. Advertising

  2. dna

    Jim Guest

    dna wrote:
    > I have a sql query:
    >
    > SELECT ID, Name from tblUsers
    > WHERE ID IN (10, 20, 30)
    >
    > and, as it probably is obvious to most, ID is of datatype INT
    >
    > In my perl module, using DBI, I have converted this statement to:
    >
    > SELECT ID, Name from tblUsers
    > WHERE ID IN (?)
    >
    > i have a nice little for loop that will go through my passed in array of
    > arguments for this query and then it will bind_param each of the arguments
    > to the query.
    >
    > for (my $i = 0; $i <= $#{@$args}; $i++) {
    > my $type;
    > if ($args->[$i] =~ /^\d+$/) {
    > $type = "SQL_INTEGER";
    > } else {
    > $type = "SQL_VARCHAR";
    > }
    > $sth->bind_param(($i+1), $args->[$i], { TYPE => $type });
    > }
    >
    > the thing is, the argument that is used to populate the '?' in the query is
    > a STRING as i need to make the list of possible id's dynamic. then, when i
    > $sth->execute my handle that holds the query, setup with the bind_params, it
    > craps out.
    >
    > this seems like an obvious error, but does anyone have any idea of how to
    > get around it without having to do a loop and test each ID individually (not
    > very efficient)?
    >
    > perhaps i'm simply missing something. any input would be appreciated!!!
    >
    > Thanks in advance,
    > Dan
    >
    >
    >

    # one way:
    my $idlist = '10, 20, 30';
    my $query = "SELECT ID, Name from tblUsers WHERE ID IN ($idlist)";
    # etc.
     
    Jim, Jan 18, 2004
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ulloa
    Replies:
    1
    Views:
    546
    Juha Laiho
    Jul 22, 2004
  2. Jeff
    Replies:
    2
    Views:
    1,197
    Steve C. Orr [MCSD, MVP, CSM, ASP Insider]
    Apr 16, 2007
  3. Jerome Hauss
    Replies:
    0
    Views:
    186
    Jerome Hauss
    Oct 13, 2004
  4. stroncococcus

    type casting for DBI SQL statement doesn't work

    stroncococcus, Dec 6, 2006, in forum: Perl Misc
    Replies:
    4
    Views:
    183
    stroncococcus
    Dec 6, 2006
  5. howa
    Replies:
    2
    Views:
    135
    Joost Diepenmaat
    Feb 25, 2008
Loading...

Share This Page