A have little problem with this program, what is wrong?
Program read table structure from SQLite database
problem with sqlite / solved and continue
Moderator: Rathinagiri
- dragancesu
- Posts: 921
- Joined: Mon Jun 24, 2013 11:53 am
- DBs Used: DBF, MySQL, Oracle
- Location: Subotica, Serbia
problem with sqlite / solved and continue
- Attachments
-
- sqlproblem.zip
- (11.83 KiB) Downloaded 174 times
Last edited by dragancesu on Wed Apr 17, 2019 11:03 am, edited 1 time in total.
Re: problem with sqlite
Hello Dragan,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
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
Kind Regards,
Grigory Filatov
"Everything should be made as simple as possible, but no simpler." Albert Einstein
Grigory Filatov
"Everything should be made as simple as possible, but no simpler." Albert Einstein
- dragancesu
- Posts: 921
- Joined: Mon Jun 24, 2013 11:53 am
- DBs Used: DBF, MySQL, Oracle
- Location: Subotica, Serbia
Re: problem with sqlite
Thank you
- dragancesu
- Posts: 921
- Joined: Mon Jun 24, 2013 11:53 am
- DBs Used: DBF, MySQL, Oracle
- Location: Subotica, Serbia
Re: problem with sqlite
I will continue on project with SQLite database,
first step is input data, this program create program for import data from DBF into SQLite
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) + '","'")'
@ 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
- Posts: 1158
- Joined: Fri Mar 20, 2009 11:38 am
- DBs Used: DBF
- Location: Alicante - Spain
- Contact:
Re: problem with sqlite / solved and continue
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
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
- Attachments
-
- SQlite_Converter.zip
- (11.26 KiB) Downloaded 177 times
Last edited by mustafa on Thu Apr 18, 2019 10:57 am, edited 1 time in total.
- dragancesu
- Posts: 921
- Joined: Mon Jun 24, 2013 11:53 am
- DBs Used: DBF, MySQL, Oracle
- Location: Subotica, Serbia
Re: problem with sqlite / solved and continue
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
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
- Posts: 1158
- Joined: Fri Mar 20, 2009 11:38 am
- DBs Used: DBF
- Location: Alicante - Spain
- Contact:
Re: problem with sqlite / solved and continue
+10
Thank you
Thank you
- dragancesu
- Posts: 921
- Joined: Mon Jun 24, 2013 11:53 am
- DBs Used: DBF, MySQL, Oracle
- Location: Subotica, Serbia
Re: problem with sqlite / solved and continue
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