Visual Basic&database, Is it possible to call an Oracle7 stored procedure from VB?

Is it possible to call an Oracle7 stored procedure from VB?

Author	Message
Doug Peterma #1 / 6	Is it possible to call an Oracle7 stored procedure from VB? As above, is it possible to call an oracle 7 stored procedure from VB? I've tried various syntax, but cannot seem to get it to happen. If so, could you please show an example? I'm trying to use db.execute or db.executeSQL and the passthrough option... It has been suggested that a workaround would be to have a 'dummy' table on the oracle side and use a before insert trigger to call the procedure, which would likely do the trick, but is there a straightforward way to do it? Thanks. -Doug
Sun, 17 May 1998 03:00:00 GMT

Bruce Dougla #2 / 6	Is it possible to call an Oracle7 stored procedure from VB? Quote: > As above, is it possible to call an oracle 7 stored procedure from VB? > I've tried various syntax, but cannot seem to get it to happen. If so, > could you please show an example? I'm trying to use db.execute or > db.executeSQL and the passthrough option... It has been suggested that a > workaround would be to have a 'dummy' table on the oracle side and use a > before insert trigger to call the procedure, which would likely do the > trick, but is there a straightforward way to do it? > Thanks. > -Doug We're using a product that sits between VB and ODBC (Q+E Library), but I expect the syntax is close enough. Here is what worked for us: mSQL$ = "begin sp_name('" & parm1 & "',' & parm2 & "'); end;" -------------------------------------------------------- Bruce Douglas -- BellSouth Telecommunications --------------------------------------------------------
Sun, 17 May 1998 03:00:00 GMT

Andrew Marlo #3 / 6	Is it possible to call an Oracle7 stored procedure from VB? Quote: > As above, is it possible to call an oracle 7 stored procedure from VB? > I've tried various syntax, but cannot seem to get it to happen. If so, > could you please show an example? I'm trying to use db.execute or > db.executeSQL and the passthrough option... It has been suggested that a > workaround would be to have a 'dummy' table on the oracle side and use a > before insert trigger to call the procedure, which would likely do the > trick, but is there a straightforward way to do it? This is exactly the same problem I have got except that I tried the trigger method as well, only to find that although it works, you cannot have transactions in a stored procedure that has been invoked via a trigger (directly or indirectly). This is actually documented as a restriction. So I'm back where I started. I want to use stored procedures from VB precisely because I need transactions and it seems that procedures are the only way to get them. If you make any progress on this, please let us all know. Similarly, if I make any progress I will post to this newsgroup. -- Andrew Marlow APM Software Ltd 20 Queen Alexandra Road, Bedford, England
Sun, 17 May 1998 03:00:00 GMT

Andrew Marsha #4 / 6	Is it possible to call an Oracle7 stored procedure from VB? Quote: >As above, is it possible to call an oracle 7 stored procedure from VB? >I've tried various syntax, but cannot seem to get it to happen. If so, >could you please show an example? I'm trying to use db.execute or >db.executeSQL and the passthrough option... It has been suggested that a >workaround would be to have a 'dummy' table on the oracle side and use a >before insert trigger to call the procedure, which would likely do the >trick, but is there a straightforward way to do it? >Thanks. >-Doug Doug, I am currently on a project at McDonalds Corp. using VB and Oracle Objects and we call lots of stored procedures. I'll get the code tomorrow and follow up to this message with the code. -- Andrew Marshall, Visual Basic junkie Software Dynamics Consulting 72223,330 on CompuServe
Mon, 18 May 1998 03:00:00 GMT

Doug Peterma #5 / 6	Is it possible to call an Oracle7 stored procedure from VB? Quote: >> As above, is it possible to call an oracle 7 stored procedure from VB? >> I've tried various syntax, but cannot seem to get it to happen. If so, >> could you please show an example? I'm trying to use db.execute or >> db.executeSQL and the passthrough option... It has been suggested that a >> workaround would be to have a 'dummy' table on the oracle side and use a >> before insert trigger to call the procedure, which would likely do the >> trick, but is there a straightforward way to do it? >This is exactly the same problem I have got except that I tried the trigger >method as well, only to find that although it works, you cannot have transactions >in a stored procedure that has been invoked via a trigger (directly or indirectly). >This is actually documented as a restriction. So I'm back where I started. >I want to use stored procedures from VB precisely because I need transactions >and it seems that procedures are the only way to get them. If you make any progress >on this, please let us all know. Similarly, if I make any progress I will post to this >newsgroup. Per Bruce Douglas's post, the syntax that seems to work (regardless of the ODBC driver being Q+E or MS...) is as follows: mSQL$ = "begin sp_name('" & parm1 & "',' & parm2 & "'); end;" (in case anyone missed it.) Thanks Bruce. -Doug
Tue, 19 May 1998 03:00:00 GMT

Sean Dillo #6 / 6	Is it possible to call an Oracle7 stored procedure from VB? This probably isn't the answer you're looking for, but... I've just completed two back-to-back client-server projects in VB: the first, for a hospital, used ODBC and sqlpassthrough; the second used Oracle Objects for OLE (OO4O). I have absolutely nothing but good to say about the OO4O product; I've never experienced any weird behavior, the performance is great, and best of all, it supports calling stored procedures and functions with their db.dbExecuteSQL syntax. It also supports bind variables, too. Oh how I wish I had used it for the first! I can say with a straight face that OO4O cut the coding complexity by a third. I STRONGLY recommend Oracle's product. If you really want to see your app fly, get as much code off your client and on to the server in the form of PL/SQL procedures, calling them with bind variables. OO4O makes this easy.
Thu, 21 May 1998 03:00:00 GMT

Page 1 of 1

[ 6 post ]