MultiValue database access in Free Pascal? lazarus / 16-Databases ---------------------------------------------------------------------- maurobio · 2023-06-27 00:07 Dear ALL, Just wondering if there are any components for Lazarus that support a MultiValue (https://en.wikipedia.org/wiki/MultiValue_database) database management system (eg, Advanced Revelation, OpenInsight). Also, is there a way to emulate a multivalued-field (attribute) in a conventional relational DBMS? Thanks in advance! With best regards, ---------------------------------------------------------------------- Zvoni · 2023-06-27 06:53 Quote from: maurobio on June 27, 2023, 02:07:29 amDear ALL, Just wondering if there are any components for Lazarus that support a MultiValue (https://en.wikipedia.org/wiki/MultiValue_database) database management system (eg, Advanced Revelation, OpenInsight). No idea! QuoteAlso, is there a way to emulate a multivalued-field (attribute) in a conventional relational DBMS? JSON (Array) ---------------------------------------------------------------------- maurobio · 2023-06-27 11:24 Hi, @Zvoni! After I posted my question, I did what I should have done from the start: a search at Stackoverflow, that retrieved some interesting discussions! In fact, "MultiValue databases" (like AREV and the PICK operating system from which it was derived) are an earlier case of what is nowadays called "NoSQL databases" (eg., Redis, MongoDB, etc.). JSON key/value pairs and arrays are in fact the right way of handling data in such databases. Even some relational DBMS like more recent versions of MySQL already have a JSON data type which can be used exactly to handle multivalued fields (but still better, in my rather amateurish opinion, is to work directly with a NoSQL DBMS like MongoDB). I will explore this possibility in the near future. With warmest regards, ---------------------------------------------------------------------- SymbolicFrank · 2023-06-27 12:20 JSON fields are a pain in an SQL database, but if you're using a NoSQL one and only read/write the values for processing in code, that's not a problem. ---------------------------------------------------------------------- maurobio · 2023-06-27 20:40 Hi, @SymbolicFrank! QuoteJSON fields are a pain in an SQL database, but if you're using a NoSQL one and only read/write the values for processing in code, that's not a problem. That's what I thought too. I don't have much experience with NoSQL databases, but given their poower and flexibility, they are worth learning. With best regards, ---------------------------------------------------------------------- Zvoni · 2023-06-28 06:40 Quote from: SymbolicFrank on June 27, 2023, 02:20:57 pmJSON fields are a pain in an SQL database, but if you're using a NoSQL one and only read/write the values for processing in code, that's not a problem. Why would they be a PITA? AFAIK, SQLite and MySQL support JSON-Functionality out of the Box. No idea about other DBMS ---------------------------------------------------------------------- af0815 · 2023-06-28 11:23 MS SQL since V 2016 (13.x) https://learn.microsoft.com/en-us/sql/relational-databases/json/json- data-sql-server?view=sql-server-ver16 ---------------------------------------------------------------------- maurobio · 2023-06-28 11:34 Hi, @Zvoni, @af0815! See my post above: QuoteEven some relational DBMS like more recent versions of MySQL already have a JSON data type which can be used exactly to handle multivalued fields I am aware that MySQL already provides support for JSON, that is not the point. What I want is something different, and more appropriate to directly handling JSON data (like CouchDB or MongoDB). Best regards, ---------------------------------------------------------------------- SymbolicFrank · 2023-06-29 07:32 While some SQL databases allow you to parse the JSON fields with SQL statements, that's limited. They use special extensions and/or user- defined functions to handle them. Many basic PostgreSQL statements even break when they encounter a JSON field. So, you cannot treat the JSON values as fields and sometimes their existence breaks other SQL statements. If you want to store a table of values to be used by SQL statements, use a table, not a JSON field. They're mostly included because it seems easier to dump your REST request into that field instead of parsing it twice. And yes, at that point it's a text field, not a table. ---------------------------------------------------------------------- egsuh · 2023-06-29 09:37 I wish RDBMS support something like TStringList of Pascal. I mean functions like     Value_of (fieldname, name_of_namevalue_pair) both for reading and writing. The filed of fieldname would be BLOB type, and the content of fieldname should be the same as TStringList.Text. Of course I can implement this in  Pascal programming, but hope to use it in Firebird stored procedures, for example. Do anybody know DB that support such feature? ---------------------------------------------------------------------- SymbolicFrank · 2023-06-29 14:17 Quote from: egsuh on June 29, 2023, 11:37:38 amDo anybody know DB that support such feature? That's basically the idea behind NoSQL databases: lists of key/value pairs. The idea behind it is simple: SQL scales badly. It's hard to replicate the databases and very slow to run queries over multiple databases on different servers, especially when they're on different servers because one of them isn't capable of running all of it. And they still use locking mechanisms, which limits what connections/transactions can do. NoSQL removes everything that prevents scaling, and makes replication as easy as possible. Mostly by simplifying everything as far as it can go. ---------------------------------------------------------------------- nextjob · 2023-06-29 15:06 QuoteJust wondering if there are any components for Lazarus that support a MultiValue You could take a look at the Delphi / Free Pascal wrappers for OpenQM: http://www.tridentinfosys.com/tisQMClient.htm Or  ScarletDME (open source version of OpenQM 2.6) https://scarlet.deltasoft.com/index.php/Main_Page https://github.com/geneb/ScarletDME Very experimental / incomplete port of ScaletDME - this project creates a shared library which contains the ScarletDME There is a Free Pascal example https://github.com/nextjob/ScarletDME-dev ---------------------------------------------------------------------- maurobio · 2023-06-29 18:25 Hi, @nextjob! I have never heard before of OpenQM or ScarletDM. They look pretty interesting! Thank you very much! With warmest regards,