start with sqlite

General Help regarding HMG, Compilation, Linking, Samples

Moderator: Rathinagiri

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

Re: start with sqlite

Post by dragancesu » Wed Jun 19, 2019 9:34 am

1. https://www.sqlite.org/docs.html

2. c:\hmg3.4.4\SAMPLES\HFCL\SQL\SQLITE\sql1.prg - funtions, demp.prg - sample program, that's all what you need

3. connect2db () - connect to database
sql () returns the result, usually select
mscsql () when you do not need to result, delete, insert, update

4. SQLite on paper sounds nice, practice is a little different

supports all sql commands (+)
stores the data in the database as a string, except the primary key field (-)
since the data is stored as a string, when the account has to be converted, and there are incorrect results (-)
two tables in the database can be connected (+)
I did not manage to link tables from two bases (-)

My opinion is that this can only be used for storing data (like phonebook program), but not for more serious processing

I looked SQLITE and disappointed myself, it is better to use MySQL

it's my experience

User avatar
serge_girard
Posts: 2361
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Has thanked: 617 times
Been thanked: 124 times
Contact:

Post by serge_girard » Wed Jun 19, 2019 11:20 am

Indeed: better to use MySQL!

Serge

User avatar
SALINETAS24
Posts: 350
Joined: Tue Feb 27, 2018 3:06 am
DBs Used: DBF
Has thanked: 23 times
Been thanked: 24 times

Post by SALINETAS24 » Wed Jun 19, 2019 5:42 pm

Muchas gracias Dragan y Serge...,
cambio pues mi orientación de SQLITE a MySQL

¿hay algún manual para MYSQL.., y ejemplos...? :lol:

lo dicho.., empezamos de nuevo .. mejor con una cervecita...

User avatar
SALINETAS24
Posts: 350
Joined: Tue Feb 27, 2018 3:06 am
DBs Used: DBF
Has thanked: 23 times
Been thanked: 24 times

Post by SALINETAS24 » Wed Jun 19, 2019 6:52 pm

Una duda, estoy revisando los ejemplos de
\SAMPLES\HFCL\SQL\MYSQL...
He compilado sin problemas pero cuando ejecuto me dice que no puede conectar.
No se si es que tengo que hacer algo antes.., pero en principio tengo estos datos

HOSTNAMEIP : LocalHost
USER: root
PASWORD: <nada>

me dice "error de conexion\tmhyswls3erver no connection to server"

Supongo que tendré que hacer algo antes...

Alguna sugerencia .. gracias.

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

Post by dragancesu » Wed Jun 19, 2019 8:16 pm

I do not know how many know MySQL,
I recommend to start the portable version, like https://www.uniformserver.com/
(new version include module MariaDB)

It is portable Web server but has more or can only be started MySQL which is enough and make a basic database administration and users

For MySQL to recommend database manager https://www.heidisql.com/
It's can be install or use portable

Hint for first use, start database, create user, then create database

and copy libmysql.dll in folder with program, it found in ...\mysql\bin.. it's part of mysql server

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

Post by dragancesu » Thu Jun 20, 2019 7:26 am


User avatar
jairpinho
Posts: 345
Joined: Mon Jul 18, 2011 5:36 pm
Location: Rio Grande do Sul - Brasil
Has thanked: 3 times
Been thanked: 11 times
Contact:

Post by jairpinho » Fri Jun 21, 2019 3:05 am

SALINETAS24 wrote:
Wed Jun 19, 2019 8:20 am
Una pregunta.. hay algún manual de SQLITE ...?

Veo que se usan funciones que no termino de relacionar.

CONNECT2DB que se utiliza para abrir/conectar la Table
MISCSQL que es para hacer consultas. Utiliza dos parametros el primero es objeto dbo y la siguiente es la consulta.
y también aparece
SQL, que es igual que MISCSQL ????? en que se diferencian...

¿hay algún documento con información de las funciones que SQLITE tiene a disposición del programador HMG..?

Una de las páginas que he estado viendo es esta...
http://www.w3big.com/es/sqlite/default.html
Imagino que las funciones y comandos que se indican en la página son validos pero... ¿como se llamarian en HMG..?,
¿se usaría la función MISCSQL..?


y por último, e importante para el CORRECTO diseño de una DBF.

SQLITE diferencia entre LA CREACIÓN DE UNA BASE DE DATOS

Code: Select all

 Sqlite3 DatabaseName.db  
y la CREACION DE UNA TABLA

Code: Select all

CREATE TABLE database_name.table_name(
   column1 datatype  PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);
¿se puede crear una BASE DE DATOS utilizando el comando MISCSQL ..?
¿se podrían generar relaciones entre los CAMPOS de las diferentes TABLAS ? .

ESTO ULTIMO IGUAL NO ME EXPLICO BIEN, PERO CREO ENTENDER CON CUANDO SE DISEÑAN CON LA HERRAMIENTA QUE TRAEN ESTAS GESTORAS DE DATOS, SE PUEDE HACER QUE UN CAMPO DE UN TABLA ESTE RELACIONADO CON EL VALOR DE CAMPO DE OTRA TABLA.
UN EJEMPLO.. TENEMOS UNA TABLA DE CLIENTES CON EL CODIGO, NOMBRE, DIRECCION, ETC., , Y OTRA TABLA DE FACTURA, CON EL NUMERO DE FACTURA, Nº CLIENTE, FECHA, ETC.., PUES LO QUE HE VISTO QUE HACEN EN EL DISEÑO ES QUE RELACIÓNAN EL CAMPO NºCLIENTE DE LA TABLA DE FACTURAS CON EL CAMPO CÓDIGO DE LA TABLA DE CLIENTES, IMAGINO QUE ASÍ CUANDO CAMBIO DE FACTURA AUTMATICAMENTE ME BUSCA EL CLIENTE.......

Pero todo ello desde la linea de programación.

Muchas gracias por vuestro interés, vamos con una cervecita.
Hola, he empezado a migrar all my dbf projects a mysql donde se utiliza en multiusuarios en una red o incluso en el alojamiento de mysql, y ahora con la opción sqlite opción la base de datos en el archivo físico con el archivo de texto con old dbf, I can del everything I do with el mysql in sqlite I utiliza todos los códigos justo por cambiar la ruta de la conexión y la búsqueda de fechas de dos líneas, tengo sqlite projects con las bases de datos relacionadas con dos o más bancos y también puedo de la relación de tablas del mismo banco bank muchos diferentes. dependiendo de la casilla que debe crear funciones auxiliares para los hmg que cumplan varios proyectos diferentes.

cómo puedo utilizar mis aplicaciones hoy en mi sistema
Mysql para multiusuarios conectados a través de la red local
SqLite es una ubicación de usuario local

He aquí una documentación que he encontrado durante la migración a mysql.
Howto para Mysql do Projeto Harbour.zip
(1.7 MiB) Downloaded 53 times

english:
Hello, I started migrating all my dbf projects to mysql where I use it in multiusers on a network or even in mysql hosting, and now with the sqlite option as a local database in physical file with old dbf, I can do everything with the mysql in sqlite I use all the codes just by changing the way of connection and search of data generally two lines, I have sqlite projects with databases related to two or more banks and also I can do the relationship of tables of the same bank or bank many different. depending on the case we should create auxiliary functions for hmg that meets several different projects.

how do i use my applications today in my systems
Mysql for multiusers connected via local and remote connection network
SqLite for a single user local connection

Here is some documentation I found during dbf migration to mysql.
Howto para Mysql do Projeto Harbour.zip
(1.7 MiB) Downloaded 53 times
Jair Pinho
HMG ALTA REVOLUÇÃO xBASE
HMG xBASE REVOLUTION HIGH
http://www.hmgforum.com.br

User avatar
tonton2
Posts: 297
Joined: Sat Jun 29, 2013 1:26 pm
Location: Algerie
Has thanked: 8 times
Been thanked: 11 times
Contact:

Post by tonton2 » Thu Nov 14, 2019 9:33 am

mustafa wrote:
Wed May 15, 2019 4:53 pm
Hello, Dragan friend
Congratulations for the contribution

I understood, that there were problems with the field "L" -> Boolean
Note: for now it does not work with boolean / logical fields[
I have not been able to look at all the contents of the Sample CUST.prg

But I modified the Boolean field theme a bit, which in our case would be "MARRIED"
Look at Change in the Grid the following Code

Code: Select all

@ 90.10 GRID Grid_9;
         WIDTH 950;
         HEIGHT 400;
         HEADERS {"CUSTNUM", "FIRSTNAME", "LASTNAME", "ADDRESS", "CITY", "STATE", "MARRIED", "PHONE", "FAX"};
         WIDTHS {100,100,100,250,150,30,70,130,130};
         VALUE 1;
         ON CHANGE ViewRecord_4687 ();
         ON DBLCLICK EditRecord_4687 ("");
         ON HEADCLICK {{|| head1_4687 ()}, {|| head2_4687 ()}};
         JUSTIFY {1,0,0,0,0,0,2,0,0}
and in FUNCTION LoadData_4687 (_sql), the following

Code: Select all

*:---------------------------------------------*
FUNCTION LoadData_4687( _sql )

Local i:=0 
PRIVATE cValue_1

DELETE ITEM ALL FROM Grid_9 of Win_9

If !empty( _sql )
   aTable := sql( dbo, _sql ) 
   Win_9.dSelect.Value := _sql 
Else 
   _sql := " SELECT t1.custnum, t1.firstname, t1.lastname, t1.address, t1.city, t1.state, t1.MARRIED, t1.phone, t1.fax "
   _sql += " FROM CUSTOMER t1 "

   if !empty(custom_query)
      _sql += " AND " + custom_query
   endif

   aTable := sql( dbo, _sql )
Endif 

For i := 1 to len(aTable) 
    aCurRow  := aTable[i] 
    cValue_1 := aCurRow[7]
 IF cValue_1 = '1'
    cValue_1 := '.T.'
  ELSE
    cValue_1 := '.F.'
  ENDIF 
   
 ADD ITEM { Str(aCurRow[1],10), aCurRow[2], aCurRow[3], aCurRow[4], aCurRow[5], aCurRow[6],  cValue_1 , aCurRow[8], aCurRow[9] } TO Grid_9 Of Win_9

   if i > 300 
      msginfo( "300+ rows" ) 
      exit 
   endif 

Next 

Win_9.StatusBar.Item(1) := "Selected " + alltrim(str( len(aTable) )) + " records "
Win_9.StatusBar.Item(2) := " "

StatusRec := "X" 

Win_9.Grid_9.SetFocus

RETURN Nil
I've only reviewed this part of the sample
It seems that now it is seen in Grid '.T.' or '.F.' instead of ' 0' or '1'
regards

Mustafa
Bonjour tout le monde,
J'ai toujours utilisé les base de données DBF et je suis en train de m'initier aux base de données Sqlite et j'utilise SqliteStudio
En Analysant les différents exemples de Mr Dragancesu et les vôtres Mr Mustafa ,je me sens dépassé, aussi je m'adresse a vous pour quelques renseignements si bien sur vous le permettez.
- Comment faites vous , pour remplir les données de la grille sans mentionner le nom de la table?
- Pouvez vous faire dans le domaine du possible un petit mode d'emploi pour expliquer les exemples que vous avez utilisés.
Grand merci à vous de votre aide.
Translation google.
Hello everyone,
I've always used the DBF database and I'm learning the Sqlite database and I'm using SqliteStudio
By analyzing the different examples of Mr Dragancesu and yours Mr Mustafa, I feel overwhelmed, so I am addressing you for some information so well you allow it.
- How do you fill in the grid data without mentioning the name of the table?
- Can you in the field of possible a small manual to explain the examples you used.
Many thanks to you for your help.
L'Algerie vous salut
Y.TABET

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

Post by mustafa » Thu Nov 14, 2019 10:56 am

Google Translation

Hello tonton2 friend
Thanks to the teachings of Friend Dragancesu I have been able to experience
a little bit SQLite

ConverterDBF <-> SQLite by Master Rathinagiri -> dbf2sqlite.zip
viewtopic.php?p=20587#p20587

viewtopic.php?f=5&t=5994&hilit=SQlite_Converter
SQlite_Tools.zip 

If I can serve you this little Sample that I publish
where you have the basic functions in a database
New, Modify, Low, Search and Printer

viewtopic.php?f=9&t=4795&hilit=mustafa+sqlite&start=10
Sqlite_Photo_Selection_New_Plus.zip

Regards /As Salamu Alaikum
Mustafa

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

Post by dragancesu » Thu Nov 14, 2019 12:48 pm

Hello Tonton
at one point I was curious about sqlite so I wrote a little bit about it,
after many tries and samples I gave up using it, it is skiled for simple applications, one exe and one database file, it works

But if an application needs to have a lot of data to account with then it needs something better, a good solution is MySQL (or MariaDB, for the program it's the same)

Harbour naturally uses DBF, MySQL and the like is a possibility and then you should first read the data that will be displayed

To start with it, need make some procedure/functions but it pays off, SQL is a simple and powerful language, the database stores a lot of data, quickly uses it select/insert/update/delete, data dictionary exsist, one make index and forget, it work

Data transfer to another computer is possible through tools, export and import

Post Reply