Jump to content

Leaderboard


Popular Content

Showing content with the highest reputation on 03/24/2020 in all areas

  1. 1 point
    jarto

    Database development

    I've been doing some work to create data aware controls for SMS. I'm aiming to do a versatile solution, where you can access data from a TDataset by using a TDataLink, no matter what the data comes from. So it could be a cvs-table, json, sql query etc. I started by building a TW3DataSet (and fixing some bugs) and writing a simple TW3DataSource and TW3DataLink. Then a data-aware Label, which shows the data properly. It's pretty much the same mechanism as with Delphi and Lazarus. Today I started then adding SQLite. I started by downloading a database file with TW3HttpRequest and loading it from a stream. Again, a few bugs and improvements had to be made, but now it works and I have a nice mechanism for loading any binary data from a file. But now then... Making it possible to read the results of a SQLite query through a dataset.... Here I hit a little road block and don't know how to proceed. It's simple to make a select-query and access that data for results, but what's the best way for me to know what the fields are? Their names and datatypes?
  2. 1 point
    jarto

    Database development

    Heh, well, this actually looks now a lot easier. Sqllite actually returns the parsed column names in the results. It even works with field aliases: lower(employees.FirstName) as Representative That was correctly returned as Representative
  3. 1 point
    jarto

    Database development

    Reading a bit of Lazarus source, it seems they don't have a full blown sql parser. For example, they don't allow updates if the select is from multiple tables or derived tables. So it's probably a good idea to aim at being able to show a dbgrid from a select-query and try to retrieve and parse the field names. I'd better add a possibility to provide or adjust field names as well. After all, if you use complicated SQL, you do know what your columns are and what you want to view.
  4. 1 point
    lynkfs

    Database development

    They must have some sort of an sql parser. Some links : Commercial vcl (delphi tokyo) parser : http://sqlparser.com/download.php http://sqlparser.com/dlaction.php?fid=gspdxe10_2_tokyo_trial&ftitle=General%20SQL%20Parser%20VCL%20for%20delphi%20XE10.2%20tokyo%20trial%20version and some really old ones https://torry.net/quicksearchd.php?String=sql+parser&Title=Yes http://www.felix-colibri.com/papers/db/sql_parser/sql_parser.html to write a complete sql parser from scratch might be a big job
  5. 1 point
    jarto

    Database development

    Seems like DBGrid in Lazarus can parse this properly as well: select lower(customers.FirstName) as FirstName,lower(customers.LastName) as LastName, lower(employees.FirstName) as Representative from customers,employees where customers.SupportRepId=employees.EmployeeId; The dataset used is chinook.db from here: https://www.sqlitetutorial.net/sqlite-sample-database/
  6. 1 point
    jarto

    Database development

    @lynkfs Thank you. It's possible to get the table fields with a query in SQLite as well. For example: select sql from sqlite_master where tbl_name="Employee"; That returns: CREATE TABLE [Employee] ( [ID] INTEGER PRIMARY KEY NULL, [Name] TEXT NULL, [Department] text NULL, [Seniority] INTEGER NULL ) So, if the query selects all fields from that table (select * from Employee....), it's quite simple to find the field name. However, when only some fields are selected (select Name, Department from Employee...), I'd need to analyze also, which fields are selected. Problem is, this can get pretty complicated. I have no control over what the sql-query is. There can possibly be joins, functions etc. For example: select Name,lower(Department) from Employee; select Name,lower(Department) as Position from Employee; When I test those, I notice that Lazarus' TDBGrid does add the second column as "lower(Department)" or "Position". So it seems that it analyzes the sql-query itself and creates the fields from there. Makes sense. Then, the next big question is editing data. In a simple "select *" it's easy, but I wonder if the other data aware controls simply prevent editing, when the field name is a function or an alias.
  7. 1 point
    lynkfs

    Database development

    I guess there are a couple of approaches : - Have access to the meta information when parsing results. Most SQL based rdbms's have type information stored in meta tables, which is accessible by a normal query. Unfortunately this is not standardised, so MySQL, Oracle, DB2, SQL server etc have differently named tables for that purpose. For SQLite see this link. - Let the developer define the meta data in the designer while setting up the data link, and store it somewhere persistently (TDatalink ?). Then parse the results with this stored meta data. - If you only want to know if a field is numeric or not (I don't think that's your question, but anyway) then there is the poor mans solution : just read the first 100 records or so, and if a field only contains 0-9 then assume that field to be numeric across the whole result set. Not recommended (but I did it once to figure out the left or right alignment of fields in a grid)
×