Topic
  • 3 replies
  • Latest Post - ‏2004-02-13T02:33:19Z by SystemAdmin
SystemAdmin
SystemAdmin
17917 Posts

Pinned topic VB.NET OLE Stored Procedures

‏2003-12-31T21:16:33Z |
I am running DB2 v7.2.9 (DB2 7.2 FP 11) and have created a VB Class in
VB.NET. This class contains several UDF's and a stored procedure. DB2 has
no problem calling the UDF's, but for some reason will not call the stored
procedure. It simply returns the argument I passed with a return code of 0.

However, when I take the same code and backport it to VB 6, the stored
procedure call works fine.

Any ideas?

Thanks in advance.

Updated on 2004-02-13T02:33:19Z at 2004-02-13T02:33:19Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: VB.NET OLE Stored Procedures

    ‏2004-01-05T14:48:54Z  
    I think we would need some clarification before attempting to answer. You
    say that you built a VB class in VB.NET and this class contains several UDFs
    and SPs. Does this mean that your class provides an implementation of the
    logic of these UDFs and SPs or is this class simply calling SQL statements
    that contain UDF calls and calls to SPs?
    If your VB.NET class truly implements UDFs and SPs then what you are
    attempting to do is develop .NET managed code and have DB2 engine execute
    this code as UDFs and SPs. This is not a function that works in DB2 UDB
    V7.2. Regular VB6 (not .NET) would work because it generates plain old
    unmanaged code and DB2 knows how to run this.
    The good news is that ability to build server objects (stored procedures and
    UDFs) in managed code is a feature in the upcoming DB2 "Stinger" release.
    You can get a Technology Preview that does allow you to build .NET managed
    code (using VB.NET or C#) stored procedures. You can get the DB2 "Stinger"
    Technology Preview here
    http://www-106.ibm.com/developerworks/db2/downloads/dotnetbeta/.

    "David Sarge" <etpcorp@yahoo.com> wrote in message
    news:bsvebj$8cgg$1@news.boulder.ibm.com...
    > I am running DB2 v7.2.9 (DB2 7.2 FP 11) and have created a VB Class in
    > VB.NET. This class contains several UDF's and a stored procedure. DB2
    has
    > no problem calling the UDF's, but for some reason will not call the stored
    > procedure. It simply returns the argument I passed with a return code of
    0.
    >
    > However, when I take the same code and backport it to VB 6, the stored
    > procedure call works fine.
    >
    > Any ideas?
    >
    > Thanks in advance.
    >
    >

  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: VB.NET OLE Stored Procedures

    ‏2004-01-07T04:27:00Z  
    I think we would need some clarification before attempting to answer. You
    say that you built a VB class in VB.NET and this class contains several UDFs
    and SPs. Does this mean that your class provides an implementation of the
    logic of these UDFs and SPs or is this class simply calling SQL statements
    that contain UDF calls and calls to SPs?
    If your VB.NET class truly implements UDFs and SPs then what you are
    attempting to do is develop .NET managed code and have DB2 engine execute
    this code as UDFs and SPs. This is not a function that works in DB2 UDB
    V7.2. Regular VB6 (not .NET) would work because it generates plain old
    unmanaged code and DB2 knows how to run this.
    The good news is that ability to build server objects (stored procedures and
    UDFs) in managed code is a feature in the upcoming DB2 "Stinger" release.
    You can get a Technology Preview that does allow you to build .NET managed
    code (using VB.NET or C#) stored procedures. You can get the DB2 "Stinger"
    Technology Preview here
    http://www-106.ibm.com/developerworks/db2/downloads/dotnetbeta/.

    "David Sarge" <etpcorp@yahoo.com> wrote in message
    news:bsvebj$8cgg$1@news.boulder.ibm.com...
    > I am running DB2 v7.2.9 (DB2 7.2 FP 11) and have created a VB Class in
    > VB.NET. This class contains several UDF's and a stored procedure. DB2
    has
    > no problem calling the UDF's, but for some reason will not call the stored
    > procedure. It simply returns the argument I passed with a return code of
    0.
    >
    > However, when I take the same code and backport it to VB 6, the stored
    > procedure call works fine.
    >
    > Any ideas?
    >
    > Thanks in advance.
    >
    >

    Leon,

    There is no SQL in these UDF's. These are UDF's that perform Active
    Directory queries and return a "table" as a result. Instead of our
    developer's having to learn .NET directory-services classes, they can simply
    execute these UDF's via SQL.

    I also created a UDF to allow me to add a user to a domain. Originally,
    this was coded as an OLE Stored Procedure in .NET. Since this is not
    working from DB2 to .NET, I will continue to use UDF's. (VB6 works fine,
    but we are a .NET shop. In addition, the Active Directory API's available
    to VB6 are not thread safe, but the directory services classes in .NET are.)
    We will not be migrating to v81x until next year, since there are so many
    patches required on our OS/390 side (v711) to make it play well w/81x (and
    the reverse).

    Thanks for the reply.

    David
    David Sarge
    State of Georgia, Department of Revenue
    DB2 Certified DBA
    Oracle Certified DBA
    "Leon Katsnelson" <leon.nospam@ca.ibm.com> wrote in message
    news:btbtkn$3cd8$1@news.boulder.ibm.com...
    > I think we would need some clarification before attempting to answer. You
    > say that you built a VB class in VB.NET and this class contains several
    UDFs
    > and SPs. Does this mean that your class provides an implementation of the
    > logic of these UDFs and SPs or is this class simply calling SQL statements
    > that contain UDF calls and calls to SPs?
    > If your VB.NET class truly implements UDFs and SPs then what you are
    > attempting to do is develop .NET managed code and have DB2 engine execute
    > this code as UDFs and SPs. This is not a function that works in DB2 UDB
    > V7.2. Regular VB6 (not .NET) would work because it generates plain old
    > unmanaged code and DB2 knows how to run this.
    > The good news is that ability to build server objects (stored procedures
    and
    > UDFs) in managed code is a feature in the upcoming DB2 "Stinger" release.
    > You can get a Technology Preview that does allow you to build .NET managed
    > code (using VB.NET or C#) stored procedures. You can get the DB2 "Stinger"
    > Technology Preview here
    > http://www-106.ibm.com/developerworks/db2/downloads/dotnetbeta/.
    >
    > "David Sarge" <etpcorp@yahoo.com> wrote in message
    > news:bsvebj$8cgg$1@news.boulder.ibm.com...
    > > I am running DB2 v7.2.9 (DB2 7.2 FP 11) and have created a VB Class in
    > > VB.NET. This class contains several UDF's and a stored procedure. DB2
    > has
    > > no problem calling the UDF's, but for some reason will not call the
    stored
    > > procedure. It simply returns the argument I passed with a return code
    of
    > 0.
    > >
    > > However, when I take the same code and backport it to VB 6, the stored
    > > procedure call works fine.
    > >
    > > Any ideas?
    > >
    > > Thanks in advance.
    > >
    > >
    >
    >

  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: VB.NET OLE Stored Procedures

    ‏2004-02-13T02:33:19Z  
    Leon,

    There is no SQL in these UDF's. These are UDF's that perform Active
    Directory queries and return a "table" as a result. Instead of our
    developer's having to learn .NET directory-services classes, they can simply
    execute these UDF's via SQL.

    I also created a UDF to allow me to add a user to a domain. Originally,
    this was coded as an OLE Stored Procedure in .NET. Since this is not
    working from DB2 to .NET, I will continue to use UDF's. (VB6 works fine,
    but we are a .NET shop. In addition, the Active Directory API's available
    to VB6 are not thread safe, but the directory services classes in .NET are.)
    We will not be migrating to v81x until next year, since there are so many
    patches required on our OS/390 side (v711) to make it play well w/81x (and
    the reverse).

    Thanks for the reply.

    David
    David Sarge
    State of Georgia, Department of Revenue
    DB2 Certified DBA
    Oracle Certified DBA
    "Leon Katsnelson" <leon.nospam@ca.ibm.com> wrote in message
    news:btbtkn$3cd8$1@news.boulder.ibm.com...
    > I think we would need some clarification before attempting to answer. You
    > say that you built a VB class in VB.NET and this class contains several
    UDFs
    > and SPs. Does this mean that your class provides an implementation of the
    > logic of these UDFs and SPs or is this class simply calling SQL statements
    > that contain UDF calls and calls to SPs?
    > If your VB.NET class truly implements UDFs and SPs then what you are
    > attempting to do is develop .NET managed code and have DB2 engine execute
    > this code as UDFs and SPs. This is not a function that works in DB2 UDB
    > V7.2. Regular VB6 (not .NET) would work because it generates plain old
    > unmanaged code and DB2 knows how to run this.
    > The good news is that ability to build server objects (stored procedures
    and
    > UDFs) in managed code is a feature in the upcoming DB2 "Stinger" release.
    > You can get a Technology Preview that does allow you to build .NET managed
    > code (using VB.NET or C#) stored procedures. You can get the DB2 "Stinger"
    > Technology Preview here
    > http://www-106.ibm.com/developerworks/db2/downloads/dotnetbeta/.
    >
    > "David Sarge" <etpcorp@yahoo.com> wrote in message
    > news:bsvebj$8cgg$1@news.boulder.ibm.com...
    > > I am running DB2 v7.2.9 (DB2 7.2 FP 11) and have created a VB Class in
    > > VB.NET. This class contains several UDF's and a stored procedure. DB2
    > has
    > > no problem calling the UDF's, but for some reason will not call the
    stored
    > > procedure. It simply returns the argument I passed with a return code
    of
    > 0.
    > >
    > > However, when I take the same code and backport it to VB 6, the stored
    > > procedure call works fine.
    > >
    > > Any ideas?
    > >
    > > Thanks in advance.
    > >
    > >
    >
    >

    David,

    From your mail..it looks like that you have created External table functions
    and implemented that in Managed code.

    I need to create an external scalar function which should be implemented in
    a managed (c#) code
    By looking at the documentation of IBM DB2 it doesn't say that it supports
    the implementation in managed code at all. The only languages that are
    supported are C, JAVA and OLE.

    I guess this was the intent of Leon's reply of your initial email...that
    .Net implemenatation of external UDFs is still not supported. I was just
    wondering how did that worked for you. Could you provide me some details
    what have you done...may be send a sample copy of your code ...so that I can
    try that with my proposed scalar function.

    -Thanks In advance
    Pankaj

    "David Sarge" <etpcorp@yahoo.com> wrote in message
    news:btg1ql$3maa$1@news.boulder.ibm.com...
    > Leon,
    >
    > There is no SQL in these UDF's. These are UDF's that perform Active
    > Directory queries and return a "table" as a result. Instead of our
    > developer's having to learn .NET directory-services classes, they can
    simply
    > execute these UDF's via SQL.
    >
    > I also created a UDF to allow me to add a user to a domain. Originally,
    > this was coded as an OLE Stored Procedure in .NET. Since this is not
    > working from DB2 to .NET, I will continue to use UDF's. (VB6 works fine,
    > but we are a .NET shop. In addition, the Active Directory API's available
    > to VB6 are not thread safe, but the directory services classes in .NET
    are.)
    > We will not be migrating to v81x until next year, since there are so many
    > patches required on our OS/390 side (v711) to make it play well w/81x (and
    > the reverse).
    >
    > Thanks for the reply.
    >
    > David
    > --
    > David Sarge
    > State of Georgia, Department of Revenue
    > DB2 Certified DBA
    > Oracle Certified DBA
    > "Leon Katsnelson" <leon.nospam@ca.ibm.com> wrote in message
    > news:btbtkn$3cd8$1@news.boulder.ibm.com...
    > > I think we would need some clarification before attempting to answer.
    You
    > > say that you built a VB class in VB.NET and this class contains several
    > UDFs
    > > and SPs. Does this mean that your class provides an implementation of
    the
    > > logic of these UDFs and SPs or is this class simply calling SQL
    statements
    > > that contain UDF calls and calls to SPs?
    > > If your VB.NET class truly implements UDFs and SPs then what you are
    > > attempting to do is develop .NET managed code and have DB2 engine
    execute
    > > this code as UDFs and SPs. This is not a function that works in DB2 UDB
    > > V7.2. Regular VB6 (not .NET) would work because it generates plain old
    > > unmanaged code and DB2 knows how to run this.
    > > The good news is that ability to build server objects (stored procedures
    > and
    > > UDFs) in managed code is a feature in the upcoming DB2 "Stinger"
    release.
    > > You can get a Technology Preview that does allow you to build .NET
    managed
    > > code (using VB.NET or C#) stored procedures. You can get the DB2
    "Stinger"
    > > Technology Preview here
    > > http://www-106.ibm.com/developerworks/db2/downloads/dotnetbeta/.
    > >
    > > "David Sarge" <etpcorp@yahoo.com> wrote in message
    > > news:bsvebj$8cgg$1@news.boulder.ibm.com...
    > > > I am running DB2 v7.2.9 (DB2 7.2 FP 11) and have created a VB Class in
    > > > VB.NET. This class contains several UDF's and a stored procedure.
    DB2
    > > has
    > > > no problem calling the UDF's, but for some reason will not call the
    > stored
    > > > procedure. It simply returns the argument I passed with a return code
    > of
    > > 0.
    > > >
    > > > However, when I take the same code and backport it to VB 6, the
    stored
    > > > procedure call works fine.
    > > >
    > > > Any ideas?
    > > >
    > > > Thanks in advance.
    > > >
    > > >
    > >
    > >
    >
    >