Retrieve Result Shape for Procedures with Specific Values

[Jack]

Currently I am trying to integrate a third party database with Data Access, and I am unable to use the Retrieve Result Shape command to generate a complex type from the procedure because the values being passed in are NULL.

Looking at the MySQL packets in wireshark shows:
Statement: CALL `my_database`.`my_procedure`(NULL, NULL, NULL)
Resulting in:
Error message: Invalid date value(s).

I have no way to modify the stored procedure. Is there any way to cause Data Access to send an actual date instead of NULL?

I have been using ORM for a long time now, and it seems like this is an archaic method of processing a procedure. It would be nice if instead of a single command of Retrieve Result Shape there was first an option to Retrieve Procedure Parameters. The values filled in could then be used to retrieve the result shape.

[Doroteya]

Hello Jack,

I am sorry for the inconvenience you experience.

In general, your query you observe is the executed one and it is part of the schema retrieval process used by Telerik Data Access, and for stored procedures it is executed in schema-read mode. In other words, when such a query is executed like this, the procedure will not actually use the values and return a result. Rather than that, it will only return its result shape. 

Regarding the generation of the domain method on your side, could you share the following information:
1. Does Domain Method Editor notify you about an "Unable to retrieve the result shape" error when you attempt to retrieve the result shape?
2. Do the stored procedures in the database utilize temporary tables?

Thank you in advance for your cooperation. I am looking forward to your feedback.


Regards,
Doroteya
Telerik

 

 

[Jack]

Yes, the error message on the Domain Method Editor is "Unable to retrieve the result shape." This is what led me to try to find out what was executing against the database.

I do not have schema access to the mysql database. I only have the option to run procedures. If I provide a valid date value, the procedure returns a result set. If the date is set to null, it returns an error. I'm thinking they have some sort of if statement checking the date and returning an error if it's not valid.

[Kaloyan Nikolov]

Hello Jack,

Usually such validations, temporary tables and some other factors can prevent the functions to be executed in schema-only mode and thus the result set cannot be retrieved. 

As a workaround I can suggest you to create a procedure with the same result set and the same parameters on a local server where you have the required access rights, map it with the wizard and copy the generated code in a partial class of your context. The code will be exactly the same with the one which would be generated anyway. 

I hope this helps. Please excuse us for the inconvenience caused. 


Regards,
Kaloyan Nikolov
Telerik