Absent Member.
Absent Member.
282 views

Query against DB with no time-stamp or unique number

There is a table that maintains usernames and their associated priviledges.  It is my intent to regularly query this table that resides in a MySQL instance, in order to monitor for changes to user attributes.  But as mentioned in the title, there is neither a time-stamp nor a numeric ID field by which to anchor the query and satisfy the MAX(xxx) requirement in the sdkibdatabase.properties config.   I have toyed with using @row := @row + 1 AS ROW_COUNT” in the query in order to fabricate a column that has unique and logical values, but this doesn't seem to satisfy the "Max" requirement as the logs fill with "Unable , ."  Due to the complete absence of a time-stamp, I am presuming an ID-based flexconnector.  But this limitation has me totally stuck.  Anyone encounter this or have some thoughts on how to complete this?

Labels (2)
0 Likes
3 Replies
Fleet Admiral
Fleet Admiral

You could do a max on a time stamp in epoc format, then you add an artificial column which also uses the epoc time as the id. Get the connector to use this epoc column as the id and make the uniqueness the epoc id + another field.

It should work, try it and let me know.

0 Likes
Absent Member.
Absent Member.

I had this problem as well. Solved it by using the newid() function in SQL which lets you generate a unique key on the fly

query = SELECT eventID=newid()

0 Likes
Fleet Admiral Fleet Admiral
Fleet Admiral

You may create trigger for update you table and create new table with incremental id and link to you table.

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.