DOCUMENT:Q186103  20-AUG-1999  [foxpro]
TITLE   :HOWTO: Use Oracle Nextval Function from Visual FoxPro
PRODUCT :Microsoft FoxPro
PROD/VER:WINDOWS:3.0,3.0b,5.0,5.0a
OPER/SYS:
KEYWORDS:

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0, 5.0a 
-------------------------------------------------------------------------------

SUMMARY
=======

The sample code below demonstrates how to use the Nextval function of an Oracle
database from Visual FoxPro. The Nextval function retrieves the next available
number from a sequence.

Oracle Sequence is similar to Identity Columns in Microsoft SQL Server version
6.0, which contains system-generated sequential values that uniquely identify
each row within a table.

MORE INFORMATION
================

SQL Passthrough Sample Code
---------------------------

      Sqlhandle=sqlconnect("Oracle_Server")
      =Sqlexec(Sqlhandle,"Insert into person(P_id,lastname,firstname) ;
      values (PersonSN.nextval,'Lastname','Firstname')")

Remote View
-----------

The Remote View designer parses 'PersonSN.nextval' as a field named "nextval" in
a table named "PersonSN". Since a table named "PersonSN" does not exist, the
following error occurs at run-time:

   Alias not found.

In order to retrieve the sequence from Oracle, you must call an Oracle User
Defined Function (UDF) before appending a record.

   Oracle Server
   -------------

      ** Create a Sequence name "PersonSN"

      CREATE SEQUENCE PersonSN
      INCREMENT BY 1
      START WITH 100001
      NOCACHE;

      ** Create a Server Function "FNext" to return the next sequence from
      ** Person.

      CREATE OR REPLACE FUNCTION FNext RETURN NUMBER
      IS
         Count1 NUMBER(10,2);
      BEGIN
         SELECT PersonSN.nextval INTO Count1
         FROM dual;
       RETURN(Count1);
      END;
      / 

   Visual FoxPro
   -------------

      =SQLEXEC(Sqlhandle,"Select FNext from dual")

Additional query words: VFoxWin kbDSupport KBDSE

======================================================================
Keywords          :  
Technology        : kbVFPsearch kbAudDeveloper kbVFP300 kbVFP300b kbVFP500 kbVFP500a
Version           : WINDOWS:3.0,3.0b,5.0,5.0a
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1999.