bind variable problem

------=_Part_1190_12786360.1169513808096
Content-Type: text/plain; charset=WINDOWS-1252; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi,



This problem has been killing me for a while =85

Script 1 and script 2 (mentioned below) are very much the same. However I
have hard coded the query

in script 2 whereas constructed in script 1.



Also script 1 is implement on an environment (which uses oracle
version 9and DBI
1.38) and script 2 on another environment (which uses oracle version 10gan=
d
)



In script 1, the sql query is executed successfully, but in script 2 it
fails in execute() step..

The bind variable has the following values:



Tables =3D> CHARGE NORMALISED_EVENT SUBTOTAL_RATING_VALUE
SUBTOTAL_RATING_DELTA NORMALISED_EVENT_ERROR



Hard coding bind variables goes trhough fine.. Is this a known bug or am =
I
doing anything silly here.. Please could someone help me out?





Script 1:

========

$lsql =3D "SELECT NVL2(partition_name,

segment_name || ':' || partition_name,

segment_name)

FROM user_segments

WHERE segment_type IN ('TABLE', 'TABLE PARTITION') AND

segment_name NOT IN (";

}

# Create the necessary number of bind variable placeholders.

$lsql .=3D '?,' x scalar(@ltables);

# Remove the last ",".

chop $lsql;

# Order by BYTES if we are not splitting customer partitions out.

$lsql .=3D ") ORDER BY bytes DESC";

}



print ("\n SQL =3D> $lsql");

# Execute the query and build an array of "other" table names.

my @lotherTables;

$lcsr =3D $zdb->prepare($lsql) ||

zDie("Could not prepare query at line " . __LINE__ . ": " .

$zdb->errstr);

$lcsr->execute(@ltables) ||

zDie("Could not execute query at line " . __LINE__ . ": " .

$zdb->errstr);





Script 2

=======3D

$lsql =3D "SELECT NVL2(partition_name,

segment_name || ':' || partition_name,

segment_name)

FROM user_segments

WHERE segment_type IN ('TABLE', 'TABLE PARTITION') AND

segment_name NOT IN

(SELECT object_name

FROM recyclebin bin) AND

segment_name NOT IN (?,?,?,?,?) ORDER BY bytes
DESC";#'CHARGE','NORMALISED_EVENT','SUBTOTAL_RATING_DELTA',

#'NORMALISED_EVENT_ERROR')
ORDER BY bytes DESC";

# Create the necessary number of bind variable placeholders.

#$lsql .=3D '?,' x scalar(@ltables);

# Remove the last ",".

#chop $lsql;

# Order by BYTES if we are not splitting customer partitions out.

#if (!$opt_scp) {

# $lsql .=3D ") ORDER BY bytes DESC";

#}

#else {

# $lsql .=3D ")";

#}

print ("\n sql =3D> $lsql");

# Execute the query and build an array of "other" table names.

my @lotherTables;

$lcsr =3D $zdb->prepare($lsql) ||

zDie("Could not prepare query at line " . __LINE__ . ": " .

$zdb->errstr);

print ("\nTables =3D> @ltables\n");

$lcsr->execute(@ltables) ||

zDie("Could not execute query at line " . __LINE__ . ": " .

$zdb->errstr);


Thanks,

Anand.

------=_Part_1190_12786360.1169513808096--

Re: bind variable problem

Bit difficult without an error message. Suggest you print out the $lsql
variable before executing and then try running the same string using
SQL*Plus or similar, see if you can get an error from it. Suspect a
syntax problem somewhere.

Martin

Anand.K.S. wrote:
> Hi,
>
>
>
> This problem has been killing me for a while …
>
> Script 1 and script 2 (mentioned below) are very much the same. However I
> have hard coded the query
>
> in script 2 whereas constructed in script 1.
>
>
>
> Also script 1 is implement on an environment (which uses oracle
> version 9and DBI
> 1.38) and script 2 on another environment (which uses oracle version
> 10gand
> )
>
>
>
> In script 1, the sql query is executed successfully, but in script 2 it
> fails in execute() step..
>
> The bind variable has the following values:
>
>
>
> Tables => CHARGE NORMALISED_EVENT SUBTOTAL_RATING_VALUE
> SUBTOTAL_RATING_DELTA NORMALISED_EVENT_ERROR
>
>
>
> Hard coding bind variables goes trhough fine.. Is this a known bug
> or am I
> doing anything silly here.. Please could someone help me out?
>
>
>
>
>
> Script 1:
>
> ========
>
> $lsql = "SELECT NVL2(partition_name,
>
> segment_name || ':' || partition_name,
>
> segment_name)
>
> FROM user_segments
>
> WHERE segment_type IN ('TABLE', 'TABLE PARTITION') AND
>
> segment_name NOT IN (";
>
> }
>
> # Create the necessary number of bind variable placeholders.
>
> $lsql .= '?,' x scalar(@ltables);
>
> # Remove the last ",".
>
> chop $lsql;
>
> # Order by BYTES if we are not splitting customer partitions out.
>
> $lsql .= ") ORDER BY bytes DESC";
>
> }
>
>
>
> print ("\n SQL => $lsql");
>
> # Execute the query and build an array of "other" table names.
>
> my @lotherTables;
>
> $lcsr = $zdb->prepare($lsql) ||
>
> zDie("Could not prepare query at line " . __LINE__ . ":
> " .
>
> $zdb->errstr);
>
> $lcsr->execute(@ltables) ||
>
> zDie("Could not execute query at line " . __LINE__ . ":
> " .
>
> $zdb->errstr);
>
>
>
>
>
> Script 2
>
> =======
>
> $lsql = "SELECT NVL2(partition_name,
>
> segment_name || ':' || partition_name,
>
> segment_name)
>
> FROM user_segments
>
> WHERE segment_type IN ('TABLE', 'TABLE PARTITION') AND
>
> segment_name NOT IN
>
> (SELECT object_name
>
> FROM recyclebin bin) AND
>
> segment_name NOT IN (?,?,?,?,?) ORDER BY bytes
> DESC";#'CHARGE','NORMALISED_EVENT','SUBTOTAL_RATING_DELTA',
>
> #'NORMALISED_EVENT_ERROR')
> ORDER BY bytes DESC";
>
> # Create the necessary number of bind variable placeholders.
>
> #$lsql .= '?,' x scalar(@ltables);
>
> # Remove the last ",".
>
> #chop $lsql;
>
> # Order by BYTES if we are not splitting customer partitions out.
>
> #if (!$opt_scp) {
>
> # $lsql .= ") ORDER BY bytes DESC";
>
> #}
>
> #else {
>
> # $lsql .= ")";
>
> #}
>
> print ("\n sql => $lsql");
>
> # Execute the query and build an array of "other" table names.
>
> my @lotherTables;
>
> $lcsr = $zdb->prepare($lsql) ||
>
> zDie("Could not prepare query at line " . __LINE__ . ":
> " .
>
> $zdb->errstr);
>
> print ("\nTables => @ltables\n");
>
> $lcsr->execute(@ltables) ||
>
> zDie("Could not execute query at line " . __LINE__ . ":
> " .
>
> $zdb->errstr);
>
>
> Thanks,
>
> Anand.
>

Re: bind variable problem

Anand.K.S. wrote:
> Hi,
>
>
>
> This problem has been killing me for a while …
>
> Script 1 and script 2 (mentioned below) are very much the same. However I
> have hard coded the query
>
> in script 2 whereas constructed in script 1.
>
>
>
> Also script 1 is implement on an environment (which uses oracle
> version 9and DBI
> 1.38) and script 2 on another environment (which uses oracle version
> 10gand
> )
>
>
>
> In script 1, the sql query is executed successfully, but in script 2 it
> fails in execute() step..
>
> The bind variable has the following values:
>
>
>
> Tables => CHARGE NORMALISED_EVENT SUBTOTAL_RATING_VALUE
> SUBTOTAL_RATING_DELTA NORMALISED_EVENT_ERROR
>
>
>
> Hard coding bind variables goes trhough fine.. Is this a known bug or
> am I
> doing anything silly here.. Please could someone help me out?
>
>
>
>
>
> Script 1:
>
> ========
>
> $lsql = "SELECT NVL2(partition_name,
>
> segment_name || ':' || partition_name,
>
> segment_name)
>
> FROM user_segments
>
> WHERE segment_type IN ('TABLE', 'TABLE PARTITION') AND
>
> segment_name NOT IN (";
>
> }
>
> # Create the necessary number of bind variable placeholders.
>
> $lsql .= '?,' x scalar(@ltables);
>
> # Remove the last ",".
>
> chop $lsql;
>
> # Order by BYTES if we are not splitting customer partitions out.
>
> $lsql .= ") ORDER BY bytes DESC";
>
> }
>
>
>
> print ("\n SQL => $lsql");
>
> # Execute the query and build an array of "other" table names.
>
> my @lotherTables;
>
> $lcsr = $zdb->prepare($lsql) ||
>
> zDie("Could not prepare query at line " . __LINE__ . ": " .
>
> $zdb->errstr);
>
> $lcsr->execute(@ltables) ||
>
> zDie("Could not execute query at line " . __LINE__ . ": " .
>
> $zdb->errstr);
>
>
>
>
>
> Script 2
>
> =======
>
> $lsql = "SELECT NVL2(partition_name,
>
> segment_name || ':' || partition_name,
>
> segment_name)
>
> FROM user_segments
>
> WHERE segment_type IN ('TABLE', 'TABLE PARTITION') AND
>
> segment_name NOT IN
>
> (SELECT object_name
>
> FROM recyclebin bin) AND
>
> segment_name NOT IN (?,?,?,?,?) ORDER BY bytes
> DESC";#'CHARGE','NORMALISED_EVENT','SUBTOTAL_RATING_DELTA',
>
> #'NORMALISED_EVENT_ERROR')
> ORDER BY bytes DESC";
>
> # Create the necessary number of bind variable placeholders.
>
> #$lsql .= '?,' x scalar(@ltables);
>
> # Remove the last ",".
>
> #chop $lsql;
>
> # Order by BYTES if we are not splitting customer partitions out.
>
> #if (!$opt_scp) {
>
> # $lsql .= ") ORDER BY bytes DESC";
>
> #}
>
> #else {
>
> # $lsql .= ")";
>
> #}
>
> print ("\n sql => $lsql");
>
> # Execute the query and build an array of "other" table names.
>
> my @lotherTables;
>
> $lcsr = $zdb->prepare($lsql) ||
>
> zDie("Could not prepare query at line " . __LINE__ . ": " .
>
> $zdb->errstr);
>
> print ("\nTables => @ltables\n");

You haven't called $lcsr->bind_param for the parmeters!

> $lcsr->execute(@ltables) ||
>
> zDie("Could not execute query at line " . __LINE__ . ": " .
>
> $zdb->errstr);
>
>
> Thanks,
>
> Anand.
>

Can I suggest that in future you should include the error messages you
see as you will get better assistance that way.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

Re: bind variable problem

Your first pattern is correct. In the second one you cannot use bind in an
in statment like this as it connot correctly quote the params. Not sure if
this is an DBI thing or DBD::Orale OCI thing though


"Anand . K . S ." wrote in message
news:9b02676d0701221656p52e2829fpce30cc0757b950e6@mail.gmail .com...
Hi,



This problem has been killing me for a while …

Script 1 and script 2 (mentioned below) are very much the same. However I
have hard coded the query

in script 2 whereas constructed in script 1.



Also script 1 is implement on an environment (which uses oracle
version 9and DBI
1.38) and script 2 on another environment (which uses oracle version 10gand
)



In script 1, the sql query is executed successfully, but in script 2 it
fails in execute() step..

The bind variable has the following values:



Tables => CHARGE NORMALISED_EVENT SUBTOTAL_RATING_VALUE
SUBTOTAL_RATING_DELTA NORMALISED_EVENT_ERROR



Hard coding bind variables goes trhough fine.. Is this a known bug or am I
doing anything silly here.. Please could someone help me out?





Script 1:

========

$lsql = "SELECT NVL2(partition_name,

segment_name || ':' || partition_name,

segment_name)

FROM user_segments

WHERE segment_type IN ('TABLE', 'TABLE PARTITION') AND

segment_name NOT IN (";

}

# Create the necessary number of bind variable placeholders.

$lsql .= '?,' x scalar(@ltables);

# Remove the last ",".

chop $lsql;

# Order by BYTES if we are not splitting customer partitions out.

$lsql .= ") ORDER BY bytes DESC";

}



print ("\n SQL => $lsql");

# Execute the query and build an array of "other" table names.

my @lotherTables;

$lcsr = $zdb->prepare($lsql) ||

zDie("Could not prepare query at line " . __LINE__ . ": " .

$zdb->errstr);

$lcsr->execute(@ltables) ||

zDie("Could not execute query at line " . __LINE__ . ": " .

$zdb->errstr);





Script 2

=======

$lsql = "SELECT NVL2(partition_name,

segment_name || ':' || partition_name,

segment_name)

FROM user_segments

WHERE segment_type IN ('TABLE', 'TABLE PARTITION') AND

segment_name NOT IN

(SELECT object_name

FROM recyclebin bin) AND

segment_name NOT IN (?,?,?,?,?) ORDER BY bytes
DESC";#'CHARGE','NORMALISED_EVENT','SUBTOTAL_RATING_DELTA',

#'NORMALISED_EVENT_ERROR')
ORDER BY bytes DESC";

# Create the necessary number of bind variable placeholders.

#$lsql .= '?,' x scalar(@ltables);

# Remove the last ",".

#chop $lsql;

# Order by BYTES if we are not splitting customer partitions out.

#if (!$opt_scp) {

# $lsql .= ") ORDER BY bytes DESC";

#}

#else {

# $lsql .= ")";

#}

print ("\n sql => $lsql");

# Execute the query and build an array of "other" table names.

my @lotherTables;

$lcsr = $zdb->prepare($lsql) ||

zDie("Could not prepare query at line " . __LINE__ . ": " .

$zdb->errstr);

print ("\nTables => @ltables\n");

$lcsr->execute(@ltables) ||

zDie("Could not execute query at line " . __LINE__ . ": " .

$zdb->errstr);


Thanks,

Anand.

RE: bind variable problem

John Scoles [mailto:scoles@pythian.com] wrote:
>
> Your first pattern is correct. In the second one you cannot use bind in an
> in statment like this as it connot correctly quote the params. Not sure if
> this is an DBI thing or DBD::Orale OCI thing though

I can't figure out what you mean by this. In the first block of code, the
number of placeholders is determined based on the size of @ltables; in the
second block of code, the number of placeholders is hard-coded at 5. Other than
that, the two SQL statements are *exactly the same*.

Ronald

RE: bind variable problem

Martin Evans [mailto:martin.evans@easysoft.com] wrote:
>
> You haven't called $lcsr->bind_param for the parmeters!

You don't need to call bind_param() if you pass the parameter values in the call
to execute(), as Anand is doing.

Ronald

Re: bind variable problem

Ronald Kimball wrote:
>
>
> Martin Evans [mailto:martin.evans@easysoft.com] wrote:
>> You haven't called $lcsr->bind_param for the parmeters!
>
> You don't need to call bind_param() if you pass the parameter values in the call
> to execute(), as Anand is doing.

I know that, sorry, I missed that.

Perhaps if Anand told us what the error is.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com