[solved] Table names in sqlite3 database lazarus / 16-Databases ---------------------------------------------------------------------- Joanna · 2023-06-14 00:39 Hello does anyone know how to get a list of table names I created from sqlite database. I’m currently trying to use tsqlquery to do this but I don’t know what the syntax for query is. Any suggestions ? ---------------------------------------------------------------------- arodriguezfarfan · 2023-06-14 01:11 Hello. You can execute the next command for list all tables SELECT * FROM sqlite_master WHERE type='table' ---------------------------------------------------------------------- Zvoni · 2023-06-14 06:01 Or use "GetTableNames" of TSQLConnection: https://www.freepascal.org/docs- html/fcl/sqldb/tsqlconnection.gettablenames.html ---------------------------------------------------------------------- Joanna · 2023-06-14 09:00 Thanks for the replies. I never knew that the connection could be used like that. I will use the sqlconnection solution. I tried using the Quote SELECT * FROM sqlite_master WHERE type='table' Earlier and I couldn’t seem to get the putting ‘table’ into a string To use for a sqlquery right. For future reference does anyone know how to put the ‘table’ part into a proper string? ---------------------------------------------------------------------- Zvoni · 2023-06-14 09:14 Quote from: Joanna on June 14, 2023, 11:00:07 amThanks for the replies. I never knew that the connection could be used like that. I will use the sqlconnection solution. I tried using the Quote SELECT * FROM sqlite_master WHERE type='table' Earlier and I couldn’t seem to get the putting ‘table’ into a string To use for a sqlquery right. For future reference does anyone know how to put the ‘table’ part into a proper string? Untested Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   MyQuery.SQL.Text:='SELECT * FROM sqlite_master WHERE type=:Param';  MyQuery.ParamByName('Param').AsString:='table';  MyQuery.Open; There is one big difference between both approaches: SQLConnection.GetTableNames does exactly as advertised: It returns the tablenames (with a Flag if it's a SystemTable or not) Querying the sqlite_master table, you can also retrieve the SQL- Statement how to create it, or if it's a view, or an index EDIT2: the correct "name" is actually "sqlite_schema" "sqlite_master" is an alias for it https://www.sqlite.org/schematab.html ---------------------------------------------------------------------- eljo · 2023-06-14 09:50 Quote from: Joanna on June 14, 2023, 11:00:07 amI tried using the Quote SELECT * FROM sqlite_master WHERE type='table' Earlier and I couldn’t seem to get the putting ‘table’ into a string To use for a sqlquery right. For future reference does anyone know how to put the ‘table’ part into a proper string? Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i qryMain.sql.text := 'SELECT * FROM sqlite_master WHERE type=''table'''; ---------------------------------------------------------------------- Zvoni · 2023-06-14 10:17 Quote from: eljo on June 14, 2023, 11:50:45 amCode: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i qryMain.sql.text := 'SELECT * FROM sqlite_master WHERE type=''table''';  BLEH! That's exactly one of the reasons why we have Parameters (and should use them) ---------------------------------------------------------------------- eljo · 2023-06-14 10:42 Quote from: Zvoni on June 14, 2023, 12:17:20 pmQuote from: eljo on June 14, 2023, 11:50:45 amCode: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i qryMain.sql.text := 'SELECT * FROM sqlite_master WHERE type=''table''';  BLEH! That's exactly one of the reasons why we have Parameters (and should use them) you assume too much about the development environment in use, having said that, I agree parameters are fun to use. ---------------------------------------------------------------------- Zvoni · 2023-06-14 10:44 Quote from: eljo on June 14, 2023, 12:42:16 pmyou assume too much about the development environment in use, having said that, I agree parameters are fun to use. What's that got to do with Dev Env? Escaping Quotes by doubling them is that way in any language/IDE i've seen so far. ---------------------------------------------------------------------- eljo · 2023-06-14 11:23 Quote from: Zvoni on June 14, 2023, 12:44:44 pmQuote from: eljo on June 14, 2023, 12:42:16 pmyou assume too much about the development environment in use, having said that, I agree parameters are fun to use. What's that got to do with Dev Env? Escaping Quotes by doubling them is that way in any language/IDE i've seen so far. Agreed, parameters on the other hand are not as globally supported. ---------------------------------------------------------------------- Joanna · 2023-06-14 14:35 Quote Agreed, parameters on the other hand are not as globally supported. The answer I was seeking was the simple one. The one with params is a good example of how to use params should I ever need to. So far I haven’t been too about doing an injection attack on my own database.  I would like to know more about params not being globally supported? ---------------------------------------------------------------------- eljo · 2023-06-14 15:37 Quote from: Joanna on June 14, 2023, 04:35:38 pmQuote Agreed, parameters on the other hand are not as globally supported. The answer I was seeking was the simple one. The one with params is a good example of how to use params should I ever need to. So far I haven’t been too about doing an injection attack on my own database.  I would like to know more about params not being globally supported? Globally as in IDEs other than Lazarus and Delphi. ---------------------------------------------------------------------- Joanna · 2023-06-14 22:44 Oh that’s surprising. Well I have no plans to stop using Lazarus.  Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i .   MyQuery.SQL.Text:='SELECT * FROM sqlite_master WHERE type=:Param';  MyQuery.ParamByName('Param').AsString:='table';  MyQuery.Open; Also , when you want to use multiple params, do you have to do a MyQuery.ParamByName for every param or can all the params be declared together ? ---------------------------------------------------------------------- eljo · 2023-06-15 01:58 Quote from: Joanna on June 15, 2023, 12:44:26 amOh that’s surprising. Well I have no plans to stop using Lazarus.  Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i .   MyQuery.SQL.Text:='SELECT * FROM sqlite_master WHERE type=:Param';  MyQuery.ParamByName('Param').AsString:='table';  MyQuery.Open; Also , when you want to use multiple params, do you have to do a MyQuery.ParamByName for every param or can all the params be declared together ? As far as I know you need to address every param, either by using parambyname or its index, you can't address them all together in a single statement. ---------------------------------------------------------------------- SymbolicFrank · 2023-06-15 07:12 GetTableNames doesn't work if you have multiple schemas. Or rather, it does work, but you get a list of all tables in all schemas. Although I didn't test if it respects search_path. ---------------------------------------------------------------------- Joanna · 2023-06-15 13:46 Is that like if you have multiple databases open at same time? ---------------------------------------------------------------------- SymbolicFrank · 2023-06-15 13:55 Quote from: Joanna on June 15, 2023, 03:46:16 pmIs that like if you have multiple databases open at same time? Each database can contain multiple schemas, which each can contain multiple tables (even with the same name). It's very convenient in PostgreSQL, as it doesn't allow running queries across multiple databases. There's even an extension to map the tables from database1.schema1 to database2.schema2. ---------------------------------------------------------------------- Zvoni · 2023-06-15 14:11 Let's sort out some "Definitions": A "schema" is actually a Database (as known from MySQL etc.), which contains tables, views, Indexes, Triggers, Stored Procedures and whatever else Meaning: With the same Server you can have multiple "schemas" (Databases) e.g. One schema called "SalesAndPurchases", another called "Accounting". Both have their own tables etc. A User logs in to the one single server, and then per GRANTS it's decided if User A is allowed to take a peek at "Accounting", or if he gets "Err... no no...it's not your business how much the Boss earns" I know this for a fact, since i HAVE such a scenario (though for IBM DB2) in context of SQLite: SQLite is a single-file DBMS, meaning the usual case with SQLite is: 1 File, 1 Schema (=Database), which contains tables etc.. That schema is always called "main" BUT: What you can do in SQLite is "attach" another Database-File. Then you would have the scenario, which SF is alluding to: having 2 Schemas in SQLite (though i don't know, how that attached schema is called, since in its own file it's "main" again) ---------------------------------------------------------------------- Joanna · 2023-06-15 14:28 What distinguishes one schema from another? ---------------------------------------------------------------------- Zvoni · 2023-06-19 11:54 Quote from: Joanna on June 15, 2023, 04:28:56 pmWhat distinguishes one schema from another? Usually access rights (as i've written above), but this does not apply to SQLite, since SQLite doesn't support GRANTS/User rights. ---------------------------------------------------------------------- Joanna · 2023-06-20 23:12 Ok that makes sense, I have only used sqlite, so for me the schema is just the tables in one database. Thanks again for explaining.