Microsoft KB Archive/186103

= How to use Oracle Nextval function from Visual FoxPro =

Article ID: 186103

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a

-



This article was previously published under Q186103



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.



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: kbhowto KB186103

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.