Whitehead, Roy
  •  Roy
  • New Topic Starter
2003-11-08T12:34:00Z
In order to fully evaluate this product I would be most grateful if someone can supply a concrete example of the SQL statement required in order to obtain the Primary/Mandatory Key.

I have tried "Select LAST_INSERT_ID" , but it only works after an INSERT statement has been successfully.


Cordially
Roy
Sponsor
GeniusConnect documentation search (User Manual Downloads...)
Administrator
2003-11-08T13:55:00Z
Roy,

what datatype is your primary key column?

Whitehead, Roy
  •  Roy
  • New Topic Starter
2003-11-09T12:51:00Z
Hi,

The primary key "CLIENT_KEY" is an "int(11) auto_increment NOT NULL", which I believe is the standard data type for a primary MySql Key.

Although, I think I may have to change the Data Type to a VARCHAR(255) in order to conform to Outlooks Unique Key, I may be wrong though.

I really don't mind if I have to change the Data Type although I would prefer to NOT change the "auto_increment" counter.

Roy
Administrator
2003-11-09T15:33:00Z
Roy,

try
select MAX(CLIENT_KEY)+1 from <MY_TABLE_NAME>.

Whitehead, Roy
  •  Roy
  • New Topic Starter
2003-11-10T13:20:00Z

This solution would work as long as none of the records in the field were ever deleted. As an example, imagine that we have created 10 records and that each record had the following key values 1,2,3...9,10.

If record number 5 was deleted, which would leave us with 9 records, your SQL statement would return a unique identifier ( 9 Records + 1 ) of "10".

Obviously this would create a duplicate as the Unique Value "10" already exists.

Unfortunately for the moment, I cannot find the solution, but I am sure it must exist.

Roy.


Administrator
2003-11-10T13:47:00Z
Roy,

your example:column values 1,2,3,4,6,7,8,9,10
Result of MAX(..) + 1 is "11" not "10", "11" is unique!!


Whitehead, Roy
  •  Roy
  • New Topic Starter
2003-11-10T14:36:00Z

Excuses, excuses, excuses.

I was thinking of SELECT COUNT and not SELECT MAX, I should have executed the statement before replying.

Your SQL statement does indeed work

"SELECT MAX(Field_Name) FROM Table"

Thank you

Roy
Gord
2012-11-29T01:48:00Z
Hello,

I too have a question about Mandatory Keys (SQL Server 2008 R2). In the SQL textbox, I put: DECLARE @pl_id decimal(16, 0) EXEC [dbo].[sp_get_primary_key_v2] 'user', 'id_employee', 1, @pl_id

and then click the test key and it works fine, showing the newly acquired Primary Key. Then I click OK and lose everything except the 'declare' portion of the statement.

1. Can we use stored procedures? The test worked fine but the statement was not saved.
2. If we can use sproc, this one has an output variable, @pl_id, is that causing the issue?

I am using Oct release, 32 bit, Wndows 7, Outlook 2010. Thanks in advance.
Administrator
2012-11-29T02:11:00Z
Hello,

you can use stored procedure, but it must return a result set with 1 value: Example:
{call sp_get_key('myParams')}

CREATE PROCEDURE [dbo].[sp_get_key] 
	-- Add the parameters for the stored procedure here
	@myParam some type XXX
AS
BEGIN
	-- SET NOCOUNT ON prevent extra result sets to client
	SET NOCOUNT ON;

	--internal SP logic--


	SET NOCOUNT OFF;
    -- return a value for the key
	select mykey from .......
END
Similar Topics