Classic DataSnap Application with UniDac (Postgre Driver)

derekwildstar · Post by **derekwildstar** » Sun 01 Apr 2012 14:57

Hello everybody...

(If you want, skip to the bold text, because i like to explain too much

)

My company will soon acquire UniDAC licenses. There, we work with Oracle and Sybase.

Before we decide to use the UniDAC, I made some attempts with DBExpress and Oracle, finding that alternative would not be interesting because DBExpress is unidirectional and the effort to convert our systems based on BDE would be too big, since they are simple "client/server" systems and do not use TClientDataSet/TDataSetProvider, only TStoredProc. I know this kind of system architecture is very poor, but they are legacy systems created by people without much experience.

Until then, I did not know that the DBExpress was unidirectional, because I had never used before. When looking for more information on the characteristics of the unidirectional DBExpress, I understood that it would be perfect in my personal project.

My personal project is a classic DataSnap, which uses a WebService as middleware. In my personal project I use ZeosLib (OpenSource), but I found out he has some serious limitations for some DBMS and for this reason I am considering purchasing a license UniDAC. I installed a trial version and am doing the conversion from ZeosLib to UniDAC.

Now I want all my components on the server are unidirectional, so. The component has an option TUniQuery unidirectional, which I set to "True", however, I saw in the help that is needed fetchAll also set the option to "False."

1. Just this is enough for the UniDAC to function just like unidirectional DBExpress?

2. What is the real effect of having unidirectional=True and fetchAll=True?

3. I noticed that the DBExpress when attempting to connect to a DBGrid component TDataSet descendants of an exception was raised saying that the operation is not possible with a unidirectional dataset. With UniDAC I did not notice anything unusual, even when Unidirectional = True. This property really work? In what way?

AlexP · Post by **AlexP** » Mon 02 Apr 2012 15:45

Hello,

The Unidirectional mode is designed for reducing of the used memory size. In this mode, DataSet is unidirectional, and you cannot return to the previous fetched records, since every next block rubs the previous one, and you can move only in records of the current data block. The block size can be set in the FetchRows property. If you set the FetchAll property to true, all the data will be fetched at once in DataSet and the Unidirectional property will lose sense. Oppositly to dbExpress, UniDAC doesn't generate an error when attempting to move up in Unidirectional DataSet, however, such navigation will be still impossible: when attempting to move up in DataSet, you will stop at the first record of the last read data block.

derekwildstar · Post by **derekwildstar** » Mon 02 Apr 2012 20:55

Hello AlexP

So, I *MUST* set UniDirectional=True and FetchAll=False to achieve the REAL unidirectional behaviour? Understood!

Humm...

Setting the Unidirectional property to true would set the FetchAll property to False. That would help a lot!

Well, my project is a DataSnap project and its middleware is a WebService. It's correct and safe to use UniDirectional=True and FetchAll=False in this case?

FetchRows property defaults to 25 on TUniQuery. If my ClientDataset on the client application sets a param which causes the SQL server to returns 30 records. What's the behaviour? This would raise problems to me?

More...

If my query is to return 30 records, considering the DataSnap (request-response), then setting FetchRows to a value less than 30 would return less records than the total of records? The connection is closed BEFORE i get all records?

Thanks for the explanation

AlexP · Post by **AlexP** » Tue 03 Apr 2012 11:05

Hello,

When setting the Unidirectional property to true, the FetchAll property is automatically set to False, so you don't need to change it manually. The FetchRows property value shows the number of records that will be retrieved in one package per one server call, i.e. if there are 30 records in the table, and the FetchRows property value is 25, there will be 2 calls to the server for retrieving all records. If there are less records in the table then the value of this property, there won't be any problems, since you retrieve only the records that are really in the table. The FetchRows property value should be chosen experimentally for retrieving the highest performance in every particular case

derekwildstar · Post by **derekwildstar** » Tue 03 Apr 2012 13:09

AlexP wrote:When setting the Unidirectional property to true, the FetchAll property is automatically set to False, so you don't need to change it manually.

Are you sure? I've set up one TUniConnection to connect to my local postgres database. I used one TUniQuery and I connected it on my TUniConnection.

I saw the SpecificOptions of TUniQuery and there, the FetchAll property is True (default). This is correct.

After I've set up the UniDirectional to TRUE at TUniQuery and I saw again the SpecificOptions. There, the FetchAll property remains TRUE.

Well, this is not a big problem, but i need set manually every time I need a new TUniQuery

AlexP wrote:The FetchRows property value shows the number of records that will be retrieved in one package per one server call, i.e. if there are 30 records in the table, and the FetchRows property value is 25, there will be 2 calls to the server for retrieving all records. If there are less records in the table then the value of this property, there won't be any problems, since you retrieve only the records that are really in the table. The FetchRows property value should be chosen experimentally for retrieving the highest performance in every particular case

Again another perfect answer! Thank you! The FetchRow property is something like the PacketRecords property on TClientDataSet.

AlexP · Post by **AlexP** » Wed 04 Apr 2012 09:00

Hello,

Yes, in fact, the automatic change of the FetchAll property when changing UniDirectional in UniDAC is not implemented, this functionality is implemented in PgDAC.
Our FetchRow property is similar to the PacketRecords property in ClientDataSet, except the case when PacketRecords is equal to 0, in this case ClientDataSet receives only the metadata about the fields without getting the data themselves. The FetchRow property cannot be equal to 0, so to get the metadata in UniQuery you have to specify an additional condition in the query, e.g. WHERE 11.

derekwildstar · Post by **derekwildstar** » Wed 04 Apr 2012 12:35

AlexP wrote:Our FetchRow property is similar to the PacketRecords property in ClientDataSet, except the case when PacketRecords is equal to 0, in this case ClientDataSet receives only the metadata about the fields without getting the data themselves. The FetchRow property cannot be equal to 0, so to get the metadata in UniQuery you have to specify an additional condition in the query, e.g. WHERE 11

Yes, I know about the -1, 0 and >1 possible values of PacketRecords (TClientDataSet). And thank you for the explanation about how to get metadata in TUniQuery. Thank you very much!

AlexP · Post by **AlexP** » Wed 04 Apr 2012 13:21

hello,

If you have any other questions, feel free to contact us.

derekwildstar · Post by **derekwildstar** » Wed 04 Apr 2012 14:06

Hi!

As you talk about

I have another question.

As i said before, my personal project is a DataSnap project with a WebService as Middleware. And I'm converting it from ZeosLib to UniDAC. With ZeosLib everything was fine but now, after replace the similar components I'm getting some trouble.

Here is the scenario:

On the client i click the refresh button. This action performs a simple refresh on the TClientDataSet. All records (5 records) are being fetched correctly.

Now I click the insert button and fill the only two fields (name and description) and press the post button. With this, the record is posted locally (TClientDataSet). Until now, everything seems to be perfect.

Finally I want to confirm this new insertion, so i click the Apply button (ApplyUpdates) and the folowing error is raised:

Code: Select all

---------------------------
Debugger Exception Notification
---------------------------
Project IAFClient.exe raised exception class ERemotableException with message 'One of the connections in the transaction is not active'.
---------------------------
Break   Continue   Help   
---------------------------

On the server, the TUniClient subject of this insert is linked to another TUniQuery, a detail dataset which is not being managed on the client screen, so it is untouched. This information is only to expand the scenario.

I thought the Web Service was a stateless server, each request that did not know anything about the previous request. Thinking that way I was using the ZeosLib perfectly. Thus, I thought in my architecture, on insert a record at client, the function SAS_ApplyUpdates sent a request to the server with the DELTA filled in this case with my new record.

On the server, the function SAS_ApplyUpdates, when invoked, uses the name of the provider (ProviderName) DataModule to detect what needs to be created, so each request only the minimum number of DataModules is loaded into memory. In this example scenario, only the main DataModule and the DataModule that contains the updateable UniQuery are created and I thought that after the execution of SAS_ApplyUpdates the connection would be lost after the record inserted correctly.

The erro message does not make too much sense for me. Can you try to explain?

Thanks in advance!

derekwildstar · Post by **derekwildstar** » Thu 05 Apr 2012 05:42

I found the solution for the last problem and now I have another problems :S and new questions.

The problem was that the TUniConnection on my main datamodule (on server) wasn't being automatically activated (connected) by the SAS_ApplyUpdates. Before, with ZeosLib, the behaviour was that the connection were always activated on ANY client action (fetch/select, update, insert or delete). Why the UniDAC does this way? I'll need to connect my TUniConnection after my main datamodule be created. This is not a very hard work, but i need to know if this UniDAC behaviour is expected and why. I'm really curious!

After manually connect my TUniConnection the previously mentioned error message is gone, and after that, I was fighting with another problem related to param types. On TDataSetProvider.DoAfterUpdateRecord I'm doing some things to return the last generated id (sequence). On ZeosLib, everything was working like a charm!

With SpecificOptions.UseParamTypes=False, an error was raised telling me that could not detect the param type for param $1. Question: If I need to create a dynamic TUniQuery like this case I ALWAYS need to inform the param types and set the SpecificOptions.UseParamTypes=True? This is a little bit annoying, but it's OK.

After set the SpecificOptions.UseParamTypes=True and set the types of the params the error is gone, but now a new error comes:

Code: Select all

Large object 18141 does not exists

This error, is raised when the Open method of my dynamically created TUniQuery is executed. The query is the following:

Code: Select all

SELECT ARD.adrelid
     , ARD.adnum
     , ARD.adbin
     , pg_get_expr(ARD.adbin, ARD.adrelid) AS defaultvalue
  FROM pg_attribute ATT
  JOIN pg_attrdef ARD ON (ATT.attrelid = ARD.adrelid AND ATT.attnum = ARD.adnum)
 WHERE (ATT.attrelid = CAST('grupos' AS regclass))
   AND (ATT.attname = 'sm_grupos_id')

Executing the same SQL directly on PostGre I'm getting one record with the following columns and datatypes between parenthesis:

Code: Select all

adrelid (oid)
adnum (smallint)
adbin (pg_node_treee)
defaultvalue (text)

The "adrelid" has the value of 18141, i.e., the same number shown on the error message! Previously with ZeosLib i wasn't having this problem. What can I do about it?

AlexP · Post by **AlexP** » Thu 05 Apr 2012 08:59

Hello,

This error is connected with the fact that the "adrelid" field contains a link to an object that does not exist.
Fields with the OID type are for storing integer values that are pointers to database objects. So if you see a filed with the OID type and the OIDAsInt property is set to false, we are trying to receive an object by this link, and, if this object does not exist, you get the corresponding error message.
To resolve the problem, you should set the OIDAsInt specific option to True.

derekwildstar · Post by **derekwildstar** » Thu 05 Apr 2012 12:40

Hello, Mr. Alex!

Thank you for the answer, a perfect direct answer, indeed!

And about the another issues (automatic connection and use of dynamic TUniQuery creation and parameters types)?

AlexP wrote:Hello,

This error is connected with the fact that the "adrelid" field contains a link to an object that does not exist.
Fields with the OID type are for storing integer values that are pointers to database objects. So if you see a filed with the OID type and the OIDAsInt property is set to false, we are trying to receive an object by this link, and, if this object does not exist, you get the corresponding error message.
To resolve the problem, you should set the OIDAsInt specific option to True.

AlexP · Post by **AlexP** » Fri 06 Apr 2012 12:16

Hello,

When opening ClientDataSet at first time, the connection is set automatically even if it wasn't set before, and the error you mentioned can occur in case if the explicit disconnecting from a DB was called between opening ClientDataSet and calling the ApplyUpdate method.
We cannot reproduce the problem with the parameters, please send a small sample reproducing the problem to alexp*devart*com

derekwildstar · Post by **derekwildstar** » Fri 06 Apr 2012 13:44

Well, i have no time to make a sample right now, I'm sorry, but seems you are thinking that my project is a simple client/server, but it's not!

My project is in 3 layers.

1. Client
2. MiddleWare
3. DatabaseThe clients have several TClientDatasets and components to make connection to the middleware which is a WebService. The Middleware (WebService) has the UniDAC components (TUniConnection, TUniQuery).

The first case is a simple refresh on one of TClientDatasets which returns all records of the connected DataSet. This means that the TUniConnection is being activated automatically.

The second case is when I'm trying to insert a record. On the client i use one of the TClientDatasets: insert, fill the record, post, ApplyUpdates. When I Apply the updates, the server responds with a local exception (this is normal) saying: One of the connections in the transaction is not active.

I've made some tests and really the connection is closed, but the same tests show that the connection is closed on the first case (sucessful) also, so why only on the second case (insert record) the test fails? This is confusing me.

Well, if you could not reproduce the problem, does not matter because I solved It, opening manually the connection on the creation if its DataModule. This way seems more logic to me, but the curiosity persists: Why with ZeosLib I had no problem leaving the connection component disconnected?

AlexP · Post by **AlexP** » Wed 11 Apr 2012 13:08

Hello,

When calling the TClientDatasets.Refresh method, the Open method of UniConnection is called automatically, and it opens the connection to a DB. Therefore, in this case, you don't get any errors. When calling the Insert method, the UniQuery opening doesn't occur, and if the connection to a DB is closed, you get this error. To avoid problems and the necessity to open connections manually, you can try using the Disconnect mode, in which connections are set on demand, and there will be no error in both your cases.

P.S. In the Disconnect mode, you don't have to call the DB connection explicitly, as connections are set only when directly calling DB