Page 1 of 1

Sqlite3 function to retrieve total number of in ecah table fromthe same db sqlite3

Posted: Sun Jan 19, 2020 10:30 pm
by juanato
Hello from Spain.

I need to know in each table in the db sqlite3 the total of columns and create a table that replies the format of elements like the columns (INTEGER, TEXT and BOOLEAN).

In DBF enviroment, I use...

local aValFields := {}
aValFields := facmov->( AinitFields( ) ) // Carga CAMPOS perfil

// AinitFields()
function AinitFields()
local nI := 1
local aDbfStr := {}
local aValLocal := {}
aDbfStr := DbStruct()
For nI = 1 to Len( aDbfStr )
cType := aDbfStr[nI][2]
nLen := aDbfStr[nI][3]
Aadd( aValLocal, FieldInit( cType, nLen ) )
Next
return( aValLocal )


// FieldInit( cType, nLen )
function FieldInit( cType, nLen )
local xVar
Do Case
Case cType == 'A'
xVar := {}
Case cType == 'C'
xVar := Replicate( chr(255), nLen )
Case cType == 'D'
xVar := Date()
Case cType == 'L'
xVar := .f.
Case cType == 'M'
xVar := space(1)
Case cType == 'N'
xVar := 0
Case cType == 'U'
Alert('Un campo mal Definido !!!')
xVar := nil
EndCase
return xVar


// Ainit( aArray )
function Ainit( aArray )
local nI := 1
for nI = 1 to Len( aArray )
aArray[ nI ] := VarInit( aArray [ nI ] )
next
return aArray

// VarInit( xVar )
function VarInit( xVar )
Do Case
Case ValType(xVar) == 'A'
xVar := {}
Case ValType(xVar) == 'B'
xVar := { || Cls() }
Case ValType(xVar) == 'C'
xVar := Replicate( space(1), Len( xVar) )
Case ValType(xVar) == 'D'
xVar := Date()
Case ValType(xVar) == 'L'
xVar := .f.
Case ValType(xVar) == 'M'
xVar := space(4096)
Case ValType(xVar) == 'N'
xVar := 0
Case ValType(xVar) == 'O'
xVar := {}
Case ValType(xVar) == 'U'
xVar := nil
EndCase
return xVar


I need to eval sqlite3 number of columns and type of data to create an array for each table.

Please,give some help about.

Thanks.

Re: Sqlite3 function to retrieve total number of in ecah table fromthe same db sqlite3

Posted: Mon Jan 20, 2020 2:36 pm
by mustafa
Hola juanato
no se si te podrá servir mi Sample está basado en que si no sabemos la cantidad de Columnas
quiero que calculo el color del Grid en DYNAMICBACKCOLOR &(xbColor) DYNAMICFORECOLOR &(xfColor)
el calculo está efectuado con:

Code: Select all


#include "hmg.ch"
#include "sql1.prg"

PUBLIC aTable := {}
PUBLIC aCurRow := {}
PUBLIC dbo := nil
PUBLIC cDBName := "sample.db3"   //  la Base de Datos
PUBLIC aGrid  := {}
PUBLIC nCount  
PUBLIC qry , columns , y , animox  //<--- Importante

set century on
set date ital

 if .not. file(cDBName)
   create_populate()     <------- Crear Database SQLite
   Count_Columns()       <------ Antes que Define Window
 else
   connect2db(cDBName)
   Count_Columns()   
 endif   

Define Window sample at 0,0 width 800 height 630 main TITLE "Database SQlite" 
*------------------------------------------- Automatic  Color ------------------------------------------------* 
    nCount := animox     // Número Columnas 7 // Pero si no se sabemos ?  ------> Ver Function Count_Columns()   
    xnCount := nCount -1                                                              
    xbColor := "{ " + REPLICATE(  ALLTRIM("bColor,") , xnCount ) + ALLTRIM("bColor") +" }" 
    xfColor := "{ " + REPLICATE(  ALLTRIM("fColor,") , xnCount ) + ALLTRIM("fColor") +" }" 
*-------------------------------------------------------------------------------------------------------------* 

   DEFINE GRID Grid_1
   .....
   WIDTHS  { 080,258,100,110,070,0,0 }    // 0 <-  Porque asi no sale en Grid  0   <--- Because that's not in Grid 
   HEADERS {"Code", "Name", "Phone", "Date", "Married", "Notas", "Photo" } 
   .....
   .....
   DYNAMICBACKCOLOR &(xbColor)    // Automatic By Rutina                                    
   DYNAMICFORECOLOR &(xfColor)  

*-------------------------------------------*
FUNCTION Count_Columns()     // Para saber el número de Columnas
*-------------------------------------------*

 qry := sql(dbo,"select * from  personales")
 columns  := qry  
 
      For y := 1 to LEN(columns)   
          animox := ( LEN(columns[y]) )  
      Next               

Return Nil 

Haber si puede interesarte ?
Saludos
Mustafa

Re: Sqlite3 function to retrieve total number of in ecah table fromthe same db sqlite3

Posted: Mon Jan 20, 2020 9:32 pm
by juanato
Gracias, Mustafá...