Skip to content

XAware Community

Get the Flash Player to see this player.
Flash Image Rotator Module by Joomlashack.
XAware 5.6
Need Help
Webinars and Events
Advanced Tutorials
Webinars and Events

You are here: Home arrow Forums
XAware Community Forums
Welcome, Guest
Please Login or Register.    Lost Password?
Re:Auto generated key support in SQLBizComponent (1 viewing) (1) Guest
Go to bottom Post Reply Favoured: 0
TOPIC: Re:Auto generated key support in SQLBizComponent
#5910
prichards (Admin)
Admin
Posts: 319
User Offline Click here to see the profile of this user
Re:Auto generated key support in SQLBizComponent 9 Years, 6 Months ago Karma: 18  
Other than the Insert-Update (aka "upsert") operation, which is provided as a convenience for a common combination of standard operations, all of the operations in our SQL BizComponent correspond to a single SQL operation. Even the Delete is a single operation; it's just that the SQL Delete operation may provide the number of rows deleted as a result.

Auto-identity columns are not standard SQL operations, but are features of the database that vary from database to database (and not supported on all databases). Using XAware, you can use the SQL BizComp to execute a SQL query/operation, but is limited to a single SQL operation per request, so would be much like designing the SQL operations you would use in Java, a Stored Procedure, or SQL scripting. Most databases that support an auto-identity column, provide database functions to retrieve the last inserted id value (with scoping and other restrictions). So, just as you would in SQL, you can write your Insert operation and a separate Select operation to retrieve the last inserted value. The exact function and behavior depends on the database, and you can use the "pass_through" mode if needed, to execute a specific SQL statement without using the Select wizard.

So one straight-forward approach is to perform the Insert operation in one BizComponent and then Select the result using the appropriate database function in another BizComponent (as in the webinar). You can wrap the operations in BizDoc, if you need to reuse them in several places.

Another common approach is to perform the Insert/Select in a Stored Procedure, then invoke the Stored Procedure from XAware (using the Stored Procedure BizComponent) as a single operation that returns the inserted id. This approach helps manage scope for some databases, as the operations in the Stored Proc are in the same database session scope.

Some databases like Oracle, use a "sequence" instead of an auto-identity, in which you can more easily control the id; i.e. you can Select the sequence value first and then use it in an Insert and related Inserts (or just use the nextval function directly in the Insert if you don't need the id elsewhere).

Another approach is to generate your own key first (using a session variable sequence, GUID, or unique value in the source data), and then use that value in the Insert and related inserts - instead of the auto-identity setting on the database column.
 
Report to moderator   Logged Logged  
  The administrator has disabled public write access.
      Topics Author Date
    thread link
Auto generated key support in SQLBizComponent
venkatrao 2010/02/16 03:00
    thread link
thread linkthread link Re:Auto generated key support in SQLBizComponent
dblack 2010/02/16 11:42
    thread link
thread linkthread linkthread link Re:Auto generated key support in SQLBizComponent
venkatrao 2010/02/17 11:53
    thread link
thread linkthread link Re:Auto generated key support in SQLBizComponent
prichards 2010/02/22 11:44
    thread link
thread linkthread linkthread link Re:Auto generated key support in SQLBizComponent
venkatrao 2010/02/24 06:00
Go to top Post Reply
Powered by FireBoardget the latest posts directly to your desktop

Community Login

Get the Flash Player to see this player.
Flash Image Rotator Module by Joomlashack.
Commercial
Free Training
QuickStart Packages
Image 4 Title
Image 5 Title

Visit XAware.com