Records not in database but visible in smartclient

Classic Servoy
1

Hi guys,

I have a small problem.

When we went to the new year, the customer reported that the newly imported bank statements where stil listed as starting with a 9, indicating the year.
I found out that the previous developer had been using an auto-enter custom value for the first part of the strings used, so I decided to change this to 10.

This worked fine but the customer was left with some records with the wrong id’s and asked me to rectify this on the database.

I ran a query updating the already inserted records to change the original 9 to a 10 so that on following calculations it would return the correct value.

To demonstrate, this is the calc returning the field displaying the id:

return s_year_t + bank_uittrekselline_to_bank_uittreksels.s_uittreksel;

I changed the s_year_t to 10 where it was 9 in the database for all records created in the new year.

Since the new version is not on the production server yet, it will still return 9 for the newly created records.

The client has loaded 3 new records, which are marked 9xxx.

Here comes the problem.

They asked me to rectify the records again before we bring the new version to production, however I cant find the records in the database!!

When i do my checks on the database itself the last of the adapted records as well as the two new records are not in the database!

I am a bit lost as to why this is.

I have not restarted Servoy after the database updates, perhaps this is needed?

When you go in the client you do actually see the records there, but they are not to be found in the database.

I can not find anything of interest in the logs that would indicate something being wrong.

It looks like servoy has cashed the data somewhere but has not written it to the database.

would a restart of the server cause it to synch with the database?

Can someone explain to me what happened?

Many thanks in advance for your support on this issue.

Kind regards,
John

2

Hi John,

Yes this is a caching issue. When you change data outside of Servoy then already cached data is shown in the clients instead of your changes.
You can flush the caches of the clients or when you also make schema changes you should relaunch the server.

Hope this helps.

3

I’m not sure if we’re on the same page.

I am checking the records and can see them, even though I have just connected my client.

In addition the changes were made during a time where no clients were connected.

When you talk about caching, do you mean caching between Servoy server itself and the database?

And thus I would need to restart the server at any change i make through sql updates at any time?

Thank you for your feedback.

4

I’m also a bit confused as to why the last of the adapted records is visible in the smartclient with the correct 10xxxx but not in the database.

I assume this has to do with the caching mechanism servoy uses?

Any change of a reference on how this caching works so I can avoid this situation in the future?

5

Hi John,

Maybe I am the one that is confused.

Did you change the data via Servoy or some other tool ?
Also is s_year_t a column in the database or some Servoy unstored calculation or variable ?

6

I ran an update through an sql client to update the faulty records.

The s_year_t field is a column in the database which is defined in Servoy under the “Auto Enter” tab to return the custom Value 9 at this moment in production.

7

I tried restarting the server but this did not help :-(.

The records are still visible to the client in the smartclient though, which is a bit of a positive thing.

I am considering shutting down the service and restarting it along with the database.

8

good news!

Restarting all the services, including the db service, resolved the problem.

Many thanks once again for your support.

Regards,
John

9

There are two solutions for this issue.

Method -1:

If you are sorting on a related value, MAKE SURE these fields always have some VALUE on them.

Method – 2:

Change the ‘Join Type’ of the relation to ‘left outer join’. That’s in our case, change the ‘Join Type’ of the relation ‘product_to_catagory’ to ‘left outer join’.

Eliza
http://www.mindfiresolutions.com/Avoiding-dataloss-during-sorting-records-in-Servoy-482.php

10

Hi Eliza,

Was this perhaps a reply for this thread ?