SQL in HMG

[franco]

Serge,
this program stops at dbo:
I also have to * out sql errors or it will not compile
I am missing something.
I am using hmg3.2
** PROGRAM SAMPLE

#include <hmg.ch>
#include <mysql.ch>
tbls()
DEFINE WINDOW Form_1 ;
AT 0,0 ;
WIDTH 600 ;
HEIGHT 610 ;
Main;
on release {|| 'close all tables' } ;
TITLE 'SQL Test'
@95,10 BUTTON Button_2 CAPTION ' SEARCH' WIDTH 75 HEIGHT 75 ;
MULTILINE NOTABSTOP TOOLTIP 'Add New Item:' ACTION { || ADD()}
END WINDOW

form_1.Center
form_1.Activate
RETURN
function tbls
LOCAL CF
if ! file('inv.dbf')
CF := {}
aADD(CF,{'NUM1' ,'C' , 15,0})
aADD(CF,{'DESC' ,'C' , 25,0})
aAdd(CF, {'NUM2' , 'C' , 25,0 })
aAdd(CF, {'NUM3' , 'C' , 25,0 })
DBCREATE( 'INV.DBF',CF )
USE
USE INV EXCLUSIVE NEW
INDEX ON NUM1 TO NUM1
do while recno()< 500
Inv->( DBAPPEND())
Inv->( FIELDPUT(1, 'P'+ALLTRIM(STR(20+RECNO())) ))
Inv->( FIELDPUT(2, 'DESC'+ALLTRIM(STR(20+RECNO())) ))
Inv->( FIELDPUT(3, 'N2'+ALLTRIM(STR(20+RECNO())) ))
Inv->( FIELDPUT(4, 'N3'+ALLTRIM(STR(20+RECNO())) ))
LOOP
enddo
USE
endif
return

procedure add
local cQuery1
cQuery1 := " SELECT num1, desc, num3 "
cQuery1 += " FROM inv1,"
cQuery1 += " WHERE num1 = '" + '"P2"' + "' "
cQuery1 += " ORDER BY num1"
cSQL := cQuery1
cQuery1 := dbo:Query( cQuery1 )
IF cQuery1:NetErr() // sql error
*SQL_Errors (PROCNAME(), '030', cQuery1:Error() , cSQL )
MSGINFO(cSQL_SELECT_ERROR, pNOK )
RETURN
ENDIF
return

** END OF PROGRAM

Franco

[Rathinagiri]

Franco,

You can't apply SQL commands on .dbf files like foxpro. The command you are using is to connect with MySQL server. So, it won't work.

[franco]

Thank you Rathinagiri
Is there a way to do this with sql
I have a (invoice) file. It has custid to link to a (customer) file.
It also has invoice number which links it to (invoice items) file.
I need to create a temp table with invoice number from invoice file.
customer name from customer file
items for the invoice number from items file.
This is hard to do and slow using tables and indexes.
With sql I should be able to select from different tables.
Do you know how .
Thanks Franco

[Rathinagiri]

Friend, I see people asking about accessing SQL servers just like .dbf files. That's why RDDSQL library is used in Harbour. But, what you are asking is just the opposite, ie., accessing DBF using SQL commands.

IMHO, DBF is not a true RDBMS. It is a flat file system. But group of tables (in flat files) are used to synchronize and made to look like a RDBMS.

Now, coming to the point, if you want to truly use SQL, then you have to move to any SQL based system like, MySQL, FireBird, PostgreSQL, Oracle etc., If you still want to use a simple flat file system and use SQL, then there is SQLite. I am using SQLite and MySQL extensively for small and larger enterprises. If Single or limited users go for SQLite and many users accessing the database go for MySQL.

I have shared an utility elsewhere in this forum to convert dbfs into SQLite/MySQL.

I don't know whether anybody had written any library to access DBF using SQL.

[andyglezl]

Hola Franco
Quizá esto te pueda servir... (desde la época de Clipper se podía hacer)
Si son varias DBF, puedes usar los "ALIAS->"
-------------------------------------------------------------------------------
Hi Franco
Perhaps this can serve you ... (from the time of Clipper could be done)
If you have multiple DBF, you can use the "Alias​​->"

Examples

. This example uses DBEVAL() to implement Count(), a user-
defined function that counts the number of records in a work area
matching a specified scope. The scope is passed as an array to
Count(). To make the example more interesting, there is a user-
defined command to create the scope array, thereby allowing you to
specify the scope in a familiar form. Additionally, there is a set
of manifest constants that define the attributes of the scope object.

// Scope command definition
#command CREATE SCOPE <aScope> [FOR <for>] ;
[WHILE <while>] [NEXT <next>] [RECORD <rec>] ;
[<rest:REST>] [ALL];
=>;
<aScope> := { <{for}>, <{while}>, <next>, ;
<rec>, <.rest.> }
//

// Scope attribute constants
#define FOR_COND 1
#define WHILE_COND 2
#define NEXT_SCOPE 3
#define REC_SCOPE 4
#define REST_SCOPE 5
//
// Create a scope and count records using it
LOCAL mySet, myCount
USE Customer NEW
CREATE SCOPE mySet FOR Customer = "Smith" WHILE ;
Zip > "90000"
myCount := Count( mySet )
RETURN

FUNCTION Count( aScope )
LOCAL nCount := 0
DBEVAL( {|| nCount++},;
aScope[ FOR_COND ],;
aScope[ WHILE_COND ],;
aScope[ NEXT_SCOPE ],;
aScope[ REC_SCOPE ],;
aScope[ REST_SCOPE ];
)
RETURN nCount

Files Library is CLIPPER.LIB

[serge_girard]

Franco,

Are your files plain text files or DBF files?


S

[franco]

Serge,
dbf Three dbf files invoice, invoiceitem, customer
Franco

[esgici]

...
Perhaps this can serve you ... (from the time of Clipper could be done)
...

Hi Andres

Code: Select all

  #command CREATE SCOPE ...

is an interesting implementation !

Where it from ?

If it is a share-able source, hopefully include many more useful examples

Saludos

[dhaine_adp]

Hi Franco,

Like what Rathi said, "you can't apply SQL commands on DBF" tables (flat files). Unlike Foxpro, Harbour is a different friendly monster. However you can utilized DBFCDX driver to deal with flat tables. If you wish to use SQL commands you would need to use the applicable SQL database, driver and library that suits your requirement.

On the other hand, Harbour has RDDSQL driver perhaps you can investigate this further.

If you wish to code using HMG ( this forum) or HMG Extended (http://www.hmgextended.com/" onclick=" ... rn false;), you would need to use the formal syntax to deal with DBFCDX/DBFNTX tables and SQL tables separately. Yep, its not like Foxpro in Harbour you are in the pilot seat, you'll have to deal with the files in their native format using the tools/libraries provided. That freedom (to choose libraries, mix-language programming) is the driving force behind those seasoned Clipper Programmers who have found the Lighthouse (Harbour).

Regards,

Danny

[andyglezl]

Hola esgici

Esto está en la Referencia/Manual de Clipper, así como esta yo no lo utilizo, mas bien solo utilizo el DBEVAL()
-----------------------------------------------------------------------------------------
hi esgici

This is in reference / Manual Clipper, and I do not use this, but rather just use the DBEVAL ()