How to "set define off" using dbExpress ?

[Michel Nolard]

Hi !

I've tried to insert data in an Oracle database using the dbExpress driver for Builder C++. The data contain an XML string which escapes the "<" using "<". Too bad for us, the ampersand character is the default substitution character when using Oracle.

I _REALLY_ need to disable this default Oracle behavior very quickly. Sorry to urge you this way.

Thank you by advance.

--
Michel Nolard
Ares Developer

[Paul]

I suppose you wrote incorrect string expression in your SQL. Possibly you wrote it in double quotes. Please check your query in SQL*Plus, try the following example

Code: Select all

UPDATE emp
SET
  ENAME = 'abc<abc'
WHERE
  EMPNO = 7499

[Michel Nolard]

Sorry, but I didn't made it clear enough for you to reply, I think.

Luckily a friend of mines found the solution which I give here. This is a tricky solution, waiting for a better cleaner one.

So, I recall you the situation :

I have a string containing XML text which escapes some 'A<B";[/code]

I need to put it into an SQL query objet using ParamByName without having Oracle interpreting the '&' character as a substitution character.

What I want to do is : (pseudo-code)

Code: Select all

TSQLQuery query;
query.SQL->Text = "INSERT INTO TABLE1 (DATA) VALUES (:PXml)";
query.ParamByName ("PXml")->AsString = xml;
query.ExecSQL();

This code makes Oracle hang on the ExecSQL() call because it waits for more informations as it interprets the '&' char as a substitution char.

The solution is to transform the xml string by replacing any occurence of the ampersand by the sequence "' || char (48 )|| '" :

Code: Select all

 xml.replace ("&", "' || char (48)|| '");

and it works
Note: This is a double quote " concatenated to a single quote ' !

A better solution would be to be able to tell Oracle "set define off" which is the right Oracle statement to tell it to stop to evaluate the "&" as the substitution character. The problem is that I can't find a way to tell it that.

Maybe you have a better solution, if so, feel free to tell me

Thank you.

--
Michel Nolard

[Paul]

We couldn't reproduce the problem with your example with VARCHAR field, DbxOda 2.50.5, C++ Builder 6.0. Please send us complete sample to demonstrate it and include script to create server objects.

[Michel Nolard]

I see now that there are some informations missing for you to reproduce :
- I was using a "LONG" field
- Its contents was feeded using a Borland AnsiString containing a text with an ampersand, like "Data&Data".

The SQL Statement was an "INSERT".

When the ExecSQL() method of TSQLQuery is called, the application stops upon it, waiting forever.

I hope you'll be able to reproduce this as it is a bug which I have found not to be present in other DB access libraries.

[Paul]

We cannot reproduce your problem. Please try the following code

Code: Select all

  TSQLConnection* SQLConnection2 = new TSQLConnection(NULL);
  SQLConnection2->ConnectionName = "Oracle (Core Lab)";
  SQLConnection2->DriverName = "Oracle (Core Lab)";
  SQLConnection2->GetDriverFunc = "getSQLDriverORA";
  SQLConnection2->LibraryName = "dbexpoda.dll";
  SQLConnection2->VendorLib = "OCI.DLL";
  SQLConnection2->Params->Text =
    "DataBase=ora920\r\n"
    "User_Name=test\r\n"
    "Password=test\r\n"
    "BlobSize=-1\r\n"
    "DriverName=Oracle (Core Lab)\r\n"
    "ErrorResourceFile=\r\n"
    "LocaleCode=0000\r\n"
    "Oracle TransIsolation=ReadCommited\r\n"
    "RoleName=Normal\r\n"
    "LongStrings=True\r\n"
    "EnableBCD=False\r\n"
    "InternalName=\r\n"
    "UseQuoteChar=False\r\n"
    "FetchAll=False\r\n"
    "CharLength=0\r\n"
    "Charset=\r\n";

  AnsiString xml = "A<B";
  char *ch = xml.c_str();

  TSQLQuery* query = new TSQLQuery(NULL);
  query->SQLConnection = SQLConnection2;
  SQLConnection2->Open();
  query->SQL->Text = "INSERT INTO LONG_TYPE (ID, F_LONG) VALUES  (11, :PXml)";
  query->ParamByName ("PXml")->AsString = xml;
  query->ExecSQL();

Code: Select all

CREATE TABLE LONG_TYPE (
  ID NUMBER NOT NULL,
  F_LONG LONG
)