Page 1 of 3
SQL in HMG
Posted: Thu Sep 25, 2014 4:10 pm
by 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
Re: SQL in HMG
Posted: Thu Sep 25, 2014 4:29 pm
by 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
Re: SQL in HMG
Posted: Thu Sep 25, 2014 6:49 pm
by 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
Re: SQL in HMG
Posted: Thu Sep 25, 2014 7:08 pm
by 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
Re: SQL in HMG
Posted: Thu Sep 25, 2014 7:16 pm
by 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
Re: SQL in HMG
Posted: Thu Sep 25, 2014 9:37 pm
by franco
Serge
I will try and let you know.
Thanks for help ....... Franco
Re: SQL in HMG
Posted: Fri Sep 26, 2014 3:51 pm
by 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
Re: SQL in HMG
Posted: Fri Sep 26, 2014 4:00 pm
by serge_girard
Franco,
Probably you need this include: mysql.ch
Serge
Re: SQL in HMG
Posted: Fri Sep 26, 2014 4:16 pm
by 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
Re: SQL in HMG
Posted: Fri Sep 26, 2014 4:23 pm
by 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