I am absolutely baffled by this so I thought I would throw it out here in order to see if anyone else has had a similar experience.
I am absolutely baffled by this so I thought I would throw it out here in order to see if anyone else has had a similar experience.
I am connecting to a PervasiveSQL server using FireDAC / ODBC. Everything seemed to be working fine, I can read and write data just fine using queries, but for some reason when I try to use direct access to a FDTable it crashes my server. It does this consistently on a few test servers I have tried. I am literally doing
FDTable1.Edit();
FDTable1['Field1'] := 'value';
FDTable1.Post();
As soon as post is called I get an exception with a message of "SQL_ERROR" (nothing else) and the server must be restarted.
Anyone ever experience this? This issue is making it impossible for me to even attempt to use FireDAC (this is my first production FD project). Any help or guidance appreciated!
Windows 32bit app developed in Win 10 x64 w/ Delphi 10 Seattle
I am connecting to a PervasiveSQL server using FireDAC / ODBC. Everything seemed to be working fine, I can read and write data just fine using queries, but for some reason when I try to use direct access to a FDTable it crashes my server. It does this consistently on a few test servers I have tried. I am literally doing
FDTable1.Edit();
FDTable1['Field1'] := 'value';
FDTable1.Post();
As soon as post is called I get an exception with a message of "SQL_ERROR" (nothing else) and the server must be restarted.
Anyone ever experience this? This issue is making it impossible for me to even attempt to use FireDAC (this is my first production FD project). Any help or guidance appreciated!
Windows 32bit app developed in Win 10 x64 w/ Delphi 10 Seattle
Which Delphi version do you use?
ReplyDeleteOriginal post edited to reflect 10-Seattle (Sorry!)
ReplyDeleteBoth TFDQuery and TFDTable are both descended from TFDCustomQuery.
ReplyDeleteFrom the TFDTable doc: "Use TFDTable to browse a database table and edit its records. TFDTable generates a SELECT statement based on its properties and sends it to a DBMS."
So one you specify the query (which works) and the generated one doesn't (obviously).
What does your SELECT statement that works look like?
What server do need to restart. The RDBMS? And - are you connection in some kind of "exclusive" mode (like embedded)?
ReplyDeleteDany Marmur Yes, the server OS is fine. I just need to restart the Pervasive RDBMS service.
ReplyDeletejeff weir I will try enabling FD tracing and see if that gives me any more info? What I am trying to do is extremely simple though so I can't see where it might be going astray.
ReplyDeleteThere are dozens of other SQL updates, appends, deletes, etc. and I have not had an issue...
This indicates to me (and this is speculation) that some connection parameter + something else is askew and most likely on the client (DAC) side. You should not be able to crash the (external) RDBMS by putting garbage into SQL and or parameters. You may be able to mess with the data though.
ReplyDeleteDoes Pervasive have a cap on recursion? If you have to restart the RDBMS maybe you have triggers triggering triggers? But the the same problem would surface if you issue the same SQL from a desktop-tool.
Thus a good start to pin it down is first to watch the "SQL Monitor" (maybe that is what Jeff suggested) and then do similar / same from another client (preferably a vetted desktop client for your RDBMS).
More detail... I enabled tracing and in addition to about a thousand pages of gobbledegook, the following seems to be where it is dying:
ReplyDelete12749096504091 10:23:26.581 >> Execute [Command="UPDATE SVRDATA.BATHEAD
SET BATCHTYPES = :NEW_BATCHTYPES, NEXTITEM = :NEW_NEXTITEM, ITEMCNT = :NEW_ITEMCNT,
PENDINGCNT = :NEW_PENDINGCNT
WHERE STOREID = :OLD_STOREID AND BATCHID = :OLD_BATCHID AND CREATEDATE = :OLD_CREATEDATE AND
CREATETIME = :OLD_CREATETIME AND CREATEDBY = :OLD_CREATEDBY AND
SOURCESTOREID = :OLD_SOURCESTOREID AND DESTINATIONZONE = :OLD_DESTINATIONZONE AND
CREATEDAS IS NULL AND DESCRIPTION = :OLD_DESCRIPTION AND
BATCHTYPES = :OLD_BATCHTYPES AND SOURCE = :OLD_SOURCE AND
DATETOEXECUTE = :OLD_DATETOEXECUTE AND TIMETOEXECUTE = :OLD_TIMETOEXECUTE AND
EXECUTEMODE = :OLD_EXECUTEMODE AND DATEEXECUTED = :OLD_DATEEXECUTED AND
TIMEEXECUTED IS NULL AND LABELSREQUIREDDATE = :OLD_LABELSREQUIREDDATE AND
LABELSPRINTEDDATE = :OLD_LABELSPRINTEDDATE AND DEEXECUTEDATE = :OLD_DEEXECUTEDATE AND
DELETEDATE = :OLD_DELETEDATE AND STARTDATE = :OLD_STARTDATE AND
STARTTIME = :OLD_STARTTIME AND ENDDATE = :OLD_ENDDATE AND
ENDTIME = :OLD_ENDTIME AND NEXTITEM = :OLD_NEXTITEM AND
ITEMCNT = :OLD_ITEMCNT AND PENDINGCNT = :OLD_PENDINGCNT AND
IGNOREDCNT = :OLD_IGNOREDCNT AND EXECUTEDCNT = :OLD_EXECUTEDCNT AND
DEEXECUTEDCNT = :OLD_DEEXECUTEDCNT AND LINKEDBATCH IS NULL AND
DATEEXPORTED IS NULL", ATimes=0, AOffset=0]
12749098161608 10:23:27.053 >> RecoverConnection [ConnectionDef=""]
All the pervasive log says is "resources allocated" every time the server restarts.
The only thing that looks radically different from the types of queries that I am using by hand is that the where clause is specifying ALL other fields, not just the key fields.
ReplyDeleteNathan Wild Look at the UpdateOptions property of TFDTable.
ReplyDeleteOther DACs do that when they do not know what field is the primary key. You should be able to define that somewhere. The resulting "command" would then be much less verbose.
ReplyDeleteUPDATE SVRDATA.BATHEAD
SET BATCHTYPES = :NEW_BATCHTYPES, NEXTITEM = :NEW_NEXTITEM, ITEMCNT = :NEW_ITEMCNT,
PENDINGCNT = :NEW_PENDINGCNT
WHERE STOREID = :OLD_STOREID AND BATCHID = :OLD_BATCHID
(If /those/ fields have a unique index)
On another note (but i do not know Pervasive) too many expressions should issue an exception "SQL / Command too complex" or some such. Because the SQL above (the one posted by Nathan) really do not look very big at all.
So i think that there's something else happening. Maybe you columns a HUGE blobs and that makes the RDBMS croak? Perhaps on to the Pervasive logs and / or a debug build?
jeff weir and Dany Marmur pointed me in the right direction. I think somehow the complexity of the update query broke something in the server. Specifying the key fields in the UpdateOptions seems to have done it. Thank very much!
ReplyDeleteAttila Kovacs The TFDTable default is upWhereKeyOnly but that does not guarantee that FireDAC can figure out what the key column is all on its own eh. If it can't obviously it falls back to operating as if UpdateMode is upWhereAll.
ReplyDeletejeff weir, Attila Kovacs. That propery is TDataset, i forgot.
ReplyDeleteJeff, maybe look for another RDBMS or an update?
Cool you solved it.
Dany Marmur Why would I use a different database? I don't have any issues - SQL Anywhere is supported natively by FireDAC ;-)
ReplyDeleteAttila Kovacs Your table definitions must explicitly declare primary keys (which is normal) that UniDAC (and FireDAC for that matter) can determine by looking database system tables.
ReplyDeletejeff weir because as Attila says that little query should not break a service.
ReplyDelete