Highlighted
Absent Member.
Absent Member.
386 views

How to retrieve primary key when MSSQL JDBC is leading


We have a MSSQL 2012 R2 database, which is leading in setting the
primary key to a GUID. We are doing this by giving the primary key
column a default value of newid(). After a row is inserted, the MSSQL
newid function will generate a GUID like,
8F71E963-2A02-4550-A1F0-58D172DB4862.
I will use this GUID for the association to a userobject in the Vault. I
tried all of three retrieval methods, but none of them is working for
me.
By Auto (auto-generated/identity column):
DirXML: [02/15/13 16:22:52.22]: TRACE: BEGIN Get auto-generated primary
key value.
DirXML: [02/15/13 16:22:52.22]: TRACE: RS @ index 1, field 'IdM_GUID',
value = null
DirXML: [02/15/13 16:22:52.22]: TRACE: Closed result set
SQLServerResultSet:1659.
DirXML: [02/15/13 16:22:52.22]: TRACE: Closed statement
SQLServerPreparedStatement:1667.

and later:
Unable to retrieve auto-generated primary key value. An illegal primary
key value null was returned at index 1 for column
The documentation shows that this method only works for a MySQL driver
and and MSSQL, so that clear for me.
By Driver (Subscriber-generated):
Conversion failed when converting from a character string to
uniqueidentifier

I think this is correct because this type of SQL-GUID cannot be
retrieved by a SELECT MAX(pk_columname)
and by the None (subscription event), the PK must set in the add event.
So how can I achieve this. Maybe by using MSSQL stored procedures icm
with table-local retrieving

G. Schouten


--
gschouten32
------------------------------------------------------------------------
gschouten32's Profile: https://forums.netiq.com/member.php?userid=2546
View this thread: https://forums.netiq.com/showthread.php?t=46857

Labels (1)
0 Likes
5 Replies
Highlighted
Absent Member.
Absent Member.

Re: How to retrieve primary key when MSSQL JDBC is leading

So technically 2012 is not a supported database type. That's the fine print but not to worry there.

Use the Table-Local method.
you can try auto but also set the after parameter so something like tablename(auto, after)
if the driver you are using for the SQL 2012 connection is a type three driver then it is possible
that auto might work.

Most likely you will have to use the stored procedure method to get the value you are looking for in
an after fashion. I would pass the stored procedure some unique identifier from the vault that is
able to look up the user in the database to retrieve the GUID as opposed to last generated. In
theory last generated will work in your SP but I've always been a little sketch on that when you
start getting heavy transactions.

Then, if that isn't working, please post a longer level three trace, your stored procedure, and the
driver configuration that contains the subscriber config parameters.
Thanks!

On 2/17/2013 1:14 PM, gschouten32 wrote:
>
> We have a MSSQL 2012 R2 database, which is leading in setting the
> primary key to a GUID. We are doing this by giving the primary key
> column a default value of newid(). After a row is inserted, the MSSQL
> newid function will generate a GUID like,
> 8F71E963-2A02-4550-A1F0-58D172DB4862.
> I will use this GUID for the association to a userobject in the Vault. I
> tried all of three retrieval methods, but none of them is working for
> me.
> By Auto (auto-generated/identity column):
> DirXML: [02/15/13 16:22:52.22]: TRACE: BEGIN Get auto-generated primary
> key value.
> DirXML: [02/15/13 16:22:52.22]: TRACE: RS @ index 1, field 'IdM_GUID',
> value = null
> DirXML: [02/15/13 16:22:52.22]: TRACE: Closed result set
> SQLServerResultSet:1659.
> DirXML: [02/15/13 16:22:52.22]: TRACE: Closed statement
> SQLServerPreparedStatement:1667.
>
> and later:
> Unable to retrieve auto-generated primary key value. An illegal primary
> key value null was returned at index 1 for column
> The documentation shows that this method only works for a MySQL driver
> and and MSSQL, so that clear for me.
> By Driver (Subscriber-generated):
> Conversion failed when converting from a character string to
> uniqueidentifier
>
> I think this is correct because this type of SQL-GUID cannot be
> retrieved by a SELECT MAX(pk_columname)
> and by the None (subscription event), the PK must set in the add event.
> So how can I achieve this. Maybe by using MSSQL stored procedures icm
> with table-local retrieving
>
> G. Schouten
>
>


0 Likes
Highlighted
Absent Member.
Absent Member.

Re: How to retrieve primary key when MSSQL JDBC is leading


unfortunately tablename(auto, after) in de table-local is not working.
So Now I'm trying to this by SQL function or stored procedure.
I'm not a database administrator, stored procedures and functions are
not really clear for me.
Now trying to do this by the SQL function, newid() but that's not
working. Log is complaining about newid is not a stored procedure. So my
database administrator configured a stored procedure which is executing
the newid function. I configured it like tablename(storedprocedure).
Still now working, log is complaining about an INOUT parameter.


--
gschouten32
------------------------------------------------------------------------
gschouten32's Profile: https://forums.netiq.com/member.php?userid=2546
View this thread: https://forums.netiq.com/showthread.php?t=46857

0 Likes
Highlighted
Absent Member.
Absent Member.

Re: How to retrieve primary key when MSSQL JDBC is leading

On 2/19/2013 7:44 AM, gschouten32 wrote:
>
> unfortunately tablename(auto, after) in de table-local is not working.
> So Now I'm trying to this by SQL function or stored procedure.
> I'm not a database administrator, stored procedures and functions are
> not really clear for me.
> Now trying to do this by the SQL function, newid() but that's not
> working. Log is complaining about newid is not a stored procedure. So my
> database administrator configured a stored procedure which is executing
> the newid function. I configured it like tablename(storedprocedure).
> Still now working, log is complaining about an INOUT parameter.
>
>

If you could post the stored procedure and the exact value you are using in the driver config along
with the error snippet I can help you.
0 Likes
Highlighted
Absent Member.
Absent Member.

Re: How to retrieve primary key when MSSQL JDBC is leading


I think it has to do something with the in/out parameter, which I don't
understand.

Here is the config and the log:
http://tinyurl.com/azjvguv


--
gschouten32
------------------------------------------------------------------------
gschouten32's Profile: https://forums.netiq.com/member.php?userid=2546
View this thread: https://forums.netiq.com/showthread.php?t=46857

0 Likes
Highlighted
Absent Member.
Absent Member.

Re: How to retrieve primary key when MSSQL JDBC is leading

On 2/21/2013 5:14 AM, gschouten32 wrote:
>
> I think it has to do something with the in/out parameter, which I don't
> understand.
>
> Here is the config and the log:
> http://tinyurl.com/azjvguv
>
>

So first off, your stored procedure of InsertIDM is simply going to generate a random GUID. It
doesn't do anything else in it's current form.

Side note, are you absolutely tied to using a GUID? This gets infinitely easier if you use a simple
identity column on an integer and is much more efficient from the database side.

The challenge with a GUID is that there is no function to retrieve the last generated GUID value.
So your stored procedure needs to be passed a parameter that can be used to look up the record in
the table uniquely and then return the GUID.

Given a table:
CREATE TABLE [dbo].[employee](
[IDM_guid] [uniqueidentifier] NOT NULL,
[badgeno] [int] NULL,
[firstname] [varchar](50) NULL,
[lastname] [varchar](50) NULL,
CONSTRAINT [PK_employee] PRIMARY KEY CLUSTERED
(
[IDM_guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


You could use the following stored procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: WSCHNEIDER
-- Create date: 21 FEB 2013
-- Description: RETRIEVES GUID FROM TABLE INSERT
-- =============================================
CREATE PROCEDURE IDM_INSERT
@SEARCHVAL int
AS
BEGIN
SET NOCOUNT ON;
SELECT IDM_GUID FROM employee WHERE badgeno = @SEARCHVAL;
SET NOCOUNT OFF;
END
GO


0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.