Re: OCI: How can I insert a string including an amphersand (&) from

In article <3DAA878C.6010000_at_unispeed.com>, Ole says...
>
>Hi,
>
>I think I really messed up things here. This is an example of the code I
>use.
>
>char query[200];
>
>sprintf(query, "insert into some_table (a,b) values (25, 'abc&def')");
>
>OCIStmtPrepare(stmthp,errhp,(text*)query,
> (ub4)strlen(query),(ub4)OCI_NTV_SYNTAX,(ub4) OCI_DEFAULT);
>OCIStmtExecute(svchp, stmthp, errhp,(ub4)1,
> (ub4)0,(CONST OCISnapshot *)NULL,
> (OCISnapshot *) NULL,OCI_COMMIT_ON_SUCCESS);
>

USE BIND VARIABLES, that insert should be:

  insert into some_table(a,b) values (:a,:b)

USE BIND VARIABLES in your DML unless you want to:

1. create the least scalable system you can
2. be trying to "fix" your problem (of not scaling) for a long time
3. totally defeat the purpose of the shared pool
4. buy additional CPU's in a futile effort to speed things up
5. buy additional RAM for the same reason as d

>
>Whenever I had a faulty insert I tried to execute the very same
>statement from sqlplus - and it failed too because the string also
>included an ampersand and I was prompted for a value.
>
>However the problem seems to be something different which I will look into.
>
>Thanks!
>
>Ole
>
>
>Finn Ellebaek Nielsen wrote:
>> Hi Ole.
>>
>> What does your code look like?
>>
>> Using OTL -- a generic C++ template interface to Oracle etc. available from
>> http://otl.sourceforge.net/home.htm -- the following simple example that
>> inserts rows into SCOTT.DEPT works with strings containing &:
>>
>> #include <stdio.h>
>>
>> #include "otlv32.h"
>>
>> int main()
>>
>> {
>> try {
>> otl_connect connection("scott/tiger_at_o901");
>> otl_stream statement;
>>
>> statement.open(1,
>> "insert into dept " \
>> "values (" \
>> " :deptno<int>, :dname<char[15]>, :loc<char[14]>" \
>> ")",
>> connection);
>>
>> statement << 90 << "1" << "2";
>> statement << 91 << "3" << "4";
>> statement << 92 << "&5" << "&6";
>> }
>> catch (otl_exception &e) {
>> printf("Exception: %s\n", e.msg);
>> }
>>
>> return(0);
>> }
>>
>> Cheers,
>>
>> Finn
>>
>> "Victor Kaminsky" <victorkaminsky_at_yahoo.de> wrote in message
>> news:90a73003.0210121246.57f6feb1_at_posting.google.com...
>>
>>>Ole Hansen <oha_at_city.dk> wrote in message
>>
>> news:<3DA7F142.7060909_at_city.dk>...
>>
>>>>Hi,
>>>>
>>>>I have troble escaping an amphersand (&) from within OCI.
>>>>Neither single quote (') or backslash (\) seems to work.
>>>>
>>>>The statement fails if I try to execute it from OCI if the string
>>>>includes an amphersand but I dont know how to escape an amphersand (&).
>>>>
>>>>I really need to store the amphersand with the string.
>>>>
>>>>Any ideas?
>>>>
>>>>Thanks in advance!
>>>>Ole Hansen
>>>
>>>maybe you could use the char() function. it is not nice, but could work
>>
>>
>
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp