Cheching a V12.0.1 GA reload file, I've stumbled over the following definition for a proxy procedure:

:::SQL
create procedure dbo.FSTP_MyProc(in nNr integer, in strStr char(20)) 
result (Col1 integer, Col2 integer) dynamic result sets 1
at 'MSSQL.MyDb.dbo.stp_myproc'

The reload file used to migrate this database from V11.0.1 to V12.0.1 does not have the "dynamic result sets 1" clause, nor does the original definition (as this is an undocumented clause, AFAIK).

The proc does indeed return one result set - but how does the local database know about that?

asked 21 Mar '11, 13:20

Volker%20Barth's gravatar image

Volker Barth
30.9k311457668
accept rate: 32%


This clause is meaningless in all instances except in the case of external environment calls. For example, if you are using the JAVA external environment, and if your JAVA signature has "[Ljava/sql/ResultSet;" as the last argument, then the server needs to know ahead of time exactly how many result sets will be returned in the ResultSet array. Using something like "dynamic result sets 3" tells the server that this particular JAVA external environment call will return exactly 3 result sets. The reason the server needs to know in the case of external environments is due to the fact that the "server" pre-allocates the ResultSet array prior to calling the user method.

For remote procedure calls, the server is perfectly capable of handling dynamic multiple result sets coming back from the remote server without needing to know ahead of time how many result sets are actually going to come back. Hence, dbunload is incorrect in adding the dynamic result set clause for remote procedure definitions; but as I indicated above, there is no problem in such cases since the clause is happily ignored for all instances except external environment procedures. I will still mark this problem as a dbunload bug and will get the issue resolved. I will also have a chat with the documentation group to see if we can make the documentation a bit more clear regarding the dynamic result set clause.

permanent link

answered 21 Mar '11, 15:06

Karim%20Khamis's gravatar image

Karim Khamis
5.6k53870
accept rate: 40%

@All: Thanks for the clarification. - I guess the obviously unwanted behaviour has been introduced with 12.0.1 - I have just noticed that a previous migration test with 12.0.0.2601 had not added this particular clause.

(22 Mar '11, 04:19) Volker Barth
Replies hidden
2

@Volker: Yes this unwanted/incorrect dbunload behaviour is actually a side effect of the bug fix for issue #638982. I am not sure at this time whether the fix for this new unexpected dbunload behaviour will go into the server or dbunload, but my guess is that both will need to be changed. I will update this comment with the issue number for the new bug once the new issue number is available.

(22 Mar '11, 07:39) Karim Khamis
1

This problem has now been fixed as case #667316.

(21 Apr '11, 15:04) Karim Khamis
Replies hidden

... and will be fixed in 12.0.1 build 3336 or higher (for anyone that is wondering! :-)

(21 Apr '11, 15:39) Mark Culp

This clause is documented as part of the CREATE PROCEDURE statement for external procedures. For a detailed explanation, please see 12.0.1 documentation or 12.0.0 documentation.

permanent link

answered 21 Mar '11, 13:28

Elmi%20Eflov's gravatar image

Elmi Eflov
7811014
accept rate: 31%

Volker's example is creating an RPC call which goes through OMNI to MySQL and therefore is not using the external procedure syntax but rather the syntax documented here. I guess the documentation for this clause needs to be added to this other page as well?

(21 Mar '11, 13:42) Mark Culp
Replies hidden

Thanks for the pointer. - I just hadn't looked for external procedures, and the description of that particular clause for 12.0.1 seems not really refer to proxy procedures (which are documented unter the normal CREATE PROCEDURE statement) but I guess the usage is understandable - particularly since "DYNAMIC RESULT SETS 1" is the default for procs with a RESULT clause.

(21 Mar '11, 13:46) Volker Barth
Replies hidden

But how does the server know? Does he rely on at least one procedure invocation to add this clause?

I'm not sure whether that proc was called between the migration of that database and the (rather immediate) reload to compare structure and data with the old database...

(21 Mar '11, 13:49) Volker Barth

If the documentation for this clause applies to proxy procedures, it is relying on the presence of the RESULT clause to decide that there is a single result set returned.

(21 Mar '11, 13:56) Elmi Eflov
1

Turns out that the clause does not apply to proxy procedures - see Karim's answer.

(21 Mar '11, 15:14) Mark Culp

FWIW, it's MS SQL Server...

(22 Mar '11, 04:13) Volker Barth
showing 2 of 6 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×412
×106

question asked: 21 Mar '11, 13:20

question was seen: 3,724 times

last updated: 21 Apr '11, 15:39