Delphi 2009 database update SQL includes fkCalculated field's condition?

ClientDataSet -> DataSetProvider -> SQLQuery -> [Oracle Database]

Add fkCalculated field into TSQLQuery.

Execute ClientDataSet.ApplyUpdates cast error:

ORA-00904: invalid column name

In Call stack window, The TSQLResolver.DoExecSQL carries strange 'where' 
clauses to PSExecuteStatement.



----------------------------------------------
...
DBXCommon.TDBXMorphicCommand.ExecuteQuery
SqlExpr.TSQLConnection.Execute('update "TEST_TABLE"  set'#$D#$A' "ID" = 
?'#$D#$A'where'#$D#$A' "ID" = ? and'#$D#$A' "CALC_FIELD" = ?'#$D#$A,???,nil)
SqlExpr.TCustomSQLDataSet.PSExecuteStatement('update "TEST_TABLE" 
set'#$D#$A' "ID" = ?'#$D#$A'where'#$D#$A' "ID" = ? and'#$D#$A' "CALC_FIELD" 
= ?'#$D#$A,$2772120,???)
Provider.TSQLResolver.DoExecSQL($4A85AC,$BD2358)
...
----------------------------------------------
0
jackie
9/1/2008 7:29:53 AM
embarcadero.delphi.multi-tier 396 articles. 0 followers. Follow

10 Replies
1401 Views

Similar Articles

[PageSpeed] 4
Get it on Google Play
Get it on Apple App Store

It didn't ignored the as it must be, you can make it do
with ProviderFlags (remove the pfInWhere and pfInUpdate flags
from the field definition) on TClientDataset *AND* the
TSQLQuery (or what ever).

jackie chang escreveu:
> ClientDataSet -> DataSetProvider -> SQLQuery -> [Oracle Database]
> 
> Add fkCalculated field into TSQLQuery.
> 
> Execute ClientDataSet.ApplyUpdates cast error:
> 
> ORA-00904: invalid column name
> 
> In Call stack window, The TSQLResolver.DoExecSQL carries strange 'where' 
> clauses to PSExecuteStatement.
> 
> 
> 
> ----------------------------------------------
> ..
> DBXCommon.TDBXMorphicCommand.ExecuteQuery
> SqlExpr.TSQLConnection.Execute('update "TEST_TABLE"  set'#$D#$A' "ID" = 
> ?'#$D#$A'where'#$D#$A' "ID" = ? and'#$D#$A' "CALC_FIELD" = ?'#$D#$A,???,nil)
> SqlExpr.TCustomSQLDataSet.PSExecuteStatement('update "TEST_TABLE" 
> set'#$D#$A' "ID" = ?'#$D#$A'where'#$D#$A' "ID" = ? and'#$D#$A' "CALC_FIELD" 
> = ?'#$D#$A,$2772120,???)
> Provider.TSQLResolver.DoExecSQL($4A85AC,$BD2358)
> ..
> ----------------------------------------------
0
Fabricio
9/1/2008 9:58:55 PM
I already try it.
The TClientDataset *AND* TSQLQuery ProviderFlags different from [pfInUpdate, 
pfInWhere].
(Only pfInKey to be effective)

I trace source code in Provider.pas
procedure TUpdateTree.InitDelta(ADelta: TPacketDataSet);
...
...
for i := 0 to FDeltaDS.FieldCount - 1 do
begin
  Attr := FDeltaDS.InternalGetOptionalParam(szPROVFLAGS, 
FDeltaDS.Fields[i].FieldNo);
  if not (VarIsNull(Attr) or VarIsClear(Attr)) then
    FDeltaDS.Fields[i].ProviderFlags := TProviderFlags(Byte(Attr)); //*** 
Never run this!
...
...
  Attr := FDeltaDS.InternalGetOptionalParam(szSERVERCALC, 
FDeltaDS.Fields[i].FieldNo);
  if not (VarIsClear(Attr) or VarIsNull(Attr)) and
    (VarType(Attr) = varBoolean) and Boolean(Attr) then
    FDeltaDS.Fields[i].Tag := tagSERVERCALC;  //*** Never run this!

Its always [pfInUpdate, pfInWhere] and never set field to fkCalculated.

"Fabricio Araujo"  wrote
> It didn't ignored the as it must be, you can make it do
> with ProviderFlags (remove the pfInWhere and pfInUpdate flags
> from the field definition) on TClientDataset *AND* the
> TSQLQuery (or what ever).
0
jackie
9/2/2008 7:35:01 AM
Why you put the calculated field on SQLQuery?
TSQLQuery is unidirectional. Although it must
have been excluded in ApplyUpdates, I would
never put it on the source dataset.

jackie chang escreveu:
> I already try it.
> The TClientDataset *AND* TSQLQuery ProviderFlags different from [pfInUpdate, 
> pfInWhere].
> (Only pfInKey to be effective)
> 
> I trace source code in Provider.pas
> procedure TUpdateTree.InitDelta(ADelta: TPacketDataSet);
> ..
> ..
> for i := 0 to FDeltaDS.FieldCount - 1 do
> begin
>   Attr := FDeltaDS.InternalGetOptionalParam(szPROVFLAGS, 
> FDeltaDS.Fields[i].FieldNo);
>   if not (VarIsNull(Attr) or VarIsClear(Attr)) then
>     FDeltaDS.Fields[i].ProviderFlags := TProviderFlags(Byte(Attr)); //*** 
> Never run this!
> ..
> ..
>   Attr := FDeltaDS.InternalGetOptionalParam(szSERVERCALC, 
> FDeltaDS.Fields[i].FieldNo);
>   if not (VarIsClear(Attr) or VarIsNull(Attr)) and
>     (VarType(Attr) = varBoolean) and Boolean(Attr) then
>     FDeltaDS.Fields[i].Tag := tagSERVERCALC;  //*** Never run this!
> 
> Its always [pfInUpdate, pfInWhere] and never set field to fkCalculated.
> 
> "Fabricio Araujo"  wrote
>> It didn't ignored the as it must be, you can make it do
>> with ProviderFlags (remove the pfInWhere and pfInUpdate flags
>> from the field definition) on TClientDataset *AND* the
>> TSQLQuery (or what ever).
0
Fabricio
9/9/2008 9:35:37 PM
I put some into calculated field such summing value.
It is working fine in Delphi 2007 or below.

> {quote:title=Fabricio Araujo wrote:}{quote}
> Why you put the calculated field on SQLQuery?
> TSQLQuery is unidirectional. Although it must
> have been excluded in ApplyUpdates, I would
> never put it on the source dataset.
0
jackie
9/21/2008 5:08:34 PM
So it appears to be a bug somehow on Dbx4. Better
create a report on QC.
Otherwise, you can alter Provider.pas and apply it
on your application.


[Side note]
I hope you don't take the question as a criticism,
but I really became curious about it. When I use
dbx or other components, I just put the sql and
forget about it. I use calculated fields only on
TClientDatasets.
If it sound as a criticism, my apologies.




jackie chang escreveu:
> I put some into calculated field such summing value.
> It is working fine in Delphi 2007 or below.
> 
>> {quote:title=Fabricio Araujo wrote:}{quote}
>> Why you put the calculated field on SQLQuery?
>> TSQLQuery is unidirectional. Although it must
>> have been excluded in ApplyUpdates, I would
>> never put it on the source dataset.
0
Fabricio
9/26/2008 1:14:15 AM
Thank you for your reply!
I think this problem not just "fkCalculated field's", 
Midas not sends provider property to ClientDataSet? So Client property is empty?

I already fix it by modify TSQLResolver!

> {quote:title=Fabricio Araujo wrote:}{quote}
> So it appears to be a bug somehow on Dbx4. Better
> create a report on QC.
> Otherwise, you can alter Provider.pas and apply it
> on your application.
> 
> 
> [Side note]
> I hope you don't take the question as a criticism,
> but I really became curious about it. When I use
> dbx or other components, I just put the sql and
> forget about it. I use calculated fields only on
> TClientDatasets.
> If it sound as a criticism, my apologies.
0
jackie
9/26/2008 9:48:09 AM
This might be related to a QC report I've just created:
    http://qc.codegear.com/wc/qcmain.aspx?d=67774

DBClient in Delphi 2009 doesn't correctly interface with Midas.dll, 
resulting in lost properties client-side (including the DisplayLabel and the 
Providerflags).

HTH,
Robert


"jackie chang" <no@mail.com.tw> wrote in message 
news:9019@forums.codegear.com...
> ClientDataSet -> DataSetProvider -> SQLQuery -> [Oracle Database]
>
> Add fkCalculated field into TSQLQuery.
>
> Execute ClientDataSet.ApplyUpdates cast error:
>
> ORA-00904: invalid column name
>
> In Call stack window, The TSQLResolver.DoExecSQL carries strange 'where' 
> clauses to PSExecuteStatement.
0
Robert
10/10/2008 7:41:09 PM
Thank for you post QC! I try version 12.0.3197.173709 work fine! This bug is 
gone.

I found another strange problem! use 12.0.3197.173709.

procedure TForm1.Button1Click(Sender: TObject);
var
  CommandText: WideString;
  ASQL:string; //UnicodeString;
  TmpParams: TParams;
begin
  CommandText:= 'SELECT * FROM SOMTETABLE WHERE ID=:ID';
  ASQL:= CommandText;
  TmpParams:= TParams.Create;
  try
    TmpParams.ParseSQL(ASQL,True)
  finally
    TmpParams.Free;
  end;
  MessageBox(Handle, PWideChar(ASQL),'Show SQL1',0);

  ASQL:= 'SELECT * FROM SOMTETABLE WHERE ID=:ID';
  TmpParams:= TParams.Create;
  try
    TmpParams.ParseSQL(ASQL,True)
  finally
    TmpParams.Free;
  end;
  MessageBox(Handle, PWideChar(ASQL),'Show SQL2',0);
end;

Show SQL1 Result
 'SELECT * FROM SOMTETABLE WHERE ID=?'
TParams.ParseSQ can change origin ASQL?

Show SQL2 is OK.
'SELECT * FROM SOMTETABLE WHERE ID=:ID'

Sorry! My English is very poor.
If you to verify this problem, Help me to post QC!

Thank!


"Robert Houdart" <robert.houdart@cruxis.be> wrote in message 
news:27921@forums.codegear.com...
> This might be related to a QC report I've just created:
>    http://qc.codegear.com/wc/qcmain.aspx?d=67774
>
> DBClient in Delphi 2009 doesn't correctly interface with Midas.dll, 
> resulting in lost properties client-side (including the DisplayLabel and 
> the Providerflags).
>
> HTH,
> Robert
0
jackie
10/11/2008 6:39:33 AM
Hi Jackie,

> Thank for you post QC! I try version 12.0.3197.173709 work fine! This bug is 
> gone.

Did you just violate your NDA? Version 12.0.3197.173709 is not a public 
available version (unless I've missed something)...

Groetjes,
           Bob Swart

-- 
Bob Swart Training & Consultancy (eBob42.com) Forever Loyal to Delphi
CodeGear Technology Partner -- CodeGear RAD Studio Reseller (BeNeLux)
Delphi Win32 & .NET books on Lulu.com: http://stores.lulu.com/drbob42
Personal courseware + e-mail support http://www.ebob42.com/courseware
Blog: http://www.drbob42.com/blog - RSS: http://eBob42.com/weblog.xml
0
Bob
10/15/2008 9:32:40 AM
ClientDataSet -> DataSetProvider -> SQLQuery -> [Oracle Database]
Add fkCalculated field into TSQLQuery,name is 'CalcField1'

at ClientDataSet  field editor,add all field,but 'CalcField1' fieldkind is fkdata,


sorry,my english is very poor,

Edited by: wise info on Oct 15, 2008 8:43 AM
0
wise
10/15/2008 3:45:04 PM
Reply:

Similar Artilces:

how to control a RadioButtonList's Listitem's text show according database's field
Hi my page has a repeater control,in repeater has a RadioButtonList control now i want to let the RadioButtonList has four Listitem,every Listitem's text is a database's field so the listitem can show dynamic according every record how can i work out my problem thanks a lot  hello dearbound your RadioButtonList to database if i m not clear fell free 4 replySatyabrat SinghB.E.(C.Sc.)Web developer Livetek Solution Dont forget to click "Mark as Answer" on the post that helped you. yes.i bound the radiobuttonlist to a dataset but how to display the ...

Can I update my Delphi 2007 lincense to Delphi 2009?
Hi all!! I have 12 lincense of Delphi 2007. When I bought these licenses to your partner told us that we could update to Delphi 2009 free. But, now I try to do that and I can't do. Are there any reason for that? Can I update my version of Delphi? > {quote:title=Oscar Estevez wrote:}{quote} > Hi all!! > > I have 12 lincense of Delphi 2007. When I bought these licenses to your partner told us that we could update to Delphi 2009 free. But, now I try to do that and I can't do. > Are there any reason for that? > > Can I update my version of Delphi? ...

second update panel's hidden field data loses when first update panel's procedure clicked
I am using asp.net v2.0 - my ajax enable web site have 2 update panels 1st Update panel updates a hidden field placed in the 2nd update panel - ok hiden fild is get updated edit procedure associated with in the first update panel then again try to read the value from the hidden field (it is database editing procedure, the hidden filed contain the Primary key). but when the edit procedure is clicked, hidden filed looses the data, the following event is get called when edit_procedure() is clicked Protected Sub hfTenantId_ValueChanged(ByVal sender As Object, ByV...

Bug in Delphi 2009's TDatabase
Hi, I found a bug in TDatabase of Delphi 2009. The bug is in TDatabase.GetDirectory method. The problem is casting to String to AnsiString. It generates a temporary string where the SDirectory is copied. This causes that Result will not be set at all and the memory where the temporary string is located gets correpted. function TDatabase.GetDirectory: string; var SDirectory: DBIPATH; begin if Handle <> nil then begin Check(DbiGetDirectory(Handle, False, SDirectory)); SetLength(Result, StrLen(SDirectory)); OemToCharA(SDirectory, PAnsiChar(AnsiString(...

Delphi 5 To Delphi 2009
I upgraded to Delphi 2009 from D5. The install says I can install Delphi and/or C++. Delphi installed OK but I see nothing of C++. What am I missing or does my upgrade not include C++? Thanks It depends on what you bought. If you bought Delphi 2009 only, that's what you get. If you bought Delphi 2009 and C++ Builder 2009 you get both. My guess is you got Delphi 2009 only. The simplest way to verify is look your invoice - it should say I would think. You could also go to members.embarcadero.com, login, then click on my registered products. There will be a textual description of...

Delphi 4 to Delphi 2009
Hello, Thanks to all who answered my previous question. That was a great help. And atlast our client agreed to upgrade our delphi version from 4 to Delphi 2009. *Sigh*. But before that, I need to give the estimation and cost regarding the migration to delphi 2009. Can anyone tell me is there any tool to migrate from delphi 4 to delphi 2009 or just I need to compile our Delphi 4 application in Delphi 2009. I have read from the delphi 2009 feature matrix that Delphi 1 through Delphi 2007 import is possible in delphi 2009. But i am not that sure considering the size of our application. ...

What's wrong with Delphi 2009 help system?
Having used this "help" system for two months, I must say it is a significant step back from D7 help file in usability. It is entirely possible that I am missing something here and that would be great; it would take care of my headache with this system but right now the move to the Microsoft Document Explorer looks like a bad idea to me. Case in point: 1. In the index, I select Filtered By language: Delphi. You would expect the thing to weed out any piece of help that is not delphi... I look for: DateUtils, Functions (selected from the list.) I land on a C++ de...

BLOB fields to replicate (EXE's,Dll's) Like live update
Is it possible to store program executables and Dll's in BLOB fields to replicate program updates through the database? Sort of like a live update feature. I would have an application that would read the database field and update the apprpriate program files (If possible) I am using version 8.0.2 build 4029. Thanks TPS You can put anything you want in a LONG BINARY or LONG VARCHAR column. Breck On Sun, 13 Jul 2003 19:10:52 -0600, "TPS" <ts@scientexlc.com> wrote: >Is it possible to store program executables and Dll's in BLOB fields to >repli...

PNG's not listed in Delphi 2009 IDE for SpeedButton
How come PNG's are not listed in the Delphi 2009 IDE for SpeedButton.Glyph? I thought the vcl was changed to support png images? Bill "Bill Miller" <@w2m00@frontiernet00.net> wrote in message news:26175@forums.codegear.com... > How come PNG's are not listed in the Delphi 2009 IDE for > SpeedButton.Glyph? Because TSpeedButton only supports BMP. > I thought the vcl was changed to support png images? TImageList and TPicture were updated. Anything that was previously hard-coded to TBitmap, such as various Glyph properties, was not changed....

Where's nsoftware's IP*Works in Delphi XE?
Hello, nobody in the Installation forum seems to know. Where's nsoftware's IP*Works in Delphi XE? Cheers, Tobias [http://www.superflexible.com] Hi Tobias +1 Best Regards Claudio "Tobias Giesen" ha scritto nel messaggio news:280997@forums.embarcadero.com... > Hello, > > nobody in the Installation forum seems to know. > > Where's nsoftware's IP*Works in Delphi XE? > > Cheers, > Tobias > > [http://www.superflexible.com] Tobias Giesen wrote: > nobody in the Installation forum seems to know. >...

Nick's European webinar 'A year in the life of Delphi'
Why was this only announced in Pawel Glowacki's blog just after it finished? I am a registered D2009 developer in Europe and heard nothing in advance. Quietly burying the bad news about the 64-bit compiler? Tim Frost wrote: > Why was this only announced in Pawel Glowacki's blog just after it > finished? I am a registered D2009 developer in Europe and heard > nothing in advance. Quietly burying the bad news about the 64-bit > compiler? I got an email about it yesterday, but could not participate. I am most interested to see/hear a replay of the show. When will ...

Does update of database using a DataSet's dataadapter.update method cause single or multiple trips to database ?
When I change data in a dataset and then update database using dataset's dataadapter.update  method, does the data get updated in one trip to database or multiple trips to database are involved in this update? There are multiple rows in the only datatable in a dataset. I think it would involve multiple trips, and to be exact, it will involve as many trips to the database as there are changed rows in dataset's datatable. BUT NOT SURE.sun21170 According to the online help, you are correct: When you call the Update method, the DataAdapter analyzes the changes that have been made and ex...

Dejan Crnila's TComPort in Delphi 2009- abandoned effort.
Has anyone else tried to port the well-known freeware component TComPort to Delphi 2009? I have spent days, and have given up. I have something that compiles but will not work. I have started over about five times. I have tried every possible approach known to me. I am absolutely stumped. I have not been this frustrated in years. Why? A combination of things: (a) The TComPort component was written using ancient Pascal (Turbo Pascal 3) and ancient Delphi (1.0) assumptions, including untyped-parameters, crazy amounts of pointer manipulation, very strange memory allocation...

Has anybody used OpenOffice Base's ODB as a database with Delphi?
How does it interface with Delphi? Is it similar to MDB? How good and reliable is Base's ODB? I do understand that Base itself can act as a front end to MySQL, and both are OpenSource with Sun support. > {quote:title=Anil Subbarao wrote:}{quote} > How does it interface with Delphi? Is it similar to MDB? How good and reliable is Base's ODB? I do understand that Base itself can act as a front end to MySQL, and both are OpenSource with Sun support. I think you could use an ODBC driver and then use ADO components unless they are direct components but I am not aware of them........

Web resources about - Delphi 2009 database update SQL includes fkCalculated field's condition? - embarcadero.delphi.multi-tier

Resources last updated: 1/22/2016 8:53:49 PM