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.
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.
You can download the alp file here
or play and download it from the AnyLogic Cloud here
What next?
If you liked this post, you could read more posts by following the links above. 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, or the footer at the bottom. You can also join the mobile app here!
If you really want to make a difference in supporting us please consider joining our Patreon community here
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!