Can't get records from foxpro cursor.

[Niki_S]

I have a Cursor in my foxpro as ACPGRN. And I'm trying to get some data from MSSSQL trough the cursor that I created in foxpro like this.

SELECT ACPGRN 
SCAN 
	stra="select MAS.dbo.Acp_Invoice_Hdr.cBatchNo,MAS.dbo.Acp_Invoice_Hdr.nInvValue, MAS.dbo.Acp_Invoice_Hdr.nTax1_Id, MAS.dbo.Acp_Invoice_Hdr.nTax2_Id, MAS.dbo.Acp_Invoice_Hdr.nTax3_Id,"
	stra=stra+"MAS.dbo.Acp_Invoice_Hdr.nTax4_Id,MAS.dbo.Acp_Invoice_Hdr.nTax1Value,MAS.dbo.Acp_Invoice_Hdr.nTax2Value,MAS.dbo.Acp_Invoice_Hdr.nTax3Value,"
	stra=stra+"MAS.dbo.Acp_Invoice_Hdr.nTax4Value, MAS.dbo.Acp_InvoiceOtherCharg.nOtherChargeValue "
	stra=stra+" from  MAS.dbo.Acp_Invoice_Hdr inner join  MAS.dbo.Acp_InvoiceOtherCharg on MAS.dbo.Acp_InvoiceOtherCharg.nBatchInvId=MAS.dbo.Acp_Invoice_Hdr.nBatchInvId "
	stra=stra+" where MAS.dbo.Acp_Invoice_Hdr.cBatchNo=?ACPGRN.cBatchNo "
	SQLEXEC(hndOps,stra,'_BatchValue')
ENDSCAN

When I run my form I can't get all the records and it comes only the first records. If the first cBatchNo in myACPGRN is 1234 the output of the _BatchValue in 1234.
How can I get all the records to my cursor?
Thank You

 

[Chris Miller]

Just think of what this does. You create a _BatchValue cursor for one AACPGRN.cBatchNo. In the next iteration you overwrite it. In the end you only have data for the last cNatchno in ACPGRN.

So you have to accumulate the data.

Also, you should by now see that TEXT..ENDTEXT is far better readable. Besides that stra always is the same, the ?ACPGRN.cBatchno is substituted by SQLEXEC, not by building the string, so this can move out of the loop.

TEXT TO stra NOSHOW
select MAS.dbo.Acp_Invoice_Hdr.cBatchNo,MAS.dbo.Acp_Invoice_Hdr.nInvValue, MAS.dbo.Acp_Invoice_Hdr.nTax1_Id, MAS.dbo.Acp_Invoice_Hdr.nTax2_Id, MAS.dbo.Acp_Invoice_Hdr.nTax3_Id, 
    MAS.dbo.Acp_Invoice_Hdr.nTax4_Id,MAS.dbo.Acp_Invoice_Hdr.nTax1Value,MAS.dbo.Acp_Invoice_Hdr.nTax2Value,MAS.dbo.Acp_Invoice_Hdr.nTax3Value,
    MAS.dbo.Acp_Invoice_Hdr.nTax4Value, MAS.dbo.Acp_InvoiceOtherCharg.nOtherChargeValue
    FROM  MAS.dbo.Acp_Invoice_Hdr 
    INNER JOIN  MAS.dbo.Acp_InvoiceOtherCharg ON MAS.dbo.Acp_InvoiceOtherCharg.nBatchInvId=MAS.dbo.Acp_Invoice_Hdr.nBatchInvId "
    WHERE MAS.dbo.Acp_Invoice_Hdr.cBatchNo=?ACPGRN.cBatchNo
ENDTEXT

SELECT ACPGRN 
SCAN 
	SQLEXEC(hndOps,stra,'_BatchValue') && still to fix: This overwrites previous result.
ENDSCAN

You still need to get this into your head, if you repeatedly create the same cursor or do anything into the same target object/variable, you overwrite what you already had. There is no automatic accuulation of data. You already know this when you put your strings together, you have to write stra = stra+ something new, not just str= somethingnew.

It would be nice, if you could join the ACPGRN cursor, there would be a way to do so by inserting its data into a temp table, but for now we'll simply append all _BatchValue into one _BatchValues, like this:

TEXT TO stra NOSHOW
select MAS.dbo.Acp_Invoice_Hdr.cBatchNo,MAS.dbo.Acp_Invoice_Hdr.nInvValue, MAS.dbo.Acp_Invoice_Hdr.nTax1_Id, MAS.dbo.Acp_Invoice_Hdr.nTax2_Id, MAS.dbo.Acp_Invoice_Hdr.nTax3_Id, 
    MAS.dbo.Acp_Invoice_Hdr.nTax4_Id,MAS.dbo.Acp_Invoice_Hdr.nTax1Value,MAS.dbo.Acp_Invoice_Hdr.nTax2Value,MAS.dbo.Acp_Invoice_Hdr.nTax3Value,
    MAS.dbo.Acp_Invoice_Hdr.nTax4Value, MAS.dbo.Acp_InvoiceOtherCharg.nOtherChargeValue
    FROM  MAS.dbo.Acp_Invoice_Hdr 
    INNER JOIN  MAS.dbo.Acp_InvoiceOtherCharg ON MAS.dbo.Acp_InvoiceOtherCharg.nBatchInvId=MAS.dbo.Acp_Invoice_Hdr.nBatchInvId "
    WHERE MAS.dbo.Acp_Invoice_Hdr.cBatchNo=?ACPGRN.cBatchNo
ENDTEXT

Use In Select('_BatchValues')

SELECT ACPGRN 
SCAN 
	SQLEXEC(hndOps,stra,'_BatchValue') 
  If NOT Used('_BatchValues')
     Select * From _BatchValue Into Cursor _BatchValues READWRITE && creating _Batchvalues with the first data
  Else
     SELECT _BatchValues
     APPEND FROM DBF('_BatchValue') && collecting all data results here
  Endif
ENDSCAN

Chriss

 

[Chris Miller]

There's one more thing - and something new to learn, to make this a little better working: SQLPREPARE.

You can prepare a statement, which means SQL Server keeps it in mind (precompiled) and then you can call it multiple times. This preparation is possible for queries that don't change in themselves, but only in their parameters. And we have such a case.

So there we go:

TEXT TO stra NOSHOW
select MAS.dbo.Acp_Invoice_Hdr.cBatchNo,MAS.dbo.Acp_Invoice_Hdr.nInvValue, MAS.dbo.Acp_Invoice_Hdr.nTax1_Id, MAS.dbo.Acp_Invoice_Hdr.nTax2_Id, MAS.dbo.Acp_Invoice_Hdr.nTax3_Id, 
    MAS.dbo.Acp_Invoice_Hdr.nTax4_Id,MAS.dbo.Acp_Invoice_Hdr.nTax1Value,MAS.dbo.Acp_Invoice_Hdr.nTax2Value,MAS.dbo.Acp_Invoice_Hdr.nTax3Value,
    MAS.dbo.Acp_Invoice_Hdr.nTax4Value, MAS.dbo.Acp_InvoiceOtherCharg.nOtherChargeValue
    FROM  MAS.dbo.Acp_Invoice_Hdr 
    INNER JOIN  MAS.dbo.Acp_InvoiceOtherCharg ON MAS.dbo.Acp_InvoiceOtherCharg.nBatchInvId=MAS.dbo.Acp_Invoice_Hdr.nBatchInvId "
    WHERE MAS.dbo.Acp_Invoice_Hdr.cBatchNo=?ACPGRN.cBatchNo
ENDTEXT

SQLPrepare(hndOps, stra, '_BatchValue')

Use In Select('_BatchValues')
SELECT ACPGRN 
SCAN 
   SQLEXEC(hndOps) && now executes the (last) query which was prepared by SQLPREPARE().
   If NOT Used('_BatchValues')
      SELECT * FROM _BatchValue INTO CURSOR _BatchValues READWRITE && creating _Batchvalues with the first data
   Else
      SELECT _BatchValues
      APPEND FROM DBF('_BatchValue') && collecting all further data results here
   Endif
ENDSCAN

Chriss

 

[Niki_S]

Thank you Chriss...
I have another one to know.

SELECT _BatchVal
SCAN
	IF _BatchVal.nTax_Id <> '4' THEN 
		UPDATE ACPGRN SET ACPGRN.nBatchValue = _BatchVal.nBalance WHERE ACPGRN.cBatchNo=_BatchVal.cBatchNo
	ELSE
		SELECT ACPGRN 
		replace nBatchValue WITH nBatchValue 
	ENDIF 
ENDSCAN

What is the mistake in this code? When I run my code they said Operator type mismatch.
Can You please tell me why is that?
And I want to know hot to do this with a temp table. If you can please tell me how to do that also.
Thank you

 

[Chris Miller]

Operator type mismatch mainly means type mismatch, you compare two uncomparable things, like a number and a string.

I bet the nTax_Id is numeric, so compare it with 4, not '4'.

The temp table needs a bit of work, I don't have the time for that right now.

Chriss

 

[Niki_S]

okay thank you,
If you have time to say how to do it with temp table, please tell how to do that also.

 

[Niki_S]

SELECT _BatchVal
SCAN
	IF _BatchVal.nTax_Id <> 4 THEN 
		UPDATE ACPGRN SET ACPGRN.nBatchValue = _BatchVal.nBalance WHERE ACPGRN.cBatchNo=_BatchVal.cBatchNo
	ENDIF 
ENDSCAN

when I run my form this is updating only first record in _BatchVal. How to update all the records using this where condition?
Thank you

 

[Chris Miller]

Your update isn't updating _BatchVal, you make a series of UPDATES of ACPGRN with varying cBatchNo.

If you need to check whether they are done, then in ACPGRN not in _BatchVal.

If that's not what you want, then change the updates to what you actually want to update. It's always UPDATE targettable SET field1 = val1, field2= val2, etc.


Chriss

 

[Niki_S]

Thank you chriss. I fixed it like this.

SELECT _BatchVal
INDEX On cBatchNo TAG BatchNo
SELECT ACPGRN 
   SCAN
   IF _BatchVal.nTax_Id <> 4 THEN
	   SELECT _BatchVal
	   SEEK ACPGRN.cBatchNo
	   
	   SELECT ACPGRN
	   REPLACE nBatchValue WITH _BatchVal.nBalance
	ENDIF 
   ENDSCAN

Thank you for the support.

 

[Chris Miller]

I get to the TEMP tables.

But first one even much simpler solution: Depending on how many records your ACPGRN cursor has, you can build a comma separated list of bathnos and change the WHERE condition from

WHERE MAS.dbo.Acp_Invoice_Hdr.cBatchNo=?ACPGRN.cBatchNo

to

WHERE MAS.dbo.Acp_Invoice_Hdr.cBatchNo IN ('001','102','403')

The idea I had with a temp table is based on knowing how it's easier to get all data at once with a list of batchnos in another table. INNER JOIN acts like a WHERE clause for multiple values. So - in short - it would be nice to have ACPGRN data in the SQL Server. And that could be done in the form of a temp table.

The next step is to know that SQLEXEC can not only exeute a single query, you can excute a script including temp table createion, inserts of data and multiple queries.

The creation of a temp table is quite easy:

CREATE TABLE @temptable (cBatchno char(4)) -- or whatever the column data type must be

To get over your list of values you would need to generate a list of values, again, a bit different than for the IN clause:

CREATE TABLE @temptable (cBatchno char(4))
INSERT INTO @temptable (cBatchno) VALUES ('001'),('102'),('403')

You see? There are multiple brackets, one around each record you insert its a short version for multiple inserts like

INSERT INTO @temptable (cBatchno) VALUES ('001')
INSERT INTO @temptable (cBatchno) VALUES ('102')
INSERT INTO @temptable (cBatchno) VALUES ('102')

So you'd scan the list of ACPGRN Batchnos and build the IN list or the VALUES list within the sql code to send over by SQLEXEC.

All in one:

CREATE TABLE @temptable (cBatchno char(4))
INSERT INTO @temptable (cBatchno) VALUES ('001'),('102'),('403')

SELECT MAS.dbo.Acp_Invoice_Hdr.cBatchNo,MAS.dbo.Acp_Invoice_Hdr.nInvValue, MAS.dbo.Acp_Invoice_Hdr.nTax1_Id, MAS.dbo.Acp_Invoice_Hdr.nTax2_Id, MAS.dbo.Acp_Invoice_Hdr.nTax3_Id, 
    MAS.dbo.Acp_Invoice_Hdr.nTax4_Id,MAS.dbo.Acp_Invoice_Hdr.nTax1Value,MAS.dbo.Acp_Invoice_Hdr.nTax2Value,MAS.dbo.Acp_Invoice_Hdr.nTax3Value,
    MAS.dbo.Acp_Invoice_Hdr.nTax4Value, MAS.dbo.Acp_InvoiceOtherCharg.nOtherChargeValue
    FROM  MAS.dbo.Acp_Invoice_Hdr 
    INNER JOIN  MAS.dbo.Acp_InvoiceOtherCharg ON MAS.dbo.Acp_InvoiceOtherCharg.nBatchInvId=MAS.dbo.Acp_Invoice_Hdr.nBatchInvId
    INNER JOIN @temptable as tempBatchnos ON MAS.dbo.Acp_Invoice_Hdr.cBatchNo = tempBatchnos

Chriss

 

[Niki_S]

Thank you Chriss.
But I have an issu in this. i DID MY CODE LIKE THIS.

variable1 = "@temptable"
CREATE TABLE (variable1)  (nBatchId n(10))
INSERT INTO @temptable (nBatchId) VALUES (_ACPgrn.nBatchId) 


stra="SELECT MAS.dbo.Acp_Invoice_Hdr.cBatchNo,MAS.dbo.Acp_Invoice_Hdr.nInvValue, MAS.dbo.Acp_Invoice_Hdr.nTax1_Id, MAS.dbo.Acp_Invoice_Hdr.nTax2_Id, MAS.dbo.Acp_Invoice_Hdr.nTax3_Id, "
stra=stra+" MAS.dbo.Acp_Invoice_Hdr.nTax4_Id,MAS.dbo.Acp_Invoice_Hdr.nTax1Value,MAS.dbo.Acp_Invoice_Hdr.nTax2Value,MAS.dbo.Acp_Invoice_Hdr.nTax3Value, "
stra=stra+ " MAS.dbo.Acp_Invoice_Hdr.nTax4Value, MAS.dbo.Acp_InvoiceOtherCharg.nOtherChargeValue "
stra=stra+" FROM  MAS.dbo.Acp_Invoice_Hdr "
stra=stra+" INNER JOIN  MAS.dbo.Acp_InvoiceOtherCharg ON MAS.dbo.Acp_InvoiceOtherCharg.nBatchInvId=MAS.dbo.Acp_Invoice_Hdr.nBatchInvId "
stra=stra+" INNER JOIN @temptable as tempbatch ON MAS.dbo.Acp_Invoice_Hdr.nBatchInvId = tempbatch   "
stra=stra+" where MAS.dbo.Acp_Invoice_Hdr.nBatchInvId =tempbatch.nBatchId "
SQLEXEC(hndOps,stra,'_BatchValNew')

When I run this it says Must declare the table variable @temptable.
How can I fix this?

 

[Chris Miller]

You don't create the temp table in Foxpro, you create it in SQL Server. The whole script has to go to SQL Server, that's why I mentioned SQLEXEC isn't limited to sql queries, it can send a whole script to SQL Server.


Chriss

 

[Niki_S]

I used this way to select records using a cursor.

SELECT tempBatchSibNew
SCAN
TEXT TO stra NOSHOW
select * from Mas.dbo.vMasTransDt where cDocType= ?cName  AND cDocNo=?tempBatchSibNew.cGrnNO  AND cLineNo= ?tempBatchSibNew.cLineNo
ENDTEXT

Use In Select('tempBatchTransDt')

SELECT tempBatchSibNew
SCAN 
	SQLEXEC(hndOps,stra,'_Trnqty') 
  If NOT Used('tempBatchTransDt')
     Select * From _Trnqty Into Cursor tempBatchTransDt READWRITE 
  Else
     SELECT tempBatchTransDt
     APPEND FROM DBF('_Trnqty') 
  Endif
ENDSCAN 
ENDSCAN

But when I us this it takes more time to get my output. Is there have any other way to get my output or how can I resolve this?
Thank you

 

[Chris Miller]

How many records are in tempBatchSibNew?

This does not work good with lots of IDs.

If you got tempBatchSibNew from SQL Server, too, then just don't get the ID list, just inner join it with Mas.dbo.vMasTransDt to get the data you really want.

Chriss

 

[Niki_S]

There have over 10 000 records in my tempBatchSibNew.
I tried something like this.

stra="select cBatchNo,nSupplierID as nSuplID,cPaymentCurr as cPoCurrancy  from MAS.dbo.Acp_Batch where cTag='W' and  cOrderType=?thisform.cboPoType.value and cFactory=?thisform.cboFactory.value"
SQLEXEC(hndOps,stra,'_Tag')
 
stra="SELECT cBatchNo,nPoDtlID,cPoLine,nBatchInvId,nSuplID,cSuplName,cPaymentCurr as cPoCurrancy,nInvValue,nConvRate,cTag,cFtyCD "
stra=stra+" FROM MAS.dbo.vAcp_All_InvDtl "
stra=stra+" INNER JOIN _Tag "
stra=stra+" ON vAcp_All_InvDtl.nPoDtlID=_Tag.nPoDtlID "
stra=stra+" where cBatchNo=?_Tag.cBatchNo "
SQLEXEC(hndOps,stra,'Temp_tag')

It says Invalid object name _Tag . What should I do for this?

 

[Chris Miller]

MSSQL still has no access to the VFP cursors, just by ? for single fields of single records.

But you can simply do this:
Don't query into a cursor _TAG.

You just want this to filter vAcp_All_InvDtl, then do so - on the server side:

strSubquery = "select cBatchNo,nSupplierID as nSuplID,cPaymentCurr as cPoCurrancy  from MAS.dbo.Acp_Batch where cTag='W' and  cOrderType=?thisform.cboPoType.value and cFactory=?thisform.cboFactory.value"

stra="SELECT cBatchNo,nPoDtlID,cPoLine,nBatchInvId,nSuplID,cSuplName,cPaymentCurr as cPoCurrancy,nInvValue,nConvRate,cTag,cFtyCD "
stra=stra+" FROM MAS.dbo.vAcp_All_InvDtl "
stra=stra+" INNER JOIN ("+strSubquery") as Batches On  vAcp_All_InvDtl.nPoDtlID = Batches.nPoDtlID"
SQLEXEC(hndOps,stra,'Result')

Chriss

 

[Niki_S]

When I used this it says there is a syntax error in 4th line.

stra=stra+" INNER JOIN ("+strSubquery") as Batches On  vAcp_All_InvDtl.nPoDtlID = Batches.nPoDtlID"

 

[Chris Miller]

A plus is missing, sorry.

stra=stra+" INNER JOIN ("+strSubquery+") as Batches On  vAcp_All_InvDtl.nPoDtlID = Batches.nPoDtlID"

Chriss

 

[Niki_S]

strSubquery = "select cBatchNo,nSupplierID as nSuplID,cPaymentCurr as cPoCurrancy  from MAS.dbo.Acp_Batch where cTag='W' and  cOrderType=?thisform.cboPoType.value and cFactory=?thisform.cboFactory.value"

stra="SELECT cBatchNo,nPoDtlID,cPoLine,nBatchInvId,nSuplID,cSuplName,cPaymentCurr as cPoCurrancy,nInvValue,nConvRate,cTag,cFtyCD "
stra=stra+" FROM MAS.dbo.vAcp_All_InvDtl "
stra=stra+" INNER JOIN ("+strSubquery+") as Batches On  vAcp_All_InvDtl.cBatchNo= Batches.cBatchNo "
SQLEXEC(hndOps,stra,'Result')

When I run this it says ambiguous column name 'cBatchNo' . Why is that?
We can't use nPoDtlID to link this because in Batches there haven't nPoDtlID .

 

[Chris Miller]

Yes, but then how would it work with _TAG, just for sake assuming SQL Server would have access to your VFP cursor, if _TAG has no nPoDtlID?

You wanted to use

...ON vAcp_All_InvDtl.nPoDtlID=_Tag.nPoDtlID

So, this now does not only work for one reason - SQL Server can't access the VFP cursors - but two reasons - even if, _TAG has no nPoDtlID field.


You have to sort out what you really need to join with. One thing is for sure: SQL Server can only access SQL Server tables. But since you use queries from SQL Server data, SQL Server can use the data you have in a cursor just by doing the same query again as a subquery.

So, the subquery has to be something giving a nPoDtlID.

I can't help you with the necessary subquery.

Chriss