TRICKY - Trying to determine how CarteGraph application populates Primary Key fields

Familiarity with the CarteGraph application may not be necessary to answer this question.  The CarteGraph application is largely constructed on VBS and C#.  It fronts on a SQL Server or Oracle database.  The database typically contains 200 plus tables.  Each table contains a primary key field that is NOT and identity column.  Instead, it appears that the the front end application populates the primary key field.  I'm trying to figure out how it does so.  I've attached a spreadsheet that provides a day's sample of data into one table ("signEvents").  The data is sorted by time of entry.  The values in the signEventsOID appear to be random.  However, the signEventsOID column contains values with only 9 digits.  With the table itself containing over 100,000 rows, it seems to me that a purely random number 9 digit number in a 100,000 row table might result in duplicate primary key values.  

I'm looking for some insight in how these numbers might be generated.  I've spent several hours poking around the VBS modules looking for the code that produces the primary key values.  I've struck out.  
SignEvents.xls
jdanaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gothamiteCommented:
I would use SQL Profiler to trace exactly what is happening. Can you start a trace - all defaults should work i think, then try to get CarteGraph to do whatever it is doing. Then stop the trace and hopefully you should see the exact T-SQL commands that are being sent from CarteGraph.

Note this can cause a performance hit on busy servers. If you can run it in a test environment that would be better.
0
jtsagaraCommented:
Hi,

I looked at you excel , it seems to be a key from another table and when the record created the application copies this id to this table ,
There are many techiques to create a primary key in such applications like keep the numbers in KEYS_TABLES and generate a CRC compined with dates ,

better look at more primary tables that they don't have transactional propose and see if it is a continued number ,

for examble "signMainConditionOID" field seems to be feeded from a static parametrical table,

Hope this help you

0
jdanaAuthor Commented:
gothamite,

I'm 99% sure that the algorithm used to populate the PK column lives in VBS or C#.  The only sprocs associated with the database are those that I built.

jtsagara,

I've looked for a an equivalent to KEYS_TABLES but had no success.  The table that acts as a repository of primary key values makes sense.  Can you point me to a page that discusses the "many techiques to create a primary key in such applications"?  

jdana

0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

gothamiteCommented:
It doesn't matter where the algorithm is, if you do the right Profiler trace you will see the T-SQL commands that the client is sending (maybe after getting translated from whatever abstraction is relevant for the language).
0
jtsagaraCommented:
jdana,
I work on such projects, I work on an ERP system development this era, I am 100% sure that the primary key is generated from the application this is the way to make the application database independent.

If the application generates the key you can port the data to any database, for example if you use oracles ROWID for primary key you can’t use SQL Server cause there is no ROWID.

 I bet that there are no relations in the database schema and the application also checks the cascading deletes.

As for the techniques to generate a primary key I don’t know a specific page but I have seen many of them on various applications.

For example I use a table with the fields “TABLE_NAME, YEAR, INTNO” with an internal routine that handles the locks with delay so all my tables has a concatenation of a primary key like “2010-000000000005” .

Another way is to take ROWID (Oracle) or a combination of RANK - ROW_NUMBER (SQL Server) and add the year or the linear date from the application without a database proc.

There are millions of possibilities and is depended on the designer of the application.

Ok here is what I would do if I were in your position…

1)      Is there code that checks for the databases type in the application? ( ORACLE – SQLSERVER ) , if it is you  must search for sql statement that generates PK in the application layer of course

2)      Is the signEventsOID field string? If it is a number possible contains a linear date

3)      Export all the schema to sql statements and search all the fields that they are the same type with the primary keys ( easy way to find any tables that keep the primary keys )

If you can send here the schema sql , I will check it to see if I can help you…

And remember the easiest solution is the simply solution ….

You can also call the company that provides the software and ask this info , but I think you have done this already correct ?

Have success
John




0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jdanaAuthor Commented:
jtsagara,

Apologies for the slow response.

I truly appreciate the thorough response.  

1) I've poked around the application layer for several hours looking for the blasted code.  No luck.  
2) signEventsOID is simply an integer field.
3) The majority of user data tables in the DB use the same algorithm.  I contacted CarteGraph.  Their technical rep claims the PK values are purely random.  I don't buy it.  With a maximum value of 2E9 for signEventsOID in a 100,000 row table, redundant primary keys will eventually be generated.  (I could do the math, but you get the point.)  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.