problem with sqlite / solved and continue

General Help regarding HMG, Compilation, Linking, Samples

Moderator: Rathinagiri

Post Reply
User avatar
dragancesu
Posts: 638
Joined: Mon Jun 24, 2013 11:53 am
DBs Used: DBF, MySQL, Oracle
Location: Subotica, Serbia
Has thanked: 19 times
Been thanked: 125 times

problem with sqlite / solved and continue

Post by dragancesu » Tue Apr 16, 2019 7:40 am

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

Program read table structure from SQLite database
Attachments
sqlproblem.zip
(11.83 KiB) Downloaded 19 times
Last edited by dragancesu on Wed Apr 17, 2019 11:03 am, edited 1 time in total.

User avatar
gfilatov
Posts: 618
Joined: Fri Aug 01, 2008 5:42 am
Location: Ukraine
Has thanked: 22 times
Been thanked: 176 times
Contact:

Post by gfilatov » Tue Apr 16, 2019 8:37 am

dragancesu wrote:
Tue Apr 16, 2019 7:40 am
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: :arrow:

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 :idea:
Kind Regards,
Grigory Filatov

"Everything should be made as simple as possible, but no simpler." Albert Einstein

User avatar
dragancesu
Posts: 638
Joined: Mon Jun 24, 2013 11:53 am
DBs Used: DBF, MySQL, Oracle
Location: Subotica, Serbia
Has thanked: 19 times
Been thanked: 125 times

Post by dragancesu » Tue Apr 16, 2019 10:15 am

Thank you

User avatar
dragancesu
Posts: 638
Joined: Mon Jun 24, 2013 11:53 am
DBs Used: DBF, MySQL, Oracle
Location: Subotica, Serbia
Has thanked: 19 times
Been thanked: 125 times

Post by dragancesu » Wed Apr 17, 2019 11:03 am

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

User avatar
mustafa
Posts: 790
Joined: Fri Mar 20, 2009 11:38 am
DBs Used: DBF
Location: Alicante - Spain
Been thanked: 109 times
Contact:

Post by mustafa » Wed Apr 17, 2019 7:09 pm

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

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 :idea:

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
Attachments
SQlite_Converter.zip
(11.26 KiB) Downloaded 11 times
Last edited by mustafa on Thu Apr 18, 2019 10:57 am, edited 1 time in total.

User avatar
dragancesu
Posts: 638
Joined: Mon Jun 24, 2013 11:53 am
DBs Used: DBF, MySQL, Oracle
Location: Subotica, Serbia
Has thanked: 19 times
Been thanked: 125 times

Post by dragancesu » Wed Apr 17, 2019 7:43 pm

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

User avatar
mustafa
Posts: 790
Joined: Fri Mar 20, 2009 11:38 am
DBs Used: DBF
Location: Alicante - Spain
Been thanked: 109 times
Contact:

Post by mustafa » Thu Apr 18, 2019 10:55 am

+10
Thank you

User avatar
dragancesu
Posts: 638
Joined: Mon Jun 24, 2013 11:53 am
DBs Used: DBF, MySQL, Oracle
Location: Subotica, Serbia
Has thanked: 19 times
Been thanked: 125 times

Post by dragancesu » Thu Apr 18, 2019 12:47 pm

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

Post Reply