Apex Code Development You need to sign in to do that
Don't have an account?
Jay Salesforce id not unique?
Hello all,
I ran a query on salesforce to acquire all accounts of a specific industry and attempted to save the results to a SQL Server DB table with a primary key constraint on "id".
I get errors everytime I try to load these results. I took the primary key off and data loaded fine. However, when I ran some queries on the saved data it showed that id's where in the table twice.
Are there instances where an "id" maybe reused?
Hi Jay,
The ids in sforce are definitely unique, with a caveat. The caveat is that if you compare them in a case sensitive manner they will be unique. Unfortunately, as you have discovered, SQL Server defaults to case insensitive in comparing values, even to the point of determining primary key violations.
To solve this problem, you can add the useCaseSafeIDs parameter (setting it to true) when you run your query. This will cause the service to return an 18 character ID as opposed to the default 15 character ID. The extra three characters are calculated in such a way as to allow for case insensitive comparisons.
I've run into this myself populating a SQL Server table from the product entity in sforce and the useCaseSafeIDs flag did the trick. If you need more info on the algorithm used to generate the case safe ids, let me know.
Dave,
I am also going to do a batch update back to salesforce, do I need to convert these case safe id's back to case sensitive id's?
Hi Jay,
No, the service can determine based on length which format the ID has been submitted in.
The other option is to load it into SQL Server and then change the collation for each ID field to be case sensitive. To do this, open the design of your SQL table and highlight the ID field. In the properties area for the field, there is a property called collation. By default the value is case insensitive, but if you pick "SQL_Latin1_General_CP1_CS_AS" from the dropdown, the field will be case sensitive and your value will be unique.
You'll also want to do this for all foreign keys so you can perform joins.
Can you give an example of how to set this option in the API? Can't find this parameter in the generated reference.cs.
Thanks.