Using guid's as primary keys in Powerbuilder

[Former Member]

Hi,

I have been tasked with researching the use of guid's as primary keys in our Powerbuilder applications.

We currently transfer data between databases and often across different database vendors (Oracle, SQL Server, and Adaptive Server Anywhere).  We currently maintain ids and manage those ids during the data transfer to ensure that duplicates do not exist in the destination db.  Our system works well however it is very time consuming.  We would like to move towards guids so that we can eliminate the need to manage the ids during transfer.

I have found examples of how to generate guids using Powerbuilder (ll_result = PBObject.GenerateGUID (REF ls_GUID) ) but I am looking for feedback on the best or easiest method to use for generating guids within a Powerbuilder application.

Thanks,

Karen

[former_member190719 · Accepted solution]

I'd recommend relying on the database's internal methods for generating them:

     Oracle:                              SYS_GUID()

     SQL Server:                       NEWID()

     Adaptive Sever Anywhere:   NEWID()

You might create descendants of the transaction object class (n_trans if using PFC) and put a common get_guid method on it.  In the database specific transaction objects, you would then code the appropriate database specific call.

You might also consider modifying the PBODBxxx.INI file (where xxx is the PB version number) so that the GetIdentity value references those methods and just use the autoincrementing column option in the DataWIndow itself to get the value.  You'd have to play with it a bit for Oracle though, as I'm not sure if the Oracle native driver knows to use the PBODBxxx.INI file or what you would have to add to get it to use it.

[Former Member · Accepted solution]

Thanks Bruce,

I appreciate the fast reply.  I will check out the db functions for fetching guids.

Thanks,

Karen

[Former Member · Accepted solution]

Hey Bruce,

One more question.  I have created a table in an ASA db called guid_testing with two columns. 

1) guid_id uniqueidentifier

2) guid_comment char(500)

I have created a datawindow in Powerbuilder that retrieves from this table.  Powerbuilder warns me that i cannot include the guid_id column in my select statement as it would require the user of an embedded select statment.

Question 1: You referenced using the autoincrementing column option to fetch the next guid.  Are you referring to an identity column?

Question 2: If you are referring to an identity column, how do i reference the guid_id column as an identity when i can't select it?

Thanks again,

Karen

[former_member190719 · Accepted solution]

That's odd.  I'm using PB 12.5 Classic and I was able to create a DataWindow that retrieved and inserted into a similarily created table.

Wasn't thinking through the autoincrement thing though when I posted that.  The way that the autoincrement works you need to have the database automatically populate the column, but there has to be some method for you to call from PowerBuilder that would retrieve the value assigned after the insert was sent to the database.  The problem is that there doesn't seem to be any methods with GUIDs to say "tell me the last value that got used".

So, instead of having the database automatically populate the column, you would use the method on the transaction object get the GUID and then use that to populate the column in the datawindow before sending the insert to the database.

[Former Member · Accepted solution]

I am not sure if it makes a difference or not, but i am using ASA9.  If i use the string unique identifier datatype (char 36) it works fine. 

I have been playing with the ini file and was never able to get it to fetch the guid.  Instead of calling my function i just tried selecting the NEWID()

GetIdentity='Select NEWID()'

I am still playing with it, but not certain yet if i am making any headway.

I would really like to use the identity columns if possible so that i do not have to modify data before the insert.

Thanks for all the help,

Karen

[former_member190719 · Accepted solution]

You can use identity columns.  Declare the columns as autoincrementing rather than using a GUID.  In that case, what was originally in the PBODBxxx.INI file will work.

The issue with the identity columns and GUIDs was specific to GUIDs.  There doesn't seem to be a way to say "what was the last GUID assigned".  Calling NEWID directly will just give you a new GUID, it won't tell you what the last one was.  That's what you need in order to get the DataWindow identity column feature to work, some way to ask the database what value was assigned.

Don't know if you saw this blog post.

[Former Member · Accepted solution]

Sorry Bruce, i misunderstood what you were saying about the identity columns and guids in your previous post.  That may be why my post seemed unclear.  Thanks for the link for the blog post.  I had already found and read it on Friday.  Thank you.

I have moved forward with my guid testing and we are happy with using the specific function calls from the database. 

Thanks again for your help

Karen

[Former Member · Accepted solution]

Hello Bruce,

I took this way in Powerbuilder

SELECT UUIDTOSTR(NEWID())  INTO :ls_realestate_guid FROM ts_realestate;

where ts_realestate is a table and :ls_realestate_guid a string.

But sometimes it works, sometimes I get an empty string.

In the table ts_realestate I declared the primary key "re_guid" as default newid(). But when I Update the datawindow for the table ts_realestate, I get an error: re_guid may not be empty.

How can I call NewId() directly from Powerbuilder?

Thank you for your Help

Stephan

[former_member190719 · Accepted solution]

Stephan Schröpel wrote:
I took this way in Powerbuilder
 SELECT UUIDTOSTR(NEWID())  INTO :ls_realestate_guid FROM ts_realestate;
where ts_realestate is a table and :ls_realestate_guid a string.
But sometimes it works, sometimes I get an empty string.

You need to do some database tracing to determine why that's not working all the time.

In the table ts_realestate I declared the primary key "re_guid" as default newid(). But when I Update the datawindow for the table ts_realestate, I get an error: re_guid may not be empty.
How can I call NewId() directly from Powerbuilder?

You can either:

1.  Call the statement from embedded SQL and put the returned value in the datawindow directly through powerscript

OR

2.  Use it as a default in the table, but then query the value that was assigned through the GetIdentity entry in the PBODBCxxx.INI file for that particular database.  I don't know how you would do that with NewID though.  SQL Server has an OUTPUT clause that can return the values, but PB doesn't (that I know of) know how to use it.

[Former Member]

I don't know if this would be helpful or not, but I created a GUID generator in PowerBuilder to embed into the request object of one of the web services the app consumes.

If you're interested in the code I'd be happy to send it to you.

[former_member190719]

Just curious, are you using the GenerateGUID function?  Many people don't realize that such a function exists in PB.

[Former Member]

Count me as another one of those people that didn't realize such a funciton existed.  Said another way, no, I wrote my own GUID builder; it's a little old school in that it relies on the MAC address and time stamps and such, but it will always generate a unique ID.

[Former Member]

Update: I noticed the link you sent me was for PowerBuilder 10.5; I just checked the PowerBuilder 12.5 documentation and that section of the Application Techniques manual has been eliminated.  In fact, I just searched for "GenerateGUID" in all of the 12.5 documentation and see no reference at all.  Looks like this method may have gone the way of the Dodo. 

[Former Member]

My SMTP example has a function that generates GUID using Windows API functions.

http://www.topwizprogramming.com/freecode_emailsmtp.html

[Former Member]

Thanks Roland!

I've browsed through your various (and very helpful) goodies before and have used more than one when the need fit the solution you've come up with.  I wrote the GUID generator a year and a half ago or so and I don't remember exactly which programming examples I used for inspiration (or for that matter if I used any at all).  I'll look at how you solved the problem and see if it rings a bell.

Either way, this now gives Karen a second (and probably better) code model to use should she want to generate a GUID internally instead of relying on a database call (which in my case wasn't possible as the particular application using mine doesn't use any database connections). 

Regards,

Dale

[former_member190719]

Yep, you're right.  Forgot about that. It was in 11.0 as well.  However, in 11.5 they dropped support for "automation server projects", which was what that function was based on.