Update won't post in Win32 Service app

[TerryC]

I'm creating a TServiceApplication in C++ Builder 6 Professional. Everything with ODAC 6.9 has been working perfectly, except for an update.

Qry2 is a TOraQuery.
ThisTime is a TDateTime.
RevID, MgrID, ApvID are all Integers.
OracleDate is an AnsiString.

Code: Select all

Qry2->SQL->Text =
      "select APV_ID "
      "  from AFE_APPROVALS "
      "       join AFE_REVISIONS on REV_ID = APV_REV_ID "
      "       join MANAGEMENT on MGR_ID = APV_MGR_ID "
      " where REV_ID = " + IntToStr(RevID) + " "
      "       and MGR_ID = " + IntToStr(MgrID);

Qry2->Open();
ApvID = Qry2->FieldByName("APV_ID")->AsInteger;
Qry2->Close();

ThisTime = Now();

OracleDate = IntToStr(YearOf(ThisTime)) + "-" +
            IntToStr(MonthOf(ThisTime)) + "-" +
            IntToStr(DayOf(ThisTime));

Qry2->SQL->Text =
      "update AFE_APPROVALS "
      "   set APV_SENT_DT = DATE '" + OracleDate + "' "
      " where APV_ID = " + IntToStr(ApvID);

Qry2->Execute();

The Execute() generates an Access Violation (!)
What have I done wrong?

[bork]

Hello

To resolve your issue we need additional information. Please send us the DDL script for creating the "AFE_APPROVALS" table.

[TerryC]

Here's that DDL:

Code: Select all

CREATE TABLE MYPROJ.AFE_APPROVALS
(
  APV_ID            NUMBER(12)                  NOT NULL,
  APV_REV_ID        NUMBER(12)                  NOT NULL,
  APV_MGR_ID        NUMBER(12)                  NOT NULL,
  APV_APV_CODE_ID   NUMBER(12)                  DEFAULT 0                     NOT NULL,
  APV_KEY           CHAR(32 CHAR),
  APV_SENT_DT       DATE,
  APV_SENT_BKUP_DT  DATE,
  APV_RECD_DT       DATE,
  APV_EMP_NO        VARCHAR2(15 CHAR),
  APV_COMMENT       CLOB,
  APV_USE_BKUP      CHAR(1 BYTE)                DEFAULT 'N'
)

[TerryC]

Also, I tried rewriting the code for the TOraQuery to use parameters for the variables instead of building the SQL in the program. The result of Execute() was the same when using params: Access Violation.

[TerryC]

Tried adding a second TOraSession component to my service application so that the TOraQuery doing the update would have its own.
That didn't help either.

[TerryC]

I have now tried a new TServiceApplication with nothing but a TOraSession and a TOraQuery. The session has the connect string set correctly, Connected set to True and LoginPrompt set to False.
The TOraQuery->SQL = "update AFE_APPROVALS set APV_SENT_DT = DATE '2010-5-10' where APV_ID = 12398"
The OnExecute() event handler for the service is: "OraQuery1->Execute();"
It's as simple of an example as I could do, and it still causes an Access Violation, as reported in the Windows Event Log.
Please help me resolve this, as I am up against a looming deadline on this project.
Thank you!

[TerryC]

...oh, another bit of info. The database is Oracle version 10.2.0.3.0.

[bork]

Hello

I tried to reproduce your issue but record was updated successfully. Probably you change some options of your connection (or dataset) or change the date. We need more information.

Try to execute the following two queries in the "SQL Plus" utility:

Code: Select all

update AFE_APPROVALS set APV_SENT_DT = DATE '2010-5-10' where APV_ID = 12398

and

Code: Select all

update AFE_APPROVALS set APV_SENT_DT = TO_DATE('2010-05-11', 'yyyy-dd-mm') where APV_ID = 12398

Also try to execute the following code from your application:

Code: Select all

{
  OraSession1->Open();

  TOraQuery *UpdateQuery = new TOraQuery(NULL);
  UpdateQuery->Session = OraSession1;

  UpdateQuery->SQL->Text =
          "update BTEST12 set APV_SENT_DT = TO_DATE('2010-05-11', 'yyyy-dd-mm') where APV_ID = 12398";

  UpdateQuery->Execute();

  UpdateQuery->~TOraQuery();
}

Please provide us results of the two queries and code execution. Also please specify exact ODAC version (for example 6.90.0.57).

[TerryC]

Both of the SQL statements execute correctly when fed directly to Oracle (I use an Oracle DB manager called Toad, rather than using SQLPlus)
The C++ code you provided does not work, however. It results in the same Access Violation that my attempts have caused.
Please confirm that you were able to execute an UPDATE statement in a TServiceApplication? There is no problem performing an UPDATE in a regular, GUI application. If that's what you tested, please try a Service application instead. That's where the problem is.
(In the C++ Builder IDE menu, select File -> New -> Other -> Service Application)
It still doesn't work, and this is a show-stopper. A large project is dead in the water until we can get this update to work.

[TerryC]

Oh, and yes, the ODAC version is the most recent build 6.9.0.57.

[TerryC]

Another angle I tried was to update a different field. I tried to update a VARCHAR2 field with some text, and that also causes an Access Violation and fails to update.
There seems to be a problem with the Execute() function when running in a TServiceApplication. That is the combination which causes an error.

[TerryC]

EUREKA!
I have found the problem: It was my fault.
There were copies of an older version of the ODAC .bpl, .lib and .bpi files in folders where they shouldn't be. Not sure how they got there, but probably a bad drag-and-drop along the way somewhere.
I'm guessing that one or more of the old libraries got linked in by mistake, making the application crash.
I have searched my system thoroughly for old versions and cleaned it all up, so now the UPDATE works perfectly.
I apologize for wasting your time, and I greatly appreciate your help.

[bork]

It is good to see that this problem has been solved. If any other questions come up, please contact me.