Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQLPassthrough from Visual Basic to Oracle. HOW?

Re: SQLPassthrough from Visual Basic to Oracle. HOW?

From: Sreeni Karpurapu <karpurapus_at_detroitedison.com>
Date: 1997/10/02
Message-ID: <01bccf3d$e4ae15a0$185909a2@karpurapus.deco.com>#1/1

For some reason the pass through queries do not access DUAL!

Here is a quick and dirty way to achieve what you want.

Create a stored procedure on oracle ( or you can even write it as a PL/SQL block and pass it as a pass through statement!). All this procedure does is it will insert the next seq number into a temporary work table. Then query it using a record set. If lot of people need to get the next seq number, then identify each seq number by passing a random number. Once you get the next seq number into VB application delete that row from the work table.

Create PROCEDURE GetNextSeq(ID varchar2) IS

    NextSeqNum number(8);
BEGIN
    select ih_id.nextval.nextval into NextSeqNum from dual;     insert into work_tbl values(id, NextSeqNum); END;

-- 

Hope this works!

Sreeni

========================================
Sreeni Karpurapu 
Oracle, SAP DBA
Miracle Software Systems
Southfield, MI 48034
========================================

Northern Lake Service <norlake_at_newnorth.net> wrote in article
<34326F2D.C873FE3E_at_newnorth.net>...

>
>
> Mike Hackett wrote:
>
> > I'm assuming that if you can attach in Access, you've already got
SQL*Net
> > and ODBC drivers configured correctly.
> >
>
> SQL*Net and ODBC are working correctly, but I cannot get the following
code to
> work:
>
> Dim Rs As Recordset, Db As Database, Ndb As Workspace
> Set Ndb = Workspaces(0)
> Set Db = Ndb.OpenDatabase("NLS", False, False,
> "odbc;dsn=NLS;uid=aspen;pwd=scattner;database=NLS")
> Set Rs = Db.OpenRecordset("select ih_id.nextval from dual;",
dbOpenSnapshot,
> dbSQLPassThrough)
> Rs.Close
>
> The above gives me ------ "Run-time error '3146': ODBC-call failed."
>
> I have NLS in the ODBC Administrator, and the ODBC Test on it works fine.
>
> I've tried the CreateQueryDef too, but must be doing it wrong.
>
> Any ideas on what i'm doing wrong???
>
> Chris Geske
> LIMS Manager
> Northern Lake Service, Inc.
> email: norlake_at_newnorth.net
>
>
Received on Thu Oct 02 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US