The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
(RESOLVED) Generate Table Id's W/Out slxCreateIDFor
Posted: 21 Sep 07 9:44 AM
|
Im in a position where I will need to be able to create the Table Id's from a website. The server that this site will sit on will not have the slx provider on it so I will be creating the Id's with a function that I created. This function uses the SITEKEYS table to get the current KeyValue and then increament that number. I have figured out the logic behind it and it all seems to be working correclty.
The problem that im having is this, Im not sure which SITEKEY belongs to the QUOTEMAIN TABLE. All the other tables that I will be using (Account, Contact, Address) are labeled so they stand out but the QUOTE table does not. In looking at the ID's of the Quotemain Table it appears that it may be using the SITEKEY with the KeyDesc of 'Other' as this is the only KeyValue that starts with or is even close to 'A00F'. That being said Im not sure that this is where the QuoteMain Table is getting its KeyValue from and I was wondering if any one knew for sure.
I guess I can generate a quote real quick and see if it increaments the 'Other' field but I thought I would ask. |
|
|
|
Re: Generate Table Id's W/Out slxCreateIDFor
Posted: 21 Sep 07 9:50 AM
|
Ok sorry for the post. I guess I should of just created an ID for the QuoteMain Table and then compared the DB value to the Id that was created.
The QuoteMain Id is generated from the KeyDesc field of Other (at least this is the case with my version of SLX which I have no idea which version it is).
Thats it |
|
|
|
Re: (RESOLVED) Generate Table Id's W/Out slxCreateIDFor
Posted: 22 Sep 07 7:23 AM
|
Originally posted by lmf232s
Im in a position where I will need to be able to create the Table Id's from a website. The server that this site will sit on will not have the slx provider on it so I will be creating the Id's with a function that I created. This function uses the SITEKEYS table to get the current KeyValue and then increament that number. I have figured out the logic behind it and it all seems to be working correclty.
The problem that im having is this, Im not sure which SITEKEY belongs to the QUOTEMAIN TABLE. All the other tables that I will be using (Account, Contact, Address) are labeled so they stand out but the QUOTE table does not. In looking at the ID's of the Quotemain Table it appears that it may be using the SITEKEY with the KeyDesc of 'Other' as this is the only KeyValue that starts with or is even close to 'A00F'. That being said Im not sure that this is where the QuoteMain Table is getting its KeyValue from and I was wondering if any one knew for sure.
I guess I can generate a quote real quick and see if it increaments the 'Other' field but I thought I would ask. |
|
This is a very dangerous practice and you could easily trash sitekeys.
Yes.. all custom 1:m tables use the "Other" key (and so do a bunch of SalesLogix tables).
Why can't you do a provider connection? You seem to be able to do a normal SQL connection.. just load the provider. It's available as a stand-alone install.
--
rjl |
|
|
| |
|
Re: (RESOLVED) Generate Table Id's W/Out slxCreateIDFor
Posted: 26 Sep 07 8:52 AM
|
inspite of getting so many requests to get the IDs created thru some sql technique as opposed to an SP in the provider , sage has not provided with a legit way to get it created.All the techniques that we do is totally unsupported and they wouldn't waste 2 secs in telling you that your on your own there...
rj, why dont you tech partners out there ask sage to come up with some new features lilke this atleast for ppl with sql 2005(an sp in a .net lang which will do this same trick) |
|
|
|
Re: (RESOLVED) Generate Table Id's W/Out slxCreateIDFor
Posted: 27 Sep 07 6:47 AM
|
Nandu, Using the provider is the prefered method. For the mobile product keys are generated on the device using the 'prefix-SiteCode-Base36Increment 7 charactervalue' specification. More specifically the key ids are determined at the high order. Since there are 7 base 36, the ID's are started at Z000000 allowing a huge range of numbers in between and making pretty safe to be up there.
Mark |
|
|
|
Re: (RESOLVED) Generate Table Id's W/Out slxCreateIDFor
Posted: 27 Sep 07 1:06 PM
|
7 base 36 ? what is that numbering system? if it is the regular base36 which starts with A000000 , then isnt it what slx is doing when they start the numbering process?
and defnitely i didnt take mobile into account when i was talking about exposing that id generator thru a sql sp.My expirience with mobile is limited to being a corporate enterprise BB user ..
if deployed thru an sp, is that going to hurt the overall architecture of slx that much?whats the harm in having multiple ways to genarate the IDs?
while we are at this i have another one.....i have heard that for java apps to use the slx oledbprovider , there has to be another type2 driver which can handle the incoming jdbc calls.Is this true? if so why is sage not keen of integrating the java systems with slx data thru a separate type2 driver ?
thanks!
-nandu
|
|
|
|
Re: (RESOLVED) Generate Table Id's W/Out slxCreateIDFor
Posted: 27 Sep 07 6:45 PM
|
Doesn't this problem go away if you set the PK to auto-generate and then do inserts using the provider? Why worry about separate SPs?
SLX has never been very good at using extended SQL Server functionality - and given that it works with Oracle too, I understand why. Once you accept this, you can learn to live with the consequent limitations - well, I have anyway. Don't get me wrong - I have a SQL Server background and it is sometimes *incredibly* frustrating not to be able to use some of the horsepower that raw SQL Server can provide. |
|
|
|
Re: (RESOLVED) Generate Table Id's W/Out slxCreateIDFor
Posted: 28 Sep 07 7:57 AM
|
Nandu,
For your Java questions I really do not have any idea. All my server side development is in .net so I have never come up with that edge case. I also do not know of anywork that the Sage SalesLogix team is doing with Java so being keen to integrate through drivers is more then likely outside the scope of what they are doing (IMHO). Regular Base 36 does not have to start with A000000, it can start with 000000. Note the key is made up of a prefix (A) then a site Code (EA12) and then the numbering auto incremented. So the whole key is AEA120000000. The biggest harm in having multiple ways of generating keys is possible key collision for inserts. Thats why the Mobile keys are set much higher up in the key range. The potential of key collision is much much less and in a distributed environment this needs to be done. In Sage CRM distribution of keys are done via a range 1000-2000 and when that range is exhausted a new range is requested. Frankly I prefer GUIDs as they are pretty much guarenteed to be unique but of course carry a size penalty.
Mark |
|
|
|
Re: (RESOLVED) Generate Table Id's W/Out slxCreateIDFor
Posted: 28 Sep 07 8:03 AM
|
Phil,
If you are inserting through the provider indeed you can use the auto-generate feature. If you are doing a distributed smart client application that is not connected to the server in a client server approach but has to synchronize data at a later time then when it is created it can be problematic. Complexity ensues you have to have tempory IDs that will need to be updated in the client when the changes are finally saved at the server. By taking an approach of ranging the ids and generating you can ensure that referencial integrity is maintained and you can query on your local client objects consistently (no special query rules need to be defined).
Here us a question with your SQL background. Is it possible with 2005 hosting the CLR to create .net extended sp that can give you back the ID from the provider (Provided it is on the same box) so that you can do the inserts within a query on the SLX box. The only way this will competely work is if no remotes are in the system.
Mark |
|
|
| |
|