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?
Auto generated key support in SQLBizComponent (1 viewing) (1) Guest
Go to bottom Post Reply Favoured: 0
TOPIC: Auto generated key support in SQLBizComponent
#5904
venkatrao (User)
XAware user
Posts: 9
User Offline Click here to see the profile of this user
Auto generated key support in SQLBizComponent 9 Years, 11 Months ago Karma: 0  
Good Morning Kirstan,

I want to use the feature of returning of auto generated key when we perform insert operation.

A example can be, insert order items based on the returned auto generated key of order insert.

Is there any provision to get the auto generated key in XAware?

Can you Please help me in find examples which demonstrats this? if no, do you have any suggestion to meet this requirement?

If anyone already implemented this can you please take some time and post information Please.

Thank You Very Much for Your Help.

Thanks,
Venkat.
 
Report to moderator   Logged Logged  
  The administrator has disabled public write access.
#5906
dblack (Admin)
Admin
Posts: 508
User Offline Click here to see the profile of this user
Re:Auto generated key support in SQLBizComponent 9 Years, 11 Months ago Karma: 21  
Venkat,
It depends entirely on the functionality provided by your data source. Watch this previously recorded webinar(around 18:25) in which I do exactly what you want using mySQL...
 
Report to moderator   Logged Logged  
  The administrator has disabled public write access.
#5907
venkatrao (User)
XAware user
Posts: 9
User Offline Click here to see the profile of this user
Re:Auto generated key support in SQLBizComponent 9 Years, 11 Months ago Karma: 0  
Good Morning Darin,

Thank You for information.

When I gone through the webnair, you are hitting DB again to find the last insert id. So as you said we should have some sort of function exist at DB side to give us last generated id and this function will differ from DB to DB, Can we avoid this hit to DB again? like how Delete BizComponent does, it give the deleted records in the same bizcomponent, That way we have only one component doing insert.

Thanks again for your Help.

Thanks,
Venkat.
 
Report to moderator   Logged Logged  
  The administrator has disabled public write access.
#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, 11 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.
#5911
venkatrao (User)
XAware user
Posts: 9
User Offline Click here to see the profile of this user
Re:Auto generated key support in SQLBizComponent 9 Years, 10 Months ago Karma: 0  
Thank You Paul for briefing the approaches.

May be if you could leverage the method Statement.getGeneratedKeys() provided by JDBC api then another hit to DB can be avoided, but as you said its depend on DB and Driver then I think better approch can be hitting db again for generated id or may generate the id at bizcomp level and insert in to DB.

Thank You.
 
Report to moderator   Logged Logged  
  The administrator has disabled public write access.
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