• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 610
  • Last Modified:

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
0
jdana
Asked:
jdana
  • 2
  • 2
  • 2
1 Solution
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now