DatabaseColumnType() Reporting Wrong Type in SQLite

Post bugreports for the Linux version here
swhite
Enthusiast
Enthusiast
Posts: 654
Joined: Thu May 21, 2009 6:56 pm

DatabaseColumnType() Reporting Wrong Type in SQLite

Post by swhite »

Hi

The following code reports the wrong column type in the SQLite Database. The DatabaseColumnType() always returns 0 (#PB_Database_StaticCursor). I am using PB 5.73 64bit.

Code: Select all

UseSQLiteDatabase()

If CreateFile(0,"myTest.sqlite")
   CloseFile(0)
EndIf      
If OpenDatabase(0,"myTest.sqlite","","",#PB_Database_SQLite)
   If DatabaseUpdate(0,"create table if not exists mytable (field1 int not null,field2 real not null,test3 text not null);")
      If DatabaseQuery(0,"pragma table_info('mytable')")
         While NextDatabaseRow(0)
            For ln = 0 To DatabaseColumns(0) - 1
               Select DatabaseColumnType(0,ln)
                  Case #PB_Database_String
                     Debug DatabaseColumnName(0,ln)+"  String"
                  Case #PB_Database_Long
                     Debug DatabaseColumnName(0,ln)+"  Long"
                  Case #PB_Database_Double
                     Debug DatabaseColumnName(0,ln)+"  Double"
                  Case #PB_Database_Quad
                     Debug DatabaseColumnName(0,ln)+"  Quad"
                  Case #PB_Database_Float
                     Debug DatabaseColumnName(0,ln)+"  Float"
                  Default
                     Debug DatabaseColumnName(0,ln)+"  "+Str(DatabaseColumnType(0,ln))
               EndSelect
            Next
         Wend
      EndIf
   EndIf
   FinishDatabaseQuery(0)
   CloseDatabase(0)
EndIf
Simon White
dCipher Computing
swhite
Enthusiast
Enthusiast
Posts: 654
Joined: Thu May 21, 2009 6:56 pm

Re: DatabaseColumnType() Reporting Wrong Type in SQLite

Post by swhite »

Hi

I just tested this in Windows 10 Pro and I have the same problem.

Simon
Simon White
dCipher Computing
User avatar
mk-soft
Addict
Addict
Posts: 3294
Joined: Fri May 12, 2006 6:51 pm
Location: Germany

Re: DatabaseColumnType() Reporting Wrong Type in SQLite

Post by mk-soft »

The upgrading of pragma table_info is not correct.

Code: Select all

UseSQLiteDatabase()

If CreateFile(0,"myTest.sqlite")
  CloseFile(0)
EndIf      
If OpenDatabase(0,"myTest.sqlite","","",#PB_Database_SQLite)
  If DatabaseUpdate(0,"create table if not exists mytable (field1 int not null,field2 real not null,test3 text not null);")
    If DatabaseQuery(0,"pragma table_info('mytable')")
      For ln = 0 To DatabaseColumns(0) - 1
        r1.s + DatabaseColumnName(0,ln)+";"
      Next
      Debug r1
      r1 = ""
      While NextDatabaseRow(0)
        For ln = 0 To DatabaseColumns(0) - 1
          r1.s + GetDatabaseString(0,ln)+";"
        Next
        Debug r1
        r1 = ""
      Wend
    EndIf
  EndIf
  FinishDatabaseQuery(0)
  CloseDatabase(0)
EndIf
My Projects ThreadToGUI / OOP-BaseClass / OOP-BaseClassDispatch / EventDesigner V3
PB v3.30 / v5.70 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace
collectordave
Addict
Addict
Posts: 1275
Joined: Fri Aug 28, 2015 6:10 pm
Location: Portugal

Re: DatabaseColumnType() Reporting Wrong Type in SQLite

Post by collectordave »

The DatabaseColumnType(0,ln) and DatabaseColumnName(0,ln) return the column type on a successful query of the actual table.

So DatabaseQuery(0,"PRAGMA table_info(mytable);") is returning the info on the pragma table.

To show the field names and types from the pragma you can use:-

Code: Select all

UseSQLiteDatabase()

If CreateFile(0,"myTest1.db")
   CloseFile(0)
EndIf      
If OpenDatabase(0,"myTest.sqlite","","",#PB_Database_SQLite)
   If DatabaseUpdate(0,"create table if not exists mytable (field1 INTEGER not null,field2 real not null,test3 TEXT not null);")
      If DatabaseQuery(0,"PRAGMA table_info(mytable);")

        While NextDatabaseRow(0)

             Debug GetDatabaseString(0,DatabaseColumnIndex(0,"Name"))
             Debug GetDatabaseString(0,DatabaseColumnIndex(0,"Type"))
             
         Wend
      EndIf
   EndIf
   FinishDatabaseQuery(0)
   CloseDatabase(0)
EndIf
To use the column type etc you can use something like this:

Code: Select all

UseSQLiteDatabase()

If CreateFile(0,"myTest.sqlite")
   CloseFile(0)
EndIf      
If OpenDatabase(0,"myTest.sqlite","","",#PB_Database_SQLite)
   If DatabaseUpdate(0,"create table if not exists mytable (field1 INTEGER not null,field2 real not null,test3 TEXT not null);")
     
     DatabaseUpdate(0,"INSERT INTO mytable (field1,field2,test3) VALUES (4,34.2,'Just a bit of text')")

     If DatabaseQuery(0,"SELECT * FROM mytable")  

       FirstDatabaseRow(0)

           For ln = 0 To DatabaseColumns(0) - 1

               Select DatabaseColumnType(0,ln)
                  Case #PB_Database_String
                     Debug DatabaseColumnName(0,ln)+"  String"
                  Case #PB_Database_Long
                     Debug DatabaseColumnName(0,ln)+"  Long"
                  Case #PB_Database_Double
                     Debug DatabaseColumnName(0,ln)+"  Double"
                  Case #PB_Database_Quad
                     Debug DatabaseColumnName(0,ln)+"  Quad"
                  Case #PB_Database_Float
                     Debug DatabaseColumnName(0,ln)+"  Float"
                  Default
                    Debug DatabaseColumnName(0,ln)+"  "+Str(DatabaseColumnType(0,ln))
               EndSelect
             Next
             
      EndIf
   EndIf
   FinishDatabaseQuery(0)
   CloseDatabase(0)
EndIf
Notice INSERT statement so that a least one row can be returned.

Hope this helps.
Any intelligent fool can make things bigger and more complex. It takes a touch of genius — and a lot of courage to move in the opposite direction.
swhite
Enthusiast
Enthusiast
Posts: 654
Joined: Thu May 21, 2009 6:56 pm

Re: DatabaseColumnType() Reporting Wrong Type in SQLite

Post by swhite »

Hi

I must be missing something because I am not looking for the column type for mytable. I just want to load the results of the pragma which returns one row for each column in mytable. So why would DatabaseColumnType() not return the correct type of "string"?

Thanks,
Simon
Simon White
dCipher Computing
collectordave
Addict
Addict
Posts: 1275
Joined: Fri Aug 28, 2015 6:10 pm
Location: Portugal

Re: DatabaseColumnType() Reporting Wrong Type in SQLite

Post by collectordave »

Hi

If you check the first code I posted it does exactly as you ask. It returns one row for each column in mytable.

The line

If DatabaseQuery(0,"PRAGMA table_info(mytable);")

Returns rows of data in the same way as a normal SELECT Query it could be seen as:-

SELECT * FROM table_info WHERE Name = 'mytable'. ;Note this will not work just for illustration. Pragma tables are hidden.

These rows are accessed in exactly the same way as any other Select query using GetDatabaseString etc

Now DatabaseColumnName() is a PB function that works on the results returned from a select query. So using this after the pragma query it returns the referenced column name of the pragma table not mytable.

If you check the second code I use a select query on mytable to return one row of data from mytable then the DatabaseColumnName() returns the referenced column name of the results of the query on mytable.

Hope this helps-
Any intelligent fool can make things bigger and more complex. It takes a touch of genius — and a lot of courage to move in the opposite direction.
swhite
Enthusiast
Enthusiast
Posts: 654
Joined: Thu May 21, 2009 6:56 pm

Re: DatabaseColumnType() Reporting Wrong Type in SQLite

Post by swhite »

Hi

Yes I understood your first code but you used GetDatabasestring(). I am trying to build a generic solution where I do not know the column type so I wanted to use DatabaseColumnType() to determine how the column value should be returned. So the DatabaseQuery() might be a regular SQL statement or a pragama etc. yet still return the data correctly.

Thanks,
Simon.

collectordave wrote: Sun Jul 25, 2021 3:32 am Hi

If you check the first code I posted it does exactly as you ask. It returns one row for each column in mytable.

The line

If DatabaseQuery(0,"PRAGMA table_info(mytable);")

Returns rows of data in the same way as a normal SELECT Query it could be seen as:-

SELECT * FROM table_info WHERE Name = 'mytable'. ;Note this will not work just for illustration. Pragma tables are hidden.

These rows are accessed in exactly the same way as any other Select query using GetDatabaseString etc

Now DatabaseColumnName() is a PB function that works on the results returned from a select query. So using this after the pragma query it returns the referenced column name of the pragma table not mytable.

If you check the second code I use a select query on mytable to return one row of data from mytable then the DatabaseColumnName() returns the referenced column name of the results of the query on mytable.

Hope this helps-
Simon White
dCipher Computing
collectordave
Addict
Addict
Posts: 1275
Joined: Fri Aug 28, 2015 6:10 pm
Location: Portugal

Re: DatabaseColumnType() Reporting Wrong Type in SQLite

Post by collectordave »

Hi,

The clue is that when using Pragma a resultset is returned with the fields:

cid name type not null dfltvalue pk


For mytable this would give:-

cid name type not null dfltvalue pk
0 field1 INTEGER 0 0 0
1 field2 REAL 0 0 0
2 test3 TEXT 0 0 0


So using DatabaseColumnName() on this resultset would give the following:

cid
name
type
not null
dfltvalue
pk

DatabaseColumnType() could return anything.
Which is correct.

Reading the data in the resultset is the same as reading any query I.e. getdatabasestring etc.

If building a generic procedure to read an unknown database it is best to start with a list of table names which can be filled by accessing sqlite_master or another pragma.

Then have another list to list the table name and all its fields as above.

Your application then has a complete list of all tables and their fields to work with.

Remember DatabaseColumnType() is not returning any data only the columntype of the referenced column.

You can use DatabaseColumnName() etc on a query against any table as in my second example.

The drawback of this method is the query must return at least one row of data to work.

However you can check the returned resultset from a query using FirstdatabaseRow() then use databasecolumntype() etc. to get the column type then read the data from the resultset.

A generic procedure could start:-

DatabaseQuery(MyDB,Criteria)

if FirstdatabaseRow(MyDB)

Foreach column

SELECT DatabaseColumnType(column)

Case "TEXT"

Myvar.s = Getdatabasestring()

Endselect

Next Column

endif

My second code is a good place to start.


I have posted some code to read the schema of an sqlite database on the forum somewhere if you cannot find it let me know and I will attempt to resurrect it.
Any intelligent fool can make things bigger and more complex. It takes a touch of genius — and a lot of courage to move in the opposite direction.
swhite
Enthusiast
Enthusiast
Posts: 654
Joined: Thu May 21, 2009 6:56 pm

Re: DatabaseColumnType() Reporting Wrong Type in SQLite

Post by swhite »

Hi

I think my confusion is due to the fact that SQLite seems to only returns column types if there is a schema for the table, otherwise the column gets treated as text. I was assuming based on other databases that it would always return a column type.

Thanks,
Simon
Simon White
dCipher Computing
collectordave
Addict
Addict
Posts: 1275
Joined: Fri Aug 28, 2015 6:10 pm
Location: Portugal

Re: DatabaseColumnType() Reporting Wrong Type in SQLite

Post by collectordave »

Hi

I think the confusion is between what is data and what is a schema.

Some database engines do have a procedure to return the column type which, I believe Sqlite does not.

You can write your own though:-

Code: Select all

UseSQLiteDatabase()

If CreateFile(0,"myTest1.db")
  CloseFile(0)
EndIf 
 
 
 
Procedure.s GetDBColType(TableName.s,Column.i)
  
  Define colCount.i = 0
  Define ColumnType.s
  
  If DatabaseQuery(0,"PRAGMA table_info(" + TableName + ");")
    While NextDatabaseRow(0)
      colCount = colCount + 1
      If colcount = Column
        ColumnType = GetDatabaseString(0,DatabaseColumnIndex(0,"Type"))
        FinishDatabaseQuery(0)
        ProcedureReturn ColumnType  
      EndIf
           
    Wend

  EndIf

  ;If you get here then there is no column match
  FinishDatabaseQuery(0)
  ProcedureReturn "No Such Column"

       
 EndProcedure

If OpenDatabase(0,"myTest.sqlite","","",#PB_Database_SQLite)
   If DatabaseUpdate(0,"create table if not exists mytable (field1 INTEGER not null,field2 real not null,test3 TEXT not null);")

     Debug GetDBColType("mytable",1)
     Debug GetDBColType("mytable",2)   
     Debug GetDBColType("mytable",3)
     Debug GetDBColType("mytable",4)  
   
   EndIf
   
   CloseDatabase(0)
EndIf
Any intelligent fool can make things bigger and more complex. It takes a touch of genius — and a lot of courage to move in the opposite direction.
collectordave
Addict
Addict
Posts: 1275
Joined: Fri Aug 28, 2015 6:10 pm
Location: Portugal

Re: DatabaseColumnType() Reporting Wrong Type in SQLite

Post by collectordave »

There is one more approach you can use. You can select the typeof column values as below:-

Code: Select all

SELECT typeof(ColumnName) FROM TableName;
However this returns the type of the data stored in the column not the defined column type.

Remember Sqlite is dynamically typed not like other databases which are rigidly typed.
Any intelligent fool can make things bigger and more complex. It takes a touch of genius — and a lot of courage to move in the opposite direction.
collectordave
Addict
Addict
Posts: 1275
Joined: Fri Aug 28, 2015 6:10 pm
Location: Portugal

Re: DatabaseColumnType() Reporting Wrong Type in SQLite

Post by collectordave »

Just thought maybe I should have made an attempt to explain dynamic typing.

The column type in Sqlite is really just a label not a type it helps keep compatibility with SQL statements and does not describe the type of the column.

You can use just about anything as a column type. The programmer can use it to keep track of what is stored.

To illustrate this I have modified my earlier example procedure by adding a field with a type of 'rubbish'.

So the procedure instead of reporting 'no such table' returns 'rubbish as its type.

Code: Select all

UseSQLiteDatabase()

If CreateFile(0,"myTest1.db")
  CloseFile(0)
EndIf 

Procedure.s GetDBColType(TableName.s,Column.i)
  
  Define colCount.i = 0
  Define ColumnType.s
  
  If DatabaseQuery(0,"PRAGMA table_info(" + TableName + ");")
    While NextDatabaseRow(0)
      colCount = colCount + 1
      If colcount = Column
        ColumnType = GetDatabaseString(0,DatabaseColumnIndex(0,"Type"))
        FinishDatabaseQuery(0)
        ProcedureReturn ColumnType  
      EndIf
           
    Wend

  EndIf

  ;If you get here then there is no column match
  FinishDatabaseQuery(0)
  ProcedureReturn "No Such Column"

       
 EndProcedure

If OpenDatabase(0,"myTest1.db","","",#PB_Database_SQLite)
   If DatabaseUpdate(0,"create table if not exists mytable (field1 INTEGER not null,field2 real not null,test3 TEXT not null,Test4 Rubbish);")

     Debug GetDBColType("mytable",1)
     Debug GetDBColType("mytable",2)   
     Debug GetDBColType("mytable",3)
     Debug GetDBColType("mytable",4)  
     
   Else
     
     Debug DatabaseError()
     
     
   EndIf
   
   CloseDatabase(0)
EndIf

So in my code I can interpret 'rubbish' as a type as anything I want. The type is not rigid.
Any intelligent fool can make things bigger and more complex. It takes a touch of genius — and a lot of courage to move in the opposite direction.
swhite
Enthusiast
Enthusiast
Posts: 654
Joined: Thu May 21, 2009 6:56 pm

Re: DatabaseColumnType() Reporting Wrong Type in SQLite

Post by swhite »

Thank-you for all your assistance.

Simon
Simon White
dCipher Computing
Post Reply