problem with sqlite / solved and continue

[dragancesu]

A have little problem with this program, what is wrong?

Program read table structure from SQLite database

[gfilatov]

A have little problem with this program, what is wrong?

Program read table structure from SQLite database

Hello Dragan,

Please try the following working program:

Code: Select all

/*
 * This program is generated by HMGCASE
 * developed by Dragan Cizmarevic < dragancesu(at)gmail.com > 
 */

#include <hmg.ch>

PROCEDURE main // tab_imp

   PRIVATE aTable := {}, aCurRow := {}, dbo := nil
   PRIVATE cDataBase := "prenos"

   set procedure to Sql1 
   
   use _struct new
   zap
      
lres := Connect2db ( 'prenos.db3', .f. ) 
if lres == .f.
      msgstop( 'Not a valid SQLite file.' )
else
   _sql = "SELECT type, tbl_name, sql FROM sqlite_master "

   aTable := sql(dbo,_sql)
   for i := 1 to len(aTable)
      aCurRow := aTable[i]
      _tip   := aCurRow[1]
	  _tabela := aCurRow[2]
	  _desc   := aCurRow[3]
	  
	  dbappend()
	  replace tabtype with _tip
	  replace tabname with _tabela
	  replace tabsql with _desc
	  
   next i
endif

dbcloseall()

msginfo('Import finish')

RETURN

Hope that helps

[dragancesu]

Thank you

[dragancesu]

I will continue on project with SQLite database,
first step is input data, this program create program for import data from DBF into SQLite

Code: Select all

#Include "hmg.ch"

procedure main // Function import_gen ()

set navigation extended

read_dir()

use _files

	DEFINE WINDOW ImpGenForm ;
		AT 0,0 ;
		WIDTH 400 ;  
		HEIGHT 300 ;
		TITLE 'Generate Import program' ;
		MODAL
			
      ON KEY ESCAPE ACTION ImpGenForm.Release
   
		@ 010,010 COMBOBOX Combo_1 ;
			ITEMSOURCE _files->name ;
			VALUE 1 ;
			WIDTH 200 HEIGHT 100 ;
			FONT "Arial" SIZE 10 ; 
			TOOLTIP "Form" 

	    @ 150, 140 BUTTON IMPORT_111 ; 
		   CAPTION " Create program " ;
		   WIDTH 120 ;
		   ACTION imp_genn( ImpGenForm.Combo_1.Value ) 

	END WINDOW		 

	CENTER WINDOW   ImpGenForm
	ACTIVATE WINDOW ImpGenForm

Return
*:-------------------------------------------
FUNCTION imp_GENN ( _red )

dbcloseall()

use _files
go _red

_name = alltrim(name)

dbcloseaLL()

select 4
use _files
set filter to name = _name
dbgotop()

_xx = recno()

_nn = alltrim(str(_xx))
_nnn = 111

*_frm_prg = 'form_' + _nn + '.prg'
_imp_prg = 'imp' + _name + '.prg'
 
select 4
tek_red = 0

set device to printer
set printer to &_imp_prg

@ tek_red, 0 say '/*'
tek_red++
@ tek_red, 0 say ' * This program is generated by HMGCASE'
tek_red++
@ tek_red, 0 say ' * developed by Dragan Cizmarevic < dragancesu(at)gmail.com > '
tek_red++
@ tek_red, 0 say ' */'
tek_red++

tek_red++
@ tek_red, 0 say '#include <hmg.ch>'
tek_red++

tek_red++
@ tek_red, 0 say 'PROCEDURE MAIN '

tek_red++
tek_red++
@ tek_red, 0 say '   PRIVATE oServer := Nil, lLogin := .F., dbo := nil ' 
tek_red++
@ tek_red, 0 say '   PRIVATE oRow:= {}, oQuery:="", _firstRec:=0, _lastRec:=0, _currRec:=0 '
tek_red++
@ tek_red, 0 say '   PRIVATE cDataBase:="prenos.db3" ' 
tek_red++

/* 1: create SQLite database, in this case on local folder
   2: create database for transfer data (database prenos)
      in this case is named PRENOS something like TRANSFER in my language
   3: start this program
   
   select dbf from list and program create impDBFNAME.prg
   just compile: build impDbfName 
*/

tek_red++
@ tek_red, 0 say '   set navigation extended    // for test  '
tek_red++
@ tek_red, 0 say '   set date german             // for test  '
tek_red++
@ tek_red, 0 say '   set century on             // for test  '
tek_red++
tek_red++
@ tek_red, 0 say '   set procedure to Sql1'
tek_red++

tek_red++
@ tek_red, 0 say '   DEFINE WINDOW Imp_' + _nn + ' ;'
tek_red++
@ tek_red, 0 say '      WIDTH 400 ;'
tek_red++
@ tek_red, 0 say '      HEIGHT 300 ;'
tek_red++
@ tek_red, 0 say '      TITLE "Import dbf -> SQLite " ;'
tek_red++
@ tek_red, 0 say '      MAIN ; // for test, usually MODAL' 
tek_red++
@ tek_red, 0 say '      ON INIT imp_dbf_'+_nn+'()' 
tek_red++

tek_red++
@ tek_red, 0 say '      @ 50, 50 LABEL label1 VALUE "' + _name + '.dbf -> SQLite " WIDTH 200'
tek_red++
@ tek_red, 0 say '      @ 100, 50 LABEL label2 VALUE "0/0"'
tek_red++

tek_red++
@ tek_red, 0 say '      @ 150, 50 PROGRESSBAR progres_1 ;'
tek_red++
@ tek_red, 0 say '         RANGE 0,100 ;'
tek_red++
@ tek_red, 0 say '         WIDTH 300 HEIGHT 25 '
tek_red++

tek_red++
@ tek_red, 0 say '   END WINDOW'
tek_red++

tek_red++
@ tek_red, 0 say '   CENTER WINDOW Imp_' + _nn
tek_red++
@ tek_red, 0 say '   ACTIVATE WINDOW Imp_' + _nn
tek_red++

tek_red++
@ tek_red, 0 say 'RETURN'
tek_red++
@ tek_red, 0 say '*:---------------------------------------------*'
tek_red++
@ tek_red, 0 say 'FUNCTION imp_dbf_' + _nn + '()'
tek_red++

/* konekcija na bazu */

tek_red++
@ tek_red, 0 say '   Connect2db ( cDatabase, .t. )'
tek_red++

/* brisanje tabele ako postoji */

tek_red++
@ tek_red, 0 say '   cQuery := " DROP TABLE IF EXISTS ' + lower(_name) + ' "'
tek_red++
tek_red++
@ tek_red, 0 say '   if .not. miscsql(dbo,cQuery) '
tek_red++
@ tek_red, 0 say '      Return nil '
tek_red++
@ tek_red, 0 say '   Endif '
tek_red++

select 10
use &_name

tek_red++
@ tek_red,0 say '   cQuery := "CREATE TABLE ' + lower(alltrim(_name)) + ' ( "'
tek_red++

PRIVATE a_name[64], a_type[64], a_len[64], a_dec[64]

ii = AFIELDS(a_name)
AFIELDS(a_name,a_type,a_len,a_dec)
      
for i = 1 to ii

   _polje = a_name[i]
   _tip   = a_type[i]
   _duz   = alltrim(str(a_len[i]))
   _dec   = alltrim(str(a_dec[i]))

   _text = padr(_polje,15)

   do case 
      case _tip = 'N'
           _opis = 'DECIMAL (' + _duz + ',' + _dec + ')'
   
      case _tip = 'C'
           _opis = 'CHAR (' + _duz + ')'

      case _tip = 'D'
           _opis = 'DATE' 

      case _tip = 'L'
           _opis = 'TINYINT' 
               
      otherwise
           loop
               
   endcase

   @ tek_red,0 say '   cQuery += "' + _text + _opis + if(i<ii,',','') + ' "'
   tek_red++

next 

   @ tek_red,0 say '   cQuery += "); "'

tek_red++
tek_red++
@ tek_red, 0 say '   if .not. miscsql(dbo,cQuery) '
tek_red++
@ tek_red, 0 say '      Return nil '
tek_red++
@ tek_red, 0 say '   Endif '
tek_red++

tek_red++
@ tek_red, 0 say '   USE ' + _name
tek_red++
@ tek_red, 0 say '   x1 := 0'
tek_red++
@ tek_red, 0 say '   x2 := reccount()'
tek_red++

tek_red++
@ tek_red, 0 say '   DO WHILE .NOT. eof()'
tek_red++
tek_red++
@ tek_red, 0 say '      x1++'
tek_red++

for i = 1 to ii

   _polje = a_name[i]
   _tip   = a_type[i]
   _duz   = alltrim(str(a_len[i]))
   _dec   = alltrim(str(a_dec[i]))
   
   _text = padr(_polje,15)
   
   @ tek_red,0 say '      _' + _text + ' = ' + _text
   tek_red++
   
   if _tip = 'C'
           _opis = '      _' + _text + ' = strtran(_' + alltrim(_text) + ',"' + chr(39) + '","&#039")'
           @ tek_red,0 say _opis
           tek_red++
   endif

next 

_insert = 'INSERT INTO ' + _name + ' VALUES (' 
_fields = ''
_rbr = 0

*select 4
*dbgotop()
*do while .not. eof()

for i = 1 to ii

   _polje = a_name[i]
   _tip   = a_type[i]
   _duz   = alltrim(str(a_len[i]))
   _dec   = alltrim(str(a_dec[i]))

if !empty(_fields) .and.  _rbr < ii
   _fields = _fields + ', '
endif

if _tip = 'N'
    _p = chr(34) + ' + str(_' + alltrim(_polje) + ') + ' + chr(34)
endif

if _tip = 'C'
    _p = chr(39) + chr(34) + ' + alltrim(_' + alltrim(_polje) + ') + ' + chr(34) + chr(39)
endif

if _tip = 'D'
   _p = chr(34) + ' + dtomy(_'+ alltrim(_polje) +') + '+chr(34)
endif

_fields = _fields + _p

_rbr++
*dbskip()
*enddo

next

_linija := '   cQuery := ' + chr(34) + _insert + _fields + ')' + chr(34)

tek_red++
@ tek_red, 0 say _linija

tek_red++
@ tek_red, 0 say '   if .not. miscsql(dbo,cQuery) '
tek_red++
@ tek_red, 0 say '      Return nil '
tek_red++
@ tek_red, 0 say '      exit'
tek_red++
@ tek_red, 0 say '    endif'
tek_red++

tek_red++
@ tek_red, 0 say '   Imp_' + _nn + '.Progres_1.Value := x1/x2*100'
tek_red++
@ tek_red, 0 say '   Do Events'
tek_red++

tek_red++
@ tek_red, 0 say '   Imp_' + _nn + '.Label2.Value := alltrim(str(x1)) + ' + chr(39) + ' / ' + chr(39) + ' + alltrim(str(x2))'
tek_red++

tek_red++
@ tek_red, 0 say '   dbskip()'
tek_red++

tek_red++
@ tek_red, 0 say '   ENDDO'
tek_red++

tek_red++
@ tek_red, 0 say '   dbcloseall()'
tek_red++

tek_red++
@ tek_red, 0 say 'Imp_'+_nn+'.Release'
tek_red++

tek_red++
@ tek_red, 0 say 'RETURN 0'
tek_red++

set printer to 
set device to screen
setprc(0,0)

dbcloseall()

MsgInfo ( 'Generate ' + _imp_prg + ' !')

ImpGenForm.Release

RETURN
*!*********************************************************************
FUNCTION read_dir

LOCAL afiles, list_dbf, jj, act_sel, i, ii, e_name, w_ext, w_name

IF ! FILE ("_files.dbf")
   list_dbf := {}
   AADD(list_dbf,{"name","c",8,0})
   AADD(list_dbf,{"ext","c",3,0})
   DBCREATE("_files",list_dbf)
ENDIF

afiles = DIRECTORY()
jj = LEN(afiles)

IF jj = 0
   RETURN -1
ENDIF

act_sel = SELECT()

SELECT 0
USE _files
ZAP
dbgotop()

FOR i = 1 TO jj
   e_name = afiles[i][1]
   w_ext = ''
   
   ii = AT(".",e_name)
   IF ii = 0
      w_name = e_name+REPLICATE(' ',8-LEN(e_name))
      w_ext  = ''
   ELSE
      w_name = SUBSTR(e_name,1,ii-1)+REPLICATE(' ',9-ii)
      w_ext  = SUBSTR(e_name,ii+1,3)
   ENDIF

   if substr(w_name,1,1) = '_'
      loop
   endif
   
   if upper(w_ext) != 'DBF'
      loop
   endif
   
   dbappend()
   REPLACE name WITH upper(w_name)
   REPLACE ext  WITH w_ext
NEXT

dbcloseall()

RETURN 0

[mustafa]

Hola amigo Dragan:
Magnifico trabajo !!! , me gusta

Tu sample generó error en el tema de Fechas ---> dtomy

if _tip = 'D'
* _p = chr(34) + ' + dtomy(_'+ alltrim(_polje) +') + '+chr(34)
_p = chr(34) + ' + dtos(_'+ alltrim(_polje) +') + '+chr(34)
endif

Pero las fechas me salían al revés efectuado una pequeña modificación
fragmentado las fechas para que aparezca correcta

También Modifique cDataBase:="prenos.db3"
ahora sale con el nombre de la DBF

Veo que faltaría un pequeño Grid para ver las nuevas conversiones
pero no se como construir un visor que sirva para cualquier database SQlite

A ver si te gusta la modificación
Un Saludo
Mustafa

*--------------------------------- Google ------------------------------*
Hi, Dragan,
Great job !!! , I like

Your sample generated an error in the topic of Dates ---> dtomy

if _tip = 'D'
    * _p = chr (34) + '+ dtomy (_' + alltrim (_polje) + ') +' + chr (34)
            _p = chr (34) + '+ dtos (_' + alltrim (_polje) + ') +' + chr (34)
endif

But the dates came back to me, made a small modification
fragmented the dates so that it appears correct

Also Modify cDataBase: = "prenos.db3"
now it comes out with the name of the DBF

I see that a small Grid would be missing to see the new conversions
but I do not know how to build a viewer that works for any SQlite database

Let's see if you like the modification
A greeting
Mustafa

[dragancesu]

Thank you for error report, this is change program for dbf -> mysql

Idea is import all dbf to some base, "prenos" is "transfer" in transalate, and create production database and import from prenos,
dbf and sql table is different, and must carefuly create table, index and views, than create aplication

[mustafa]

+10
Thank you

[dragancesu]

Please, be patient, now I'm doing tools for working with a SQLite database, the first one has to be like a Quick Browse Form