The dark side of the database...

Updated: Nov 12, 2021

Have you ever used the database and experienced some strange behavior? As if the database was not being updated or refreshed? The value that you just inserted into the database is not being retrieved, yet when you close your model and review the database the values are there....


Well, you are not alone!

At the bottom of the post, there are two examples of other users on Stack Overflow that experienced the "dark side" of using database tables in AnyLogic.



gif

Example


Suppose you have a database table with some key information for your model.


And during runtime, you want to access the information using the selectFrom() function



And you also want to update some information in the table using the update() function



So you run your model

You get the value from the DB and it returns, as expected, 22.

Then you save a new value to the DB, 25

You want to get this new value from the DB but it still returns 22....

You close the model.

You look at the DB value

It shows you 25



But how can this be?


You run the model again...

You get the DB value, it returns 25, as expected.

You change the value to 30.

You get the DB value.. it still returns 30.

What trickery is this!


You keep the model open and look at the DB the value shows 25...

You close the table and open it again... it shows 30....


You get the DB value, it still returns 25...


You close the model

Run the model again...


You get the DB value, and it returns 30!


You throw your hands in the air, exclaim some profanity and vow to never use the database again!


P.S. Watch the video at the bottom of the post to see me roleplay this event ;-)


But wait...

There is a good reason why you are seemingly not able to get the correct value from the DB.

If you check the firstResult() documentation you will see the following





By default, the firstResult() function will return the cached value!


If you always want to get the latest value you have to call firstResult(false, "column")


The reason why there is even a cached option is in order to speed up the DB processing, the Dark side of this is that it is enabled by default with no warning to the beginner user since the function will still work if you omit the cached parameter.


Also!

keep in mind that a few other functions have the same "feature":

  • firstResult()

  • uniqueResult()

  • count()

  • hasResults()


Note: This will be the case for any updates to your DB, whether you are using insertInto() or ModelDatabase.importFromExternalDB






What next?

If you liked this post, you are welcome to read more posts by following the links above to similar posts. Why not subscribe to our blog or follow us on any of the social media accounts for future updates. The links are in the Menu bar at the top of the footer at the bottom.


If you want to contact us for some advice, maybe a potential partnership or project or just to say "Hi!", feel free to get in touch here and we will get back to you soon!



References

Stack Overflow users that also experienced the dark side of using database tables in AnyLogic.

Example 1

Example 2



You can download the alp file here

Dark side of Database
.zip
Download ZIP • 15KB

or play and download it from the AnyLogic Cloud here






233 views0 comments

Recent Posts

See All