start with sqlite

General Help regarding HMG, Compilation, Linking, Samples

Moderator: Rathinagiri

User avatar
mustafa
Posts: 1158
Joined: Fri Mar 20, 2009 11:38 am
DBs Used: DBF
Location: Alicante - Spain
Contact:

Re: start with sqlite

Post by mustafa »

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: 920
Joined: Mon Jun 24, 2013 11:53 am
DBs Used: DBF, MySQL, Oracle
Location: Subotica, Serbia

Re: start with sqlite

Post by dragancesu »

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: 1158
Joined: Fri Mar 20, 2009 11:38 am
DBs Used: DBF
Location: Alicante - Spain
Contact:

Re: start with sqlite

Post by mustafa »

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 248 times
User avatar
dragancesu
Posts: 920
Joined: Mon Jun 24, 2013 11:53 am
DBs Used: DBF, MySQL, Oracle
Location: Subotica, Serbia

Re: start with sqlite

Post by dragancesu »

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: 1158
Joined: Fri Mar 20, 2009 11:38 am
DBs Used: DBF
Location: Alicante - Spain
Contact:

Re: start with sqlite

Post by mustafa »

suerte con la nueva versión
saludos
*-----------------------------------------------------*
luck with the new version
regards
User avatar
dragancesu
Posts: 920
Joined: Mon Jun 24, 2013 11:53 am
DBs Used: DBF, MySQL, Oracle
Location: Subotica, Serbia

Re: start with sqlite

Post by dragancesu »

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 225 times
add.zip
(9.14 KiB) Downloaded 221 times
sqlcase.zip
(2.07 MiB) Downloaded 244 times
Last edited by dragancesu on Wed May 22, 2019 6:47 am, edited 2 times in total.
User avatar
mol
Posts: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: start with sqlite

Post by mol »

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: 1158
Joined: Fri Mar 20, 2009 11:38 am
DBs Used: DBF
Location: Alicante - Spain
Contact:

Re: start with sqlite

Post by mustafa »

+1
User avatar
dragancesu
Posts: 920
Joined: Mon Jun 24, 2013 11:53 am
DBs Used: DBF, MySQL, Oracle
Location: Subotica, Serbia

Re: start with sqlite

Post by dragancesu »

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

New versions support boolean data type, but internally write it as 1 or 0
User avatar
SALINETAS24
Posts: 667
Joined: Tue Feb 27, 2018 3:06 am
DBs Used: DBF
Contact:

Re: start with sqlite

Post by SALINETAS24 »

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.
Como dijo el gran pensador Hommer Simpson..., - En este mundo solo hay 3 tipos de personas, los que saben contar y los que no. :shock:
Post Reply