Topic
  • 20 replies
  • Latest Post - ‏2019-04-01T16:10:12Z by Tapan_Kumar_Ghosh
imadin
imadin
3 Posts

Pinned topic Db2 stored procedure in entityframework

‏2017-03-28T09:50:42Z | entityframework

Hi,

 

i'm trying to generate a db2 stored procedure in entity framework but no complex type generated. the stored procedure does not return anything for .edmx.

CREATE PROCEDURE SIMU.GET_CRI_RACE_AMA
         
 (in tata int)
        RESULT SETS 1
        LANGUAGE SQL
        QUALIFIER SIMU
        PACKAGE OWNER SIMU

P1: BEGIN
        -- #######################################################################
        -- # Returns all tables created by DPIZ
        -- #######################################################################
        -- Declare cursor
        DECLARE cursor1 CURSOR WITH RETURN TO CLIENT for
        
                select C_CRI_RACE as "Code", LB_CRI_RACE as "Libellé" from SIMU.GBT547;

        -- Cursor left open for client application
        OPEN cursor1;
END P1

 

  • TapanKumarGhosh
    TapanKumarGhosh
    13 Posts

    Re: Db2 stored procedure in entityframework

    ‏2017-04-03T06:58:52Z  

    Hi,

    Could you please let us know the db2 version , DB2 Entity Framework version and against which database server you are using ?

    We had this issue with older DB2 client version and we fixed that issue. I have verified with latest db2 version i.e. db2_v105fp8 and able to create a complex type for the columns returned by stored procedure.

    Below are the steps to generate the complex type:

    First create the Entity Data Model for the stored procedure -> In Model Browser window -> right click on Stored procedure -> choose Add Function Import option -> In Add Function Import window ->

    First click on Get Column information button -> then we get the Column information i.e EDM type, Db Type etc. -> Then click on the Create New Complex Type button -> Then OK . It will create the complex types

    under complex Type section of the Model Browser window.

     

    This issue is resolved in latest DB2 version. If you use latest db2 client version i.e. db2_v105fp8, you will not face this issue.
    Please use the latest DB2 version and verify it. And let us know if you still see this issue.

    For the references, I am sharing the Add Function Import Window and Model browser window where you can see that complex types get generated under Complex Types section of the Model Browser window.

    Thanks,
    Tapan

     

  • DineshRana
    DineshRana
    11 Posts

    Re: Db2 stored procedure in entityframework

    ‏2018-09-06T23:52:27Z  

    Hello Guys,

    I am new to informix stuff and I have a Legacy database(IBM informix ) and there are thousands of stored procedures written. Now I am trying to create the model class using Entity framework database first approach.

    The problem I am facing now is;

    When I am trying to add stored procedures from Update Model from Database, noting is list down in the Stored Procedures and Functions. Further I have checked with the Server and found that all stored procedures are stored in sysprocedures table. 

    My questions are;

    How can I import these procedures into Entity framework?

    Is it possible to invoke stored procedure without importing?

     

    Much appreciate your help.

    Thanks and Regards.

     

    Updated on 2018-09-07T06:45:26Z at 2018-09-07T06:45:26Z by DineshRana
  • pkarnam
    pkarnam
    13 Posts

    Re: Db2 stored procedure in entityframework

    ‏2018-09-07T09:00:33Z  

    Hello Guys,

    I am new to informix stuff and I have a Legacy database(IBM informix ) and there are thousands of stored procedures written. Now I am trying to create the model class using Entity framework database first approach.

    The problem I am facing now is;

    When I am trying to add stored procedures from Update Model from Database, noting is list down in the Stored Procedures and Functions. Further I have checked with the Server and found that all stored procedures are stored in sysprocedures table. 

    My questions are;

    How can I import these procedures into Entity framework?

    Is it possible to invoke stored procedure without importing?

     

    Much appreciate your help.

    Thanks and Regards.

     

    Hi Dinesh,

     

    Can you please share the following details :

     

    a) What is the version of Db2 client you are using ?

    b) What is the server to which you are connecting to ? is it Db2 for z/OS or Db2 for LUW or db2 for IBM i ?


    c) Did you try importing the stored procedures while creating the model instead of importing while updating the model ?

     

    Thanks and Regards,

    Prashanth Karnam.

     

  • DineshRana
    DineshRana
    11 Posts

    Re: Db2 stored procedure in entityframework

    ‏2018-09-09T23:17:50Z  
    • pkarnam
    • ‏2018-09-07T09:00:33Z

    Hi Dinesh,

     

    Can you please share the following details :

     

    a) What is the version of Db2 client you are using ?

    b) What is the server to which you are connecting to ? is it Db2 for z/OS or Db2 for LUW or db2 for IBM i ?


    c) Did you try importing the stored procedures while creating the model instead of importing while updating the model ?

     

    Thanks and Regards,

    Prashanth Karnam.

     

    Hi Prashanth,

     

    Thanks for responding to the thread. 

    a) I am using IBM Data Sever Client Package v11.1 . https://www-01.ibm.com/support/docview.wss?uid=ibm10720011. Along with IBM Database Add-Ins for Visual Studio.  The reference in the project are IBM.Data.DB2 and IBM.Data.DB2.EntityFramework 

    b) It is Db2 for Linux, UNIX and Windows

    c) I tried to importing the stored procedures while creating the model, but non of them are listing in the database node I am pointing to. Actually all of the stored procedures are stored in the sysprocedures table. But I can not see any table when I am creating the model.

     

    Much appreciate your help.

     

    Thanks and Regards.

  • pkarnam
    pkarnam
    13 Posts

    Re: Db2 stored procedure in entityframework

    ‏2018-09-10T09:10:12Z  

    Hi Prashanth,

     

    Thanks for responding to the thread. 

    a) I am using IBM Data Sever Client Package v11.1 . https://www-01.ibm.com/support/docview.wss?uid=ibm10720011. Along with IBM Database Add-Ins for Visual Studio.  The reference in the project are IBM.Data.DB2 and IBM.Data.DB2.EntityFramework 

    b) It is Db2 for Linux, UNIX and Windows

    c) I tried to importing the stored procedures while creating the model, but non of them are listing in the database node I am pointing to. Actually all of the stored procedures are stored in the sysprocedures table. But I can not see any table when I am creating the model.

     

    Much appreciate your help.

     

    Thanks and Regards.

    Hi Dinesh,

     

    While i continue my investigation can you please provide the following details :

     

    1) What is the visual studio version you are using ?

     

    2) Can you please provide me the db2level.exe command output. The exe is present in the product_installed_location\bin .

     

    3) Db2 for LUW  server version.

     

    Thanks,
    Prashanth Karnam.

  • DineshRana
    DineshRana
    11 Posts

    Re: Db2 stored procedure in entityframework

    ‏2018-09-11T00:06:23Z  
    • pkarnam
    • ‏2018-09-10T09:10:12Z

    Hi Dinesh,

     

    While i continue my investigation can you please provide the following details :

     

    1) What is the visual studio version you are using ?

     

    2) Can you please provide me the db2level.exe command output. The exe is present in the product_installed_location\bin .

     

    3) Db2 for LUW  server version.

     

    Thanks,
    Prashanth Karnam.

    Hi Prashanth,

     

    Please find the below details for you reference.

    1) Visual Studio Professional 2015

     

    2) DB21085I  This instance or install (instance name, where applicable: "*") uses
        "64" bits and DB2 code release "SQL11013" with level identifier "0204010F".
         Informational tokens are "DB2 v11.1.3031.368", "s1807091300",
        "DYN1807091300WIN64", and Fix Pack "3b".
         Product is installed at "C:\PROGRA~1\IBM\IBMDAT~1" with DB2 Copy Name "IBMDBCL1".

     

    3) Sorry for misleading you. Actually The database engine is Informix V11.70. and The OS is SuSE Linux Enterprise Server V11.

     

    Thanks and Regards.

  • pkarnam
    pkarnam
    13 Posts

    Re: Db2 stored procedure in entityframework

    ‏2018-09-11T10:51:12Z  

    Hi Prashanth,

     

    Please find the below details for you reference.

    1) Visual Studio Professional 2015

     

    2) DB21085I  This instance or install (instance name, where applicable: "*") uses
        "64" bits and DB2 code release "SQL11013" with level identifier "0204010F".
         Informational tokens are "DB2 v11.1.3031.368", "s1807091300",
        "DYN1807091300WIN64", and Fix Pack "3b".
         Product is installed at "C:\PROGRA~1\IBM\IBMDAT~1" with DB2 Copy Name "IBMDBCL1".

     

    3) Sorry for misleading you. Actually The database engine is Informix V11.70. and The OS is SuSE Linux Enterprise Server V11.

     

    Thanks and Regards.

    Hi Dinesh,

     

    We are able to see the procedures in our local environment.

     

    Can you please check if the user with which the model creation is happening has the appropriate rights at the database to query or see the procedures.

     

    Also is the schema under which the stored procedure is created is the same schema name used while creating the model from the database ?

     

    What is the update version of Visual Studio ?, We have tested with visual studio 2015 update 3.

     

    Please provide a screen shot  of the error you get.

     

    Thanks and Regards,

    Prashanth Karnam.

    Updated on 2018-09-11T10:51:33Z at 2018-09-11T10:51:33Z by pkarnam
  • DineshRana
    DineshRana
    11 Posts

    Re: Db2 stored procedure in entityframework

    ‏2018-09-12T03:59:06Z  
    • pkarnam
    • ‏2018-09-11T10:51:12Z

    Hi Dinesh,

     

    We are able to see the procedures in our local environment.

     

    Can you please check if the user with which the model creation is happening has the appropriate rights at the database to query or see the procedures.

     

    Also is the schema under which the stored procedure is created is the same schema name used while creating the model from the database ?

     

    What is the update version of Visual Studio ?, We have tested with visual studio 2015 update 3.

     

    Please provide a screen shot  of the error you get.

     

    Thanks and Regards,

    Prashanth Karnam.

    Hi Prashanth,

     

    To illustrate further I created a simple table and a SP.

     

    Table 

     

    SP

     

    - Create the model from database first approach using entity framework 

    Pressed finish button. And got an error saying 

     

    Note: I am using the same update -> visual studio 2015 update 3

     

    How can I overcome with this issue?

     

    Thanks and Regards.

     

     

     

     

  • pkarnam
    pkarnam
    13 Posts

    Re: Db2 stored procedure in entityframework

    ‏2018-09-12T10:00:22Z  

    Hi Prashanth,

     

    To illustrate further I created a simple table and a SP.

     

    Table 

     

    SP

     

    - Create the model from database first approach using entity framework 

    Pressed finish button. And got an error saying 

     

    Note: I am using the same update -> visual studio 2015 update 3

     

    How can I overcome with this issue?

     

    Thanks and Regards.

     

     

     

     

    Hi ,

     

    Can you please share the definition of stored procedure and table ?

    I will try to repro it in my local environment.

     

    Thanks and Regards,

    Prashanth Karnam.

     

  • DineshRana
    DineshRana
    11 Posts

    Re: Db2 stored procedure in entityframework

    ‏2018-09-12T23:32:06Z  
    • pkarnam
    • ‏2018-09-12T10:00:22Z

    Hi ,

     

    Can you please share the definition of stored procedure and table ?

    I will try to repro it in my local environment.

     

    Thanks and Regards,

    Prashanth Karnam.

     

    Hi Prashanth,

    Please find the attached file.

    FYI,

     

     

    Thanks and Regards.

    Attachments

    Updated on 2018-09-13T00:17:57Z at 2018-09-13T00:17:57Z by DineshRana
  • DineshRana
    DineshRana
    11 Posts

    Re: Db2 stored procedure in entityframework

    ‏2018-09-14T01:46:51Z  

    Hi Prashanth,

    Please find the attached file.

    FYI,

     

     

    Thanks and Regards.

    Hi @pkarnam Any luck?

  • DineshRana
    DineshRana
    11 Posts

    Re: Db2 stored procedure in entityframework

    ‏2018-09-17T04:40:59Z  

    Hi Prashanth,

     

    Were you able to reproduce the issue with the given table and SP?

     

    Thanks and Regards.

  • pkarnam
    pkarnam
    13 Posts

    Re: Db2 stored procedure in entityframework

    ‏2018-09-17T09:18:28Z  

    Hi Prashanth,

     

    Were you able to reproduce the issue with the given table and SP?

     

    Thanks and Regards.

    Hi Dinesh,

     

    I am working on it. I will reply once i have an update.

     

    Thanks and Regards,

    Prashanth Karnam.

  • DineshRana
    DineshRana
    11 Posts

    Re: Db2 stored procedure in entityframework

    ‏2018-09-17T11:43:46Z  
    • pkarnam
    • ‏2018-09-17T09:18:28Z

    Hi Dinesh,

     

    I am working on it. I will reply once i have an update.

     

    Thanks and Regards,

    Prashanth Karnam.

    Thanks Prashanth. Much appreciated. 

  • DineshRana
    DineshRana
    11 Posts

    Re: Db2 stored procedure in entityframework

    ‏2018-09-24T00:14:39Z  

    Hi Prashanth,

     

    Any update on this?

     

    Thanks and Regards.

  • pkarnam
    pkarnam
    13 Posts

    Re: Db2 stored procedure in entityframework

    ‏2018-09-25T09:25:02Z  

    Hi Prashanth,

     

    Any update on this?

     

    Thanks and Regards.

    Hi Dinesh,

     

    We are able to recreate the problem. Seems like a bug.

    It will be fixed in next release.

     

    Thanks,

    Prashanth Karnam.

  • DineshRana
    DineshRana
    11 Posts

    Re: Db2 stored procedure in entityframework

    ‏2018-09-25T09:35:36Z  
    • pkarnam
    • ‏2018-09-25T09:25:02Z

    Hi Dinesh,

     

    We are able to recreate the problem. Seems like a bug.

    It will be fixed in next release.

     

    Thanks,

    Prashanth Karnam.

    Hi Prashanth,

     

    Really appreciate your input. When will be the next release?

    Thanks and Regards. 

  • pkarnam
    pkarnam
    13 Posts

    Re: Db2 stored procedure in entityframework

    ‏2018-09-26T10:56:21Z  

    Hi Prashanth,

     

    Really appreciate your input. When will be the next release?

    Thanks and Regards. 

    Hi Dinesh,

     

    We do not have confirmed date. I will let you once it is finalized.

     

    Thanks,

    Prashanth Karnam,

  • DineshRana
    DineshRana
    11 Posts

    Re: Db2 stored procedure in entityframework

    ‏2018-09-28T01:44:36Z  
    • pkarnam
    • ‏2018-09-26T10:56:21Z

    Hi Dinesh,

     

    We do not have confirmed date. I will let you once it is finalized.

     

    Thanks,

    Prashanth Karnam,

    Thank you Prashanth.

     

    Regards.

     

  • Tapan_Kumar_Ghosh
    Tapan_Kumar_Ghosh
    3 Posts

    Re: Db2 stored procedure in entityframework

    ‏2019-04-01T16:10:12Z  

    Hi Prashanth,

     

    To illustrate further I created a simple table and a SP.

     

    Table 

     

    SP

     

    - Create the model from database first approach using entity framework 

    Pressed finish button. And got an error saying 

     

    Note: I am using the same update -> visual studio 2015 update 3

     

    How can I overcome with this issue?

     

    Thanks and Regards.

     

     

     

     

    Hi Dinesh,


    While creating Entity Data Model (EDM) (for the selected function i.e. ar_get_contact_name) by following Entity Data Model wizard, just deselect "Import selected stored procedures and functions into the entity model" option in below EDM wizard window and click 'Finish.

     

    Then you will be able to see that function entry in "Stored Procedures/Functions" section of the Model Browser window.

    Thanks,

    Tapan

     

     

    Updated on 2019-04-01T16:19:07Z at 2019-04-01T16:19:07Z by Tapan_Kumar_Ghosh