Topic
  • 5 replies
  • Latest Post - ‏2013-05-23T08:30:46Z by PrashanthRaoK
KJH5_Efren_Gutierrez
3 Posts

Pinned topic Problem retrieving stored procedures to a edmx file

‏2013-05-10T17:00:44Z |

Hi there

My english is not very well, i try to explain what is my problem.

I use Visual Studio 2010   with the VSAI 10.1 Fix pack 2  and data server driver package with the same version but when i try to add  a model (edmx)

I can not view all the stored procedures that i get in my database and when I update from database i get other.

I can not add  a Function Import and associate to store procedure

  • Arvind_Gupta
    Arvind_Gupta
    58 Posts

    Re: Problem retrieving stored procedures to a edmx file

    ‏2013-05-13T05:38:35Z  

    Hi,

    Which database you are connecting to and its version?

    If you could provide us step by step screen shot , it will help to understand the problem.

    Thanks

  • KJH5_Efren_Gutierrez
    3 Posts

    Re: Problem retrieving stored procedures to a edmx file

    ‏2013-05-13T16:35:18Z  

    Hi,

    Which database you are connecting to and its version?

    If you could provide us step by step screen shot , it will help to understand the problem.

    Thanks

    HI, Arvind Thanks for response.

     

    My IDS is 11.50 with drda support i have VS 2010 with VSAI 10.1 FixPack 2 there are some screen of the procedure that i do for retrieving the tables views, procedures etc...

     

    Sorry Arvind I dont know how to present the screens I sent to you an attacched file.

     

    sorry my english is not very well.

    Thanks in advanced I really appreciate your help.

     

       

     

    Attachments

  • PrashanthRaoK
    PrashanthRaoK
    40 Posts

    Re: Problem retrieving stored procedures to a edmx file

    ‏2013-05-15T06:01:34Z  

    HI, Arvind Thanks for response.

     

    My IDS is 11.50 with drda support i have VS 2010 with VSAI 10.1 FixPack 2 there are some screen of the procedure that i do for retrieving the tables views, procedures etc...

     

    Sorry Arvind I dont know how to present the screens I sent to you an attacched file.

     

    sorry my english is not very well.

    Thanks in advanced I really appreciate your help.

     

       

     

    Hi,

    If you look at the first and second screen shot i do not see the procedure "spd_delete_origen"  present in the first screen shot.

    Is the connection different for first and second screen shot ?

    It will be help full to analyze further if you can share the "spd_delete_origen" stored procedure definition.

    Thanks and Regards,

    Prashanth

  • KJH5_Efren_Gutierrez
    3 Posts

    Re: Problem retrieving stored procedures to a edmx file

    ‏2013-05-15T14:12:12Z  

    Hi,

    If you look at the first and second screen shot i do not see the procedure "spd_delete_origen"  present in the first screen shot.

    Is the connection different for first and second screen shot ?

    It will be help full to analyze further if you can share the "spd_delete_origen" stored procedure definition.

    Thanks and Regards,

    Prashanth

    Hi 

    Yes PrashanthRaok  the connection is the same when I connect in the server explorer of the vs 2010 i see all the procedures but when i try to add inside the Edmx Designer this procedures not appear and i can not select, but i have other that doesnt appear too.

    the definition of the procedure goes below, Thanks for response.

     

     

     
    create procedure "informix".spd_delete_origen(p_origen integer)
    returning integer as Rdo, varchar(255) as Descripcion
    define SQL_error_var,ISAM_error_var integer;
    define error_data_var varchar(255);
        let SQL_error_var = 0;
        let error_data_var = 'Origen Eliminado Exitosamente';
      Begin
        On Exception set SQL_error_var,ISAM_error_var,error_data_var
          let error_data_var = 'Existen elementos que hacen referencia al Origen - ' || p_origen::Varchar(12) || '; El Origen - ' || p_origen::Varchar(12) || ' no puede ser eliminado !!!';
        End Exception 
        IF EXISTS(Select cve_origen from cat_origen where cve_origen=p_origen) then
          Delete from cat_origen where cve_origen=p_origen;
        ELSE
          let SQL_error_var = -1;
          let error_data_var = 'Origen Inexistente';
        END IF  
      End;
      return SQL_error_var,error_data_var;  
    end procedure;

     

    Other procedure that doesnt appear

     

    create procedure "informix".spd_delete_bolsa(p_origen integer, p_bolsa integer)
    returning integer as Rdo, varchar(255) as Descripcion
    define SQL_error_var,ISAM_error_var integer;
    define error_data_var varchar(255);
        let SQL_error_var = 0;
        let error_data_var = 'Bolsa Eliminada Exitosamente';
      Begin
        On Exception set SQL_error_var,ISAM_error_var,error_data_var
          let error_data_var = 'Existen elementos que hacen referencia a la Bolsa - ' ||  p_bolsa::Varchar(12) || ' del Origen - ' || p_origen::Varchar(12) ||  '; La Bolsa - ' || p_bolsa::Varchar(12) || ' no puede ser eliminada !!!';
        End Exception 
        IF EXISTS(Select cve_bolsa from cat_bolsa where cve_origen=p_origen and cve_bolsa=p_bolsa) then
          Delete from cat_bolsa where cve_origen=p_origen and cve_bolsa=p_bolsa;
        ELSE
          let SQL_error_var = -1;
          let error_data_var = 'Bolsa Inexistente';
        END IF    
      End;
      return SQL_error_var,error_data_var;  
    end procedure;

     

     

     

     

    Other comments is that when i imported some procedures in the model  appear to definition

    procedurename

    procedurename1

    I dont know why

     

  • PrashanthRaoK
    PrashanthRaoK
    40 Posts

    Re: Problem retrieving stored procedures to a edmx file

    ‏2013-05-23T08:30:46Z  

    Hi 

    Yes PrashanthRaok  the connection is the same when I connect in the server explorer of the vs 2010 i see all the procedures but when i try to add inside the Edmx Designer this procedures not appear and i can not select, but i have other that doesnt appear too.

    the definition of the procedure goes below, Thanks for response.

     

     

     
    create procedure "informix".spd_delete_origen(p_origen integer)
    returning integer as Rdo, varchar(255) as Descripcion
    define SQL_error_var,ISAM_error_var integer;
    define error_data_var varchar(255);
        let SQL_error_var = 0;
        let error_data_var = 'Origen Eliminado Exitosamente';
      Begin
        On Exception set SQL_error_var,ISAM_error_var,error_data_var
          let error_data_var = 'Existen elementos que hacen referencia al Origen - ' || p_origen::Varchar(12) || '; El Origen - ' || p_origen::Varchar(12) || ' no puede ser eliminado !!!';
        End Exception 
        IF EXISTS(Select cve_origen from cat_origen where cve_origen=p_origen) then
          Delete from cat_origen where cve_origen=p_origen;
        ELSE
          let SQL_error_var = -1;
          let error_data_var = 'Origen Inexistente';
        END IF  
      End;
      return SQL_error_var,error_data_var;  
    end procedure;

     

    Other procedure that doesnt appear

     

    create procedure "informix".spd_delete_bolsa(p_origen integer, p_bolsa integer)
    returning integer as Rdo, varchar(255) as Descripcion
    define SQL_error_var,ISAM_error_var integer;
    define error_data_var varchar(255);
        let SQL_error_var = 0;
        let error_data_var = 'Bolsa Eliminada Exitosamente';
      Begin
        On Exception set SQL_error_var,ISAM_error_var,error_data_var
          let error_data_var = 'Existen elementos que hacen referencia a la Bolsa - ' ||  p_bolsa::Varchar(12) || ' del Origen - ' || p_origen::Varchar(12) ||  '; La Bolsa - ' || p_bolsa::Varchar(12) || ' no puede ser eliminada !!!';
        End Exception 
        IF EXISTS(Select cve_bolsa from cat_bolsa where cve_origen=p_origen and cve_bolsa=p_bolsa) then
          Delete from cat_bolsa where cve_origen=p_origen and cve_bolsa=p_bolsa;
        ELSE
          let SQL_error_var = -1;
          let error_data_var = 'Bolsa Inexistente';
        END IF    
      End;
      return SQL_error_var,error_data_var;  
    end procedure;

     

     

     

     

    Other comments is that when i imported some procedures in the model  appear to definition

    procedurename

    procedurename1

    I dont know why

     

    Hi,

    Looking at the stored procedure definition , it is returning more than one value.

    Informix Functions and Stored Procedures returning multiple scalar values will not be visible in the VS Entity Data Model Wizard.

    This is a known issue and is in fact in our product backlog.

    However, to expedite, please use your IBM contact to raise a product requirement to capture the business impact/value.

    Thanks and Regards,

    Prashanth.