Ruby9i bind_param errors

ruby-talk
1

Hi,

I've been looking around and I just can't seem to find too much
documentation on ruby9i. The only thing that I found gives a very
high-level explanation of how to use bind variables when talking to an
oracle 9i database. Here is how they explain the bind_param method:

-----------------BEGIN QUOTE-------
Synopsis

statementObj.bind_param(label, bindvar)
Description

#bind_param binds bindvar to the parameter named label.

Bind variables are used for input of parameters in a DML statement or
for either input and output of parameters in a call to a stored
procedure or function.

---------------------END QUOTE--------------

Ok, so that seems pretty straight forward right? Well here is my code:

---------------------BEGIN MY CODE--------------

stmt = dbh.prepare("select value from v$segment_statistics
        where statistic_name = :stat_name
        and object_name = :t_name
        and tablspace_name = :ts_name;")

    table_name = "SOME_TABLE"
    tablespace_name = "SOME_TABLESPACE"
    stmt.bind_param("stat_name", "db block changes")
    stmt.bind_pram("t_name", "#{table_name}")
    stmt.bind_param("ts_name", "#{tablespace_name}")
    stmt.execute
-------------------------END MY CODE-----------------

However, when I run this code snippet, I get an error saying that that
'bind_param' could not bind the varialbe in "stmt_bind_param". Here
is an a copy of the error:

----------------BEGIN ERROR-----------------
in `bind_param': Could not bind variable in "stmt_bind_param" in file
"statement.c" at line 108 (Ruby9i::Error)
ORA-01036: illegal variable name/number
----------------END ERROR------------------

Anyone run into anything like this that would be able to point me in
the right direction?

Thanks in advance.
-Carlos

2

Carlos Diaz wrote:

stmt = dbh.prepare(“select value from v$segment_statistics
where statistic_name = :stat_name
and object_name = :t_name
and tablspace_name = :ts_name;”)

table_name = "SOME_TABLE"
tablespace_name = "SOME_TABLESPACE"
stmt.bind_param("stat_name", "db block changes")
stmt.bind_pram("t_name", "#{table_name}")
stmt.bind_param("ts_name", "#{tablespace_name}")
stmt.execute

You need to include the “:” in your named params in the call to
bind_param, e.g.:

 stmt.bind_param(":stat_name", "db block changes")

Chris.

3

By the way, this is failing as soon as it tries to bind the first param.

···

On 10/8/05, Carlos Diaz <crdiaz324@gmail.com> wrote:

Hi,

I've been looking around and I just can't seem to find too much
documentation on ruby9i. The only thing that I found gives a very
high-level explanation of how to use bind variables when talking to an
oracle 9i database. Here is how they explain the bind_param method:

-----------------BEGIN QUOTE-------
Synopsis

statementObj.bind_param(label, bindvar)
Description

#bind_param binds bindvar to the parameter named label.

Bind variables are used for input of parameters in a DML statement or
for either input and output of parameters in a call to a stored
procedure or function.

---------------------END QUOTE--------------

Ok, so that seems pretty straight forward right? Well here is my code:

---------------------BEGIN MY CODE--------------

stmt = dbh.prepare("select value from v$segment_statistics
        where statistic_name = :stat_name
        and object_name = :t_name
        and tablspace_name = :ts_name;")

    table_name = "SOME_TABLE"
    tablespace_name = "SOME_TABLESPACE"
    stmt.bind_param("stat_name", "db block changes")
    stmt.bind_pram("t_name", "#{table_name}")
    stmt.bind_param("ts_name", "#{tablespace_name}")
    stmt.execute
-------------------------END MY CODE-----------------

However, when I run this code snippet, I get an error saying that that
'bind_param' could not bind the varialbe in "stmt_bind_param". Here
is an a copy of the error:

----------------BEGIN ERROR-----------------
in `bind_param': Could not bind variable in "stmt_bind_param" in file
"statement.c" at line 108 (Ruby9i::Error)
ORA-01036: illegal variable name/number
----------------END ERROR------------------

Anyone run into anything like this that would be able to point me in
the right direction?

Thanks in advance.
-Carlos

4

Ok, I feel like an idiot. I just found my error. There is no problem
with the bind_param method. The problem I was running into is that I
had a for loop where I was using another instance of the variable
stmt, which happens to be the same name as I was using in the for the
stmt I was trying to bind my variable to. So, to make a long story
short, I was trying to bind a parameter to the wrong statement.

···

On 10/8/05, Carlos Diaz <crdiaz324@gmail.com> wrote:

By the way, this is failing as soon as it tries to bind the first param.

On 10/8/05, Carlos Diaz <crdiaz324@gmail.com> wrote:
> Hi,
>
> I've been looking around and I just can't seem to find too much
> documentation on ruby9i. The only thing that I found gives a very
> high-level explanation of how to use bind variables when talking to an
> oracle 9i database. Here is how they explain the bind_param method:
>
> -----------------BEGIN QUOTE-------
> Synopsis
>
> statementObj.bind_param(label, bindvar)
> Description
>
> #bind_param binds bindvar to the parameter named label.
>
> Bind variables are used for input of parameters in a DML statement or
> for either input and output of parameters in a call to a stored
> procedure or function.
>
> ---------------------END QUOTE--------------
>
> Ok, so that seems pretty straight forward right? Well here is my code:
>
> ---------------------BEGIN MY CODE--------------
>
> stmt = dbh.prepare("select value from v$segment_statistics
> where statistic_name = :stat_name
> and object_name = :t_name
> and tablspace_name = :ts_name;")
>
> table_name = "SOME_TABLE"
> tablespace_name = "SOME_TABLESPACE"
> stmt.bind_param("stat_name", "db block changes")
> stmt.bind_pram("t_name", "#{table_name}")
> stmt.bind_param("ts_name", "#{tablespace_name}")
> stmt.execute
> -------------------------END MY CODE-----------------
>
> However, when I run this code snippet, I get an error saying that that
> 'bind_param' could not bind the varialbe in "stmt_bind_param". Here
> is an a copy of the error:
>
> ----------------BEGIN ERROR-----------------
> in `bind_param': Could not bind variable in "stmt_bind_param" in file
> "statement.c" at line 108 (Ruby9i::Error)
> ORA-01036: illegal variable name/number
> ----------------END ERROR------------------
>
> Anyone run into anything like this that would be able to point me in
> the right direction?
>
> Thanks in advance.
> -Carlos
>