start with sqlite

General Help regarding HMG, Compilation, Linking, Samples

Moderator: Rathinagiri

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

Re: start with sqlite

Post by mustafa » 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

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 May 15, 2019 5:58 pm

I think that's a minor problem
I just do not use logical fields so I left it for later

Thanks for the suggestion

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

Post by mustafa » Mon May 20, 2019 3:16 pm

Hola amigos:
He modificado el Sample de nuestro amigo Dragan
con el respeto que se merece el autor, he intentado
ampliar un poco el Sample.

El Tema LOGICO-> BOOLEAN ahora ya sale .T. y .F.
Botones para moverse por la base de datos
un pequeño Search que no termina de funcionar
y resumir un poco el código prg del formulario
para Imprimir

Espero que pueda interesar el Sample de SQLite
Saludos
Mustafa

*----------------- Gloogle ---------------------*
Hello friends:
I modified the sample of our friend Dragan
With the respect that the author deserves, I've tried
expand the Sample a little.

The topic LOGICAL-> BOOLEAN now comes out .T. and .F.
Buttons to move through the database
a little search that does not finish working
and summarize a bit the prg code of the form
to print

I hope you can interest the SQLite Sample
regards
Mustafa
Attachments
SQLite_Demo.zip
(32.56 KiB) Downloaded 55 times

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 » Tue May 21, 2019 8:12 am

Nice, I hope it will be used by somebody

I think it's better for the LOGICAL field to use CHECKBOX control
you have created a SEARCH field, there is already something more flexible in the program on F2 / F3,
the query input fields are opened on F2, the jocker character are supported, then F3 is pressed to execute query

I'm looking at why logical fields are not resolved, this is based on the Rathi project HMGSqlite, and there is no work with logical fields
I do not think that Rathi is mistake of having left it, and will be added soon

I'm working on the next version, there will be everything

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

Post by mustafa » Tue May 21, 2019 12:20 pm

suerte con la nueva versión
saludos
*-----------------------------------------------------*
luck with the new version
regards

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 » Tue May 21, 2019 7:10 pm

As I promised, it has a new version

Now everything is in one program, attachment is ​​a program and resources that just need to be extracted by the working folder

Quick manual:
1. other -> connect data: enter the database with which you work (the results will be written in sql.ini)
2. tables-> import: will draw all tables into the dictionary of the data, if you add / edit / delete tables then repeat this

Creating a simple form:
1. form-> default: enter the name of the form, select the table and fields that should be in the form, and click create default
2. form-> edit: you can change the layout of the field on the screen (it will be describe later)
3. form-> generate: create a form program that only needs to be compiled

To do the first and third step in a minute, you will have a program, exe version

Note: it is assumed that the ID field is the primary key and numeric, if it is not then in the program "ID> 0", change to "<field_name_primary_key> >0"
Attachments
sqlsample.zip
(1.92 MiB) Downloaded 33 times
add.zip
(9.14 KiB) Downloaded 41 times
sqlcase.zip
(2.07 MiB) Downloaded 44 times
Last edited by dragancesu on Wed May 22, 2019 6:47 am, edited 2 times in total.

User avatar
mol
Posts: 3249
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 231 times
Been thanked: 132 times
Contact:

Post by mol » Tue May 21, 2019 8:53 pm

Some years ago I've created snall app with sqlite databasd. As I remember, there is no logical fields.
It can be done with integer, but you need to make concersion due to read and write to db

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

Post by mustafa » Wed May 22, 2019 10:23 am

+1

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 May 22, 2019 11:24 am

https://www.sqlite.org/datatype3.html

New versions support boolean data type, but internally write it as 1 or 0

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 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.

Post Reply