SQL in HMG
Moderator: Rathinagiri
SQL in HMG
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
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
All The Best,
Franco
Canada
Franco
Canada
- serge_girard
- Posts: 3167
- Joined: Sun Nov 25, 2012 2:44 pm
- DBs Used: 1 MySQL - MariaDB
2 DBF - Location: Belgium
- Contact:
Re: SQL in HMG
Franco,
In MySQL I do this as following:
Is this what you mean?
Serge
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
Serge
There's nothing you can do that can't be done...
Re: SQL in HMG
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
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
All The Best,
Franco
Canada
Franco
Canada
Re: SQL in HMG
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
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
All The Best,
Franco
Canada
Franco
Canada
- serge_girard
- Posts: 3167
- Joined: Sun Nov 25, 2012 2:44 pm
- DBs Used: 1 MySQL - MariaDB
2 DBF - Location: Belgium
- Contact:
Re: SQL in HMG
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
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
There's nothing you can do that can't be done...
Re: SQL in HMG
Serge
I will try and let you know.
Thanks for help ....... Franco
I will try and let you know.
Thanks for help ....... Franco
All The Best,
Franco
Canada
Franco
Canada
Re: SQL in HMG
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
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
All The Best,
Franco
Canada
Franco
Canada
- serge_girard
- Posts: 3167
- Joined: Sun Nov 25, 2012 2:44 pm
- DBs Used: 1 MySQL - MariaDB
2 DBF - Location: Belgium
- Contact:
Re: SQL in HMG
Franco,
Probably you need this include: mysql.ch
Serge
Probably you need this include: mysql.ch
Serge
There's nothing you can do that can't be done...
Re: SQL in HMG
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
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
All The Best,
Franco
Canada
Franco
Canada
- serge_girard
- Posts: 3167
- Joined: Sun Nov 25, 2012 2:44 pm
- DBs Used: 1 MySQL - MariaDB
2 DBF - Location: Belgium
- Contact:
Re: SQL in HMG
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
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
There's nothing you can do that can't be done...