×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Can't get records from foxpro cursor.

Can't get records from foxpro cursor.

Can't get records from foxpro cursor.

(OP)
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.

CODE

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

RE: Can't get records from foxpro cursor.

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.

CODE

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:

CODE

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

RE: Can't get records from foxpro cursor.

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:

CODE

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

RE: Can't get records from foxpro cursor.

(OP)
Thank you Chriss...bigsmile
I have another one to know.

CODE

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

RE: Can't get records from foxpro cursor.

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

RE: Can't get records from foxpro cursor.

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

RE: Can't get records from foxpro cursor.

(OP)

CODE

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

RE: Can't get records from foxpro cursor.

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

RE: Can't get records from foxpro cursor.

(OP)
Thank you chriss. I fixed it like this.

CODE

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. bigsmile

RE: Can't get records from foxpro cursor.

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

CODE

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

to

CODE

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:

CODE

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:

CODE

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

CODE

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:

CODE

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

RE: Can't get records from foxpro cursor.

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

CODE

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?

RE: Can't get records from foxpro cursor.

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

RE: Can't get records from foxpro cursor.

(OP)
I used this way to select records using a cursor.

CODE

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

RE: Can't get records from foxpro cursor.

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

RE: Can't get records from foxpro cursor.

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

CODE

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?

RE: Can't get records from foxpro cursor.

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:

CODE

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

RE: Can't get records from foxpro cursor.

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

CODE

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

RE: Can't get records from foxpro cursor.

A plus is missing, sorry.

CODE

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

Chriss

RE: Can't get records from foxpro cursor.

(OP)

CODE

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 .

RE: Can't get records from foxpro cursor.

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

CODE -->

...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

RE: Can't get records from foxpro cursor.

(OP)
Okay.... then why can't we get records based on cBatchNo not using nPoDtlID ?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close