Handle Code when SQLEXEC() Returns more than 1 Result Set 2

[carolx]

Hello Forum:

I want to convert oGv.pnBurialPlotId = 'transtab'+ALLTRIM(STR(lnReturn-1))+'.'+'burialplotid' to the format
oGv.pnBurialPlotId = transtab.burialplotid. How is it done? The code I have doesn't work. I have tried it several ways.
The result I get is in quotes: 'transtab1.burialplotid'

lnReturn = SQLEXEC(oGv.pnConnhandle, 'EXEC MCRevenue.Sundries.TentativeTransferGravePlotSP ?oGv.pnBurialPlotId,?@lnOutput','transtab')
IF lnReturn > 0
IF lnReturn > 1
SELECT 'transtab'+ALLTRIM(STR(lnReturn-1))
oGv.pnBurialPlotId = EVALUATE('transtab'+ALLTRIM(STR(lnReturn-1))+'.'+'burialplotid')
oGv.pnBurialPlotId = EVALUATE('transtab'+ALLTRIM(STR(lnReturn-1))+'.'+'transferdate')
ELSE
SELECT transtab
oGv.pnBurialPlotId = transtab.burialplotid
oGv.pdTransferDate = transtab.transferdate
ENDIF

 

[GriffMG]

I'm sorry, your question isn't very clear, I've dropped your code into a block using the [][/] notation can you explain
bit more...

LNRETURN = SQLEXEC(OGV.PNCONNHANDLE, 'EXEC MCRevenue.Sundries.TentativeTransferGravePlotSP ?oGv.pnBurialPlotId,?@lnOutput','transtab')
IF LNRETURN > 0
	IF LNRETURN > 1
		SELECT 'transtab'+ALLTRIM(STR(LNRETURN-1))
		OGV.PNBURIALPLOTID = EVALUATE('transtab'+ALLTRIM(STR(LNRETURN-1))+'.'+'burialplotid')
		OGV.PNBURIALPLOTID = EVALUATE('transtab'+ALLTRIM(STR(LNRETURN-1))+'.'+'transferdate')
	ELSE
		SELECT TRANSTAB
		OGV.PNBURIALPLOTID = TRANSTAB.BURIALPLOTID
		OGV.PDTRANSFERDATE = TRANSTAB.TRANSFERDATE
	ENDIF

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

Also it appears to be missing an ENDIF.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Let me try to guess what you are asking.

You have a stored procedure named TentativeTransferGravePlotSP which returns or more result sets. The result sets contain two common fields: TRANSFERDATE and BURIALPLOTID. You want to take the values of those two fields from the highest-numbered result set, and store them in a pair of properties of an object named OGV.

If that is correct, your first line of attack should be to modify the stored procedure so that it only returns a single result set containing just the two items you want.

But if that is not possible for some reason, you could try something like this:

LNRETURN = SQLEXEC(OGV.PNCONNHANDLE, ... etc ... )
IF LNRETURN > 0

  * Move to the highest used work area
  SELECT (SELECT(0) - 1)
  
  * Extract the required data
  OGV.PNBURIALPLOTID = BURIALPLOTID
  OGV.PDTRANSFERDATE = TRANSFERDATE

ENDIF

Note that I haven't tested this. It is based on the assumption that the cursors returned from SQLEXEC() are placed in consecutive work areas, in the order in which they are returned. You should check that for yourself.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

When SQLExec returns 2 results, the cursors aliases will be transtab1 and transtab2. Unless those are already used.

You could make use of the 5th parameteer aCountInfo to give you an array that would contain the alias names and count of records.

The code as is assumes the essential information is in the seccond to last result cursor. Without knwogint the code of th stored procedure it's impossible to tell if that's the case. But as you have problems the way it is, it's likely not that way.

Inspect the two cursors you get back. I'd assume as the stored proc returns two results they will differ, maybe one of them has the one field and one of them theother field. I second Mike it would make things much easier if that would be two fields of a single result, but that may be the nature of a stored procedure that's not designed by you.

So in short again, usage of the 5th parameter could be like this:

lnReturn = SQLExec(oGv.pnConnhandle, 'EXEC MCRevenue.Sundries.TentativeTransferGravePlotSP ?oGv.pnBurialPlotId,?@lnOutput','transtab',laAliases)
If lnReturn>0
	If lnReturn > 1
		For lnI = 1 To Alen(laAliases,1))
			Select (laAliases[lnI])
			If !Empty(Field('burialplotid'))
				oGv.PNBURIALPLOTID = burialplotid
			Endif
			If !Empty(Field('transferdate'))
				oGv.PDTRANSFERDATE = transferdate
			Endif
		Endfor lnI
	Else
		Select TRANSTAB
		oGv.PNBURIALPLOTID = TRANSTAB.burialplotid
		oGv.PDTRANSFERDATE = TRANSTAB.transferdate
	Endif
Endif

This is based on the guess that the fields will be in one of the result cursors, not knowing which one. If both results contain both fields, the values of the first alias will be overwritten with the values of the second alias. So if that's the case, you should really inspect what's in the rresult manually, at least once, to find out which alias has the relevant information. The code could then be tailored to fit that knowledge.

And just the code of the stored proc would likely also not tell much, if we don't know the database structure, so I guess you're left on your own ttesting of this. Get hands on that server, if you program remotely for a customer, that should be doable.

Chriss

 

[Mike Lewis]

When SQLExec returns 2 results, the cursors aliases will be transtab1 and transtab2.

I'm not sure that's correct. I think the first one will be transtab and the second will be transtab1.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Mike,

For multiple result sets, new cursor names are derived by appending an incremented number to the name of the first cursor.

Yes, transtab and transtab1 cover that pattern. It's a bit vague. If you did this once and didn't close transtab and transtab1 I guess the returned aliases will thenbe transtab2 and transtab3. Any way the names are, they will be put into the array you can get from the 5th parameter aCountinfo. And that'll take care of however the alias names are.



Chriss

 

[Mike Lewis]

I agree, Chris. In any case, it doesn't invalidate the code that you posted.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

There still are some catches when both result aliases have data about those two fields and properties of oGV. Only Carol can know which of them is correct.

According to the help you might have empty or no alias in the array, but I guess that only happens in the case the direct return value of SQLExec() is 0 or 1.

I also wonder whether this all is based on a misunderstanding of what multiple results are. If Carol would be very new to SQL she might not know that 2 results would mean 2 resultsets not 2 records. 2 records still are just one resultset and it's very normal if a resultset has many records. SQLExec does not return a record count, it returns the cout of reults aka resultsets and each set can have multiple rows. My code also won't go through all records of the results, if there are more than one. So it all depends on very many things we don't know about the nature of the stored procedure.

Chriss

 

[carolx]

The stored procedure returned two result sets: an empty cursor and a cursor with one record and two fields: burialplotid and transferdate.

I tried Mike's method but it didn't work. I also used Chris code and got 'table number is invalid'. What I did eventually was to set step and display the data session. The empty cursor is named transtab and the other cursor transtab1. I plugged transtab1 in the code and it worked. But I would prefer to do it using code only(without have to check the data session).

I am kind of pressed for time so I didn't experiment.

The empty cursor is created because of the following code I think:

BEGIN TRANSACTION
SELECT * FROM Burial.TransferGravePlot (HOLDLOCK) WHERE burialplotid = @BurialPlotId

 

[Mike Lewis]

Carol,

If the procedure always returns exactly two cursors, then you don't need to display the data session. With your original code, the cursors will always be named Transtab and Transtab1. So you could just plug those names into your original code.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Carol, in which line do you get this error?

The array created is loopedfrom 1 to its length, there should not be a wrong table number, the array contains aliases, not workarea numbers.
I spot one bracket error, it should be Alen(laAliases,1), but that should already error when compiling, so I guess you spotted it already.

Did you try all my code or just the part after the SQLEXEC. Notice the important change is to ask SQLEXEC to create the laAliases array.

Chriss

 

[Chris Miller]

SELECT * FROM Burial.TransferGravePlot (HOLDLOCK) WHERE burialplotid = @BurialPlotId

Well, that creates a cursor, the reason you have two and not one result must be that you have two queries. This one based on a parameter might have an empty result because your parameter value ?oGv.pnBurialPlotId isn't found.

The most important part of the stored proc would be the parameters it receives and defaults it may use and then, well, the whole procedure. But it doesn't matter much as we know you get two results and one contains the data you need, the other not. If a result is empty, it's still a result, so you have 2 results and aAliases should have 2 aliases.

And Mike, if you execute this while transtab and transtab1 already are used, are you sure they weill be overwritten/reused or will SQLEXEC result in transtab2 and transtab3? Getting the array of aliases will make sure you access exactly the result workareas coming from this SQLExec call.

Carol, please take your time, if you overlook something you waste more time than you spare by asking for help.

Chriss

 

[carolx]

Chris I tried it again.
laAliases[lnI] -> transtab (lnI = 1)
laAliases[lnI] -> 0 (lnI = 2)
laAliases[lnI] -> transtab1 (lnI = 3) but it is outside of the loop

 

[carolx]

This is a part of the stored procedure:

@BurialPlotId INT,
@Returnvar INT OUTPUT

BEGIN TRANSACTION
SELECT * FROM Burial.TransferGravePlot (HOLDLOCK) WHERE burialplotid = @BurialPlotId
IF NOT EXISTS(SELECT * FROM Burial.TransferGravePlot WHERE burialplotid = @BurialPlotId)
BEGIN
SET @Returnvar = 9
SELECT TOP 1 burialplotid,transferdate FROM Burial.TransferGravePlot ORDER BY transferdate
END;
ELSE
BEGIN
SELECT TOP 1 burialplotid,transferdate FROM Burial.TransferGravePlot WHERE burialplotid = @BurialPlotId ORDER BY transferdate DESC
END;
COMMIT TRANSACTION

 

[Chris Miller]

Okay, then you need to use laAliases[lnI,1]

Chriss

 

[Chris Miller]

And check Reccount(laAliases[lnI,1])>0, becuase no record means the result is irrelevant, no matter if it has the fields or not.

Chriss

 

[Mike Lewis]

Carol,

Your stored procedure code indicates that you will always get exactly two cursors returned. In that case, my previous advice holds good: the cursors will always be named Transtab and Transtab1. So you can plug those names into your VFP code.

That said, it is possible that one or both of the cursors will be empty, but you can test for that with RECCOUNT().

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads