SOLVED: Multi-database queries attempt

I am trying to follow the "Multi-database queries" in the help with the code below where I am using SQL Server Native driver and HF CS. When I attempt the join between HF and SQL Server I get an error on the first gwsdQRY_xx_Test file saying it does not exist alhough both have records and the where condition records are both there.

Any suggestions would be appreciated.

gwsdQRY_HF_Test is Data Source
gwsdQRY_PO_Test is Data Source
gwsdQRY_Join is Data Source

sHFSQL is string = [
SELECT 
PurcLineBinary.PurcLineBinaryID,
PurcLineBinary.ROWID,
PurcLineBinary.PURC_ORDER_ID,
PurcLineBinary.PURC_ORDER_LINE_NO,
PurcLineBinary.TYPE,
PurcLineBinary.BITS,
PurcLineBinary.BITS_LENGTH,
PurcLineBinary.Description 
FROM 
PurcLineBinary
WHERE
PurcLineBinary.Description LIKE '%ANODIZE%'
]

IF NOT HExecuteSQLQuery(gwsdQRY_HF_Test,HF_Server,hNoBind,sHFSQL) THEN
	Error(HErrorInfo())
ELSE
	HReadFirst(gwsdQRY_HF_Test)
END
sPO_POL is string =[
SELECT 
	PURCHASE_ORDER.ROWID+10 AS ROWID_PO10,	
	PURCHASE_ORDER.ID AS ID,	
	PURCHASE_ORDER.VENDOR_ID AS VENDOR_ID
FROM 
	PURCHASE_ORDER
ORDER BY 
	ORDER_DATE DESC,	
	VENDOR_ID ASC

]

IF NOT HExecuteSQLQuery(gwsdQRY_PO_Test,SQL_Server,hQueryWithoutCorrection+hNoBind,sPO_POL) // HExecuteSQLQuery()gwsdQRY_PO_Test,SQL_Server,hNoBind,sPO_POL) then
	Error(HErrorInfo())
ELSE
	HReadFirst(gwsdQRY_PO_Test)
	HReadSeekFirst(gwsdQRY_PO_Test,rowid_po10,gwsdQRY_HF_Test.rowid)
END

Info("HF recs: "+HNbRec(gwsdQRY_HF_Test),"PO_POL recs: "+HNbRec(gwsdQRY_PO_Test))

sJoin is string = [
select * FROM gwsdQRY_HF_Test,gwsdQRY_PO_Test
where gwsdQRY_HF_Test.ROWID = gwsdQRY_PO_Test.ROWID_PO10
]

IF NOT HExecuteSQLQuery(gwsdQRY_Join,hNoBind,sJoin) THEN 
	Error(HErrorInfo())
ELSE
	HReadFirst(gwsdQRY_Join)
END

Info("Join recs:"+HNbRec(gwsdQRY_Join))

Jeff Graham
Cascade Consulting



Edited 1 time(s). Last edit at 12/04/2022 09:17PM by Jeff Graham.

Hi Jeff,

Why don't you put an "hQueryWithoutCorrection" on the final query. If that doesn't work, consider simplifying both queries and add additional columns one at a time. Are the two ROWID's compatible (same type)?

My two cents

issah

Hi issah,

Thanks for the suggestion. Unfortunately the "hQueryWithoutCorrection" requires specifying connection and then second file is considered an element of the SQLServer. "hQueryWithoutHFCorrection" is acccepted but also says the first file is invalid.

I also made another test case with simple tables and text ID fields which hits the same problem.

I then did a test with two exising simple Queries in the project, one HF and one SQL. That worked using hQueryDeafult.

Thanks for the suggesions!

Jeff Graham
Cascade Consulting

Hi Jeff,

That's great....happy to hear. Did you have to put the hQueryDefault on each query?

Regards

issah

Hi Issah,
...
No, actually the only way I got it to work was by using existing Queries and then doing the sJoin query.

HOWEVER better than that I discovered the join can be put in an SQL Query and then the resulting field names are usable!

Here is the code now:

QRY_PO_POL_Search.pOrderDateFrom = StringToDate("01/01/2021",maskDateSystem)
QRY_PO_POL_Search.pOrderDateTo = StringToDate("03/01/2021",maskDateSystem)
HExecuteQuery(QRY_PO_POL_Search)

QRY_PLB_ByDescription.pDescription = "anodize"
HExecuteQuery(QRY_PLB_ByDescription)

Info("SQL: "+HNbRec(QRY_PO_POL_Search),"HF: "+HNbRec(QRY_PLB_ByDescription))


IF NOT HExecuteQuery(QRY_PO_POl_Seach_test) THEN 
	Error("4 HExecuteQuery(QRY_PO_POl_Seach_test)", HError,HErrorInfo())
ELSE
	Info("Join recs:"+HNbRec(QRY_PO_POl_Seach_test))
	HReadFirst(QRY_PO_POl_Seach_test)
END
Info("First Vendor = "+QRY_PO_POl_Seach_test.VENDOR_ID)

I will gladly share my experiences with anyone that wants to do this.

Jeff Graham
Cascade Consulting