Another SQLite question

[ricardo]

Hi,

Im creating some tables, and want to get the headers name no matter if the table is empty now.

Code: Select all

If DBHandle
    SQLiteGetTable("Select Name FROM sqlite_master WHERE type='table' ORDER BY Name") 
    Rows = SQLiteRows()
    Dim Tablas.s(Rows)
    For Row.l = 1 To Rows
      Debug SQLiteData(Row,0) 
      Tablas(Row) = SQLiteData(Row,0)
    Next
    
    For i = 1 To Rows
      Debug "Busca columnas en: " + Tablas(i)
      TableString.s = "SELECT * FROM " + Tablas(i)
      Result = SQLiteGetTable(TableString)
      Debug "Numero de columnas: " + Str(SQLiteCols())
      For Col = 0 To SQLiteCols()-1
        Debug "Columna #" + Str(Col) + " : " + SQLiteData(0, Col)
      Next Col
      
    Next i

But i can only get the headers if the table has some data.
Only if i previously INSERT some data, otherwise i get o Colums.

Is there a way to get the headers no matter if the table is empty?

One more question:

How can i add COLs later to an existing table?

One more:

Is there a limit to the data that could be stored on each cell in the database? Some limitation like the 64k of a string?

Thanks in advance for any help

[Karbon]

You could select from the sqlite_master table (it's the system table that holds schema info on your tables).. The only thing is that it returns the INSERT statement for the tables, not just a list of the column names. Easy enough to parse I guess but a bit of a pain..

Code: Select all

SELECT sql FROM
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE tbl_name LIKE 'yourtable' AND type!='meta'
ORDER BY type DESC, name

... something like that should work.

[Saboteur]

Is not possible add Cols to a table, you have to create one new. One trick is copy the data to a temporal table, create the new table and copy again the data to the new table.

The limit of each cell, I think it is in the docs, 1 Mb (I think), but it is possible to compile the sources with a bigger size.

Take a look at SQLite docs

[Karbon]

Hey ricardo..

Helpful Link:
http://www.sqlite.org/faq.html

To answer your question directly about size of a single row - it's 1 megabyte with the default compilation. You can change that to as much as 16 megabytes but you have to recompile.

And as to adding a new field to an existing table :

(From the FAQ too)

Code: Select all

    For example, suppose you have a table named "t1" with columns names "a", "b", and "c" and that you want to delete column "c" from this table. The following steps illustrate how this could be done:

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;

Good luck!

[ricardo]

Thanks all for you help, its working now