DBI SQL column datatype not jiving with SQL statement requirement

[dna]

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

[Jim]

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.