Re: Update

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/04/18
Message-ID: <8dhn19$fsn$1@nnrp1.deja.com>#1/1

In article <38FC09F6.295B83D3_at_nmg.fr>,
  AM <a.metzger_at_nmg.fr> wrote:
> Hi,
>
> I have a table called 'test' like this :
...

> I have a Pro*C/C++ function :
> ----------------------------------------------------------------------

---

>
> Query :: Update ( bool type, char* tableName, char* dateEnd )
> {

...

> // Construction de la requête
> sprintf ( sqlstmt, "update %s set end_date=TO_DATE(':first',
> 'DD/MM/YYYY HH24:MI:SS') where table_name=':second'", TableName ); //
> Name of table I want to update : TableName
....

....

> I called this function with :
> query->Update ( false, "table1", "11/04/2000 16:03:00" ). The name of
> the table is in the object Query and the Attribute is TableName.
>
> Why this doesn't work ???? I have : "no result found" ....
>
> Please help me, I have no more idea ...
>
> - a.metzger_at_nmg.fr -
>
>

Loose the quotes on the bind variables. Part of the feature of a bind variable is that you do not use the quotes. Here is a similar example to yours in sqlplus showing it not working with the quotes and then working without them:

ops$tkyte_at_8i> create table t ( x date , y varchar2(25) );

Table created.

ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i> variable first varchar2(25)
ops$tkyte_at_8i> variable second varchar2(25)
ops$tkyte_at_8i>
ops$tkyte_at_8i> insert into t values ( null, 'hello' );

1 row created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> exec :second := 'hello';

PL/SQL procedure successfully completed.

ops$tkyte_at_8i> exec :first := '01/01/2000';

PL/SQL procedure successfully completed.

ops$tkyte_at_8i>
ops$tkyte_at_8i> update t set x = to_date( ':first', 'dd/mm/yyyy' ) where y = ':second';

0 rows updated.

ops$tkyte_at_8i> update t set x = to_date( :first, 'dd/mm/yyyy' ) where y = :second;

1 row updated.

ops$tkyte_at_8i>
ops$tkyte_at_8i> select * from t;

X Y

--------- -------------------------

01-JAN-00 hello

The first query had NO bind variables. The string ':first' was the STRING ':first' -- not a bind variable named first (else you could never update a column to be ':this text starts with a colon' using a constant )

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Tue Apr 18 2000 - 00:00:00 CDT