SQL in HMG

[franco]

Hello again all,
I used visual foxpro and this type of sql. Can I do the same in hmg.
2 or more tables. One is invoice, one is invoiceitem. They are linked by invoicenumber and invoice has field custid and invoicenumber.
invoiceitem has field item and invoicenumber
here is command
select item, invoicenumber, invoice->custid from invoiceitem, invoice where invoiceitem->invoicenumber := invoice->invoicenumber into table temp.

I see sql in samples but am having trouble trying out this type of command.
Thanks in advance ..................... Franco

[serge_girard]

Franco,

In MySQL I do this as following:

Code: Select all

cQuery1  := " SELECT A.MESS_ID, A.MESS_NR, A.MESS_FROM, A.MESS_TO, A.MESSAGE, A.TIMESTAMP, B.FAM_NAAM, B.VOOR_NAAM "
cQuery1  += " FROM   MESSAGE2   A,"
cQuery1  += "        GEBRUIKERS B"
cQuery1  += " WHERE  A.MESS_TO    = '" + ANYTOSQL(XXX_PERS_ID) + "' "
cQuery1  += " AND    A.MESS_READ  = 'N' "
cQuery1  += " AND    A.MESS_FROM  = B.PERSON_ID "
cQuery1  += " ORDER BY 1,2,3,4 "
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

Is this what you mean?

Serge

[franco]

Thank you Serge,
you have made it a bit confusing for me but I mean in a simpler way.
mdate := date() -10
MEMORY TABLE CREATED := MYTABLE
cQuery1 := " SELECT A.ID, A.NR, B.FAM_id, B.date " // A.id and B.FAM_id are the same. I want to view field A.NR
cQuery1 += " FROM A,"
cQuery1 += " B"
cQuery1 += " WHERE A.id = "
cQuery1 += " B.FAM_id "
cQuery1 += " and B.date = mdate"
cQuery1 += " into table MYTABLE" // IN FOXPRO I COULD SAY INTO CURSOR 'MYTEMP1'.. DID NOT HAVE TO CREATE TABLE
??? I DO NOT KNOW WHAT THESE LINES DO //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
IF EMPTY(TEMP)
MSG('NO DATA')
RETURN
ENDIF
PROCEED

[franco]

Sorry Serge,
in reading your code closer it makes sense to me until the line
cQuery1 := dbo:Query( cQuery1 )
normally I would not put this in a variable but make it a continuing line like.

SELECT A.ID, A.NR, B.FAM_id, B.date from a, b where a.id = b.fam_id and b.date = mdate into table temp
Then error check

Franco

[serge_girard]

Hello Franco,

It be maybe confusing because it is the MySQL-way:

1) Build query
2) Save the querystring into cSQL (for error reporting)
3) Execute the query (cQuery1 := dbo:Query( cQuery1 ))
4) Check if OK (IF cQuery1:NetErr())
5) Retrieve the data (if SELECT)

Hope this helps,

Serge

[franco]

Serge
I will try and let you know.
Thanks for help ....... Franco

[franco]

Serge,
is there a .ch file I need at start of program.
I get an error no dbo:
also when this works where does the information go, I do not see a (into table xxx )
in command
Franco

[serge_girard]

Franco,

Probably you need this include: mysql.ch

Serge

[franco]

Serge,
have no mysql.ch in my include folder. I get error no mysql
how do I put .ch file in my include folder
Thanks ..... Franco

[serge_girard]

Franco,

It must be located in the HMG.3.3.1\HARBOUR\CONTRIB\HBMYSQL folder.

In HMG Ide click on the Project Browser / TAB Includes


Serge