How to store a single value into a MySQL database?

[jacdelad]

Hi,
I have a MySQL database with some data. Now I want to save the timestamp of the last change stored. I don't want to add a column and save it for ever line, I just want ONE value. Is it possible to do this?

[Paul]

Code: Select all

UseSQLiteDatabase()

If CreateFile(0,GetCurrentDirectory()+"mydata.sqlite")
  CloseFile(0)
EndIf

db=OpenDatabase(#PB_Any,GetCurrentDirectory()+"mydata.sqlite", "", "",#PB_Database_SQLite)
If db
  DatabaseUpdate(db,"CREATE TABLE settings(timestamp varchar(10));")
  DatabaseUpdate(db,"Insert into settings (timestamp)values('2023-07-19');")
  
  DatabaseQuery(db,"Select timestamp from settings;")
  If NextDatabaseRow(db)
    Debug GetDatabaseString(db,0)
  EndIf
  
  DatabaseUpdate(db,"Update settings set timestamp='2023-05-22';")
   
  DatabaseQuery(db,"Select timestamp from settings;")
  If NextDatabaseRow(db)
    Debug GetDatabaseString(db,0)
  EndIf
  
  CloseDatabase(db)
EndIf

[spikey]

Or you could use triggers to update a timestamps table. You can create multiple triggers if you want to track inserts, updates and deletes separately but these could all update the same row in the timestamps table if you aren't worried about differentiating. This way the engine will make sure that the timestamps table is always updated without having to put update code in the application.

Code: Select all

CREATE TABLE ChangeTimestamps
(
KeyID INTEGER PRIMARY KEY NOT NULL,
KeyName TEXT NOT NULL UNIQUE,
KeyTimestamp TEXT DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO ChangeTimestamps(KeyName) 
VALUES ('ObservedTableNameInsert'),
('ObservedTableNameDelete'), 
('ObservedTableNameUpdate');

CREATE TRIGGER ObservedTableInsertTimestamp
AFTER INSERT ON ObservedTableName
BEGIN
  UPDATE ChangeTimestamps
  SET KeyTimestamp = CURRENT_TIMESTAMP
  WHERE KeyName = 'ObservedTableNameInsert';
END;

CREATE TRIGGER ObservedTableDeleteTimestamp
AFTER DELETE ON ObservedTableName
BEGIN
  UPDATE ChangeTimestamps
  SET KeyTimestamp = CURRENT_TIMESTAMP
  WHERE KeyName = 'ObservedTableNameDelete';
END;

CREATE TRIGGER ObservedTableUpdateTimestamp
AFTER UPDATE ON ObservedTableName
BEGIN
  UPDATE ChangeTimestamps
  SET KeyTimestamp = CURRENT_TIMESTAMP
  WHERE KeyName = 'ObservedTableNameUpdate';
END;

[jacdelad]

Thanks you both, I'll need a while for that.

@Paul: If I get this right you create a table to store the value. That's surely a possible way, but not what I expected to do.
@spikey: I will answer on that later, my MySQL needs some tuning.

[Marc56us]

Good idea @spikey!
It's true that we rarely think of using triggers to automatically update certain information (especially dates).
It frees up the main coding and SQL query.
It also allows you to update the date when you make a manual correction to the database.
I'll keep this example.
Thanks

[spikey]

It also allows you to update the date when you make a manual correction to the database.
Thanks

You're welcome. Yes, it's useful for auditing purposes because it doesn't rely on any code in the front end.

I forgot to mention that you may need to update the table permissions before it will work properly, if triggers aren't already in use. See https://dev.mysql.com/doc/refman/8.0/en ... igger.html

[Nituvious]

Since the question has been answered I figured I would offer some tips for learning MySQL

#1 Download MySQL Workbench. With this software you can play around with local databases and learn without consequence. LINQPad is also a useful tool for SQL.
#2 If you're using MySQL for anything remotely serious then you should learn about "stored procedures". They are so much faster and safer than a typical query.
#3 MariaDB is an open source fork of MySQL and the community considers it the linux version of MySQL. So if you're trying to setup a server and can't find a MySQL package look for MariaDB instead.
#4 With relational databases you should take care with your joins because you can run into recursion problems with no real way to know that that's why your queries take ages or max your server memory out.

[jacdelad]

Thanks for that. I use MariaDB at home, but I mostly porgram at work. We have a huge server running whatever, but I have my own scheme so I can play with it. Had no time yesterday to look into the solutions to my initial question.
I'm still learning how to use the potential of MySQL, so these tips are well appreciated.