On Tue, Oct 4, 2016 at 10:52 AM, Peter Jakobsson <[email protected]>
wrote:
> C_LONGINT($i;$P_SIZE)
> C_TEXT($tableName_t)
>
> $fieldName:="pkUUID"
>
> $P_SIZE:=Get last table number
>
> For ($i;1;$P_SIZE)
> If (Is table number valid($i))
>
> $tableName_t:=Table name($i)
> $statement_t:="ALTER TABLE ["+$tableName_t+"] ADD "+$fieldName+" UUID
> AUTO_GENERATE PRIMARY KEY;"
>
> Begin SQL
> EXECUTE IMMEDIATE:$statement_t;
> End SQL
>
> End if
> End for
>
No this is not enough, you need to check to make sure that the table in
question does not have a primary key already. This code will do that.
For ($TableNumber_L;1;Get last table number)
If (Is table number valid($TableNumber_L))
Begin SQL
SELECT CONSTRAINT_ID
FROM _USER_CONSTRAINTS
WHERE TABLE_ID = :$TableNumber_L AND CONSTRAINT_TYPE = 'P'
INTO :$ConstraintID_txt;
SELECT COLUMN_ID
FROM _USER_CONS_COLUMNS
WHERE CONSTRAINT_ID = :$ConstraintID_txt
INTO :$Primary_key_field_id_L;
End SQL
End if
If ($Primary_key_field_id_L#0)
$tableName_t:=Table name($i)
$statement_t:="ALTER TABLE ["+$tableName_t+"] ADD "+$fieldName+" UUID
AUTO_GENERATE PRIMARY KEY;"
Begin SQL
EXECUTE IMMEDIATE:$statement_t;
End SQL
end if
end for
Also you should do this with an empty data file and then when you open a
real data file, the UUID fields will be populated and validated
You also need to set index to true
The code I used created a document with alter commands etc
//----------------------------------------------------
//User name (OS): administrator
//Date and time: 09/01/15, 13:29:23
//----------------------------------------------------
//Method: ut_CreatePrimaryKeyDoc
//Description
//
// Parameters
// ----------------------------------------------------
If (False)
Mods_2015_08_bug //
//Modified by: administrator (9/1/15 13:29:25)
End if
C_LONGINT($Primary_key_field_id_L)
C_TEXT($TableName_txt;$KeyName_txt)
C_TEXT($OUTPUT_txt;$Output1_txt)
C_TEXT($ConstraintID_txt)
C_LONGINT($TableNumber_L)
For ($TableNumber_L;1;Get last table number)
If (Is table number valid($TableNumber_L))
Begin SQL
SELECT CONSTRAINT_ID
FROM _USER_CONSTRAINTS
WHERE TABLE_ID = :$TableNumber_L AND CONSTRAINT_TYPE = 'P'
INTO :$ConstraintID_txt;
SELECT COLUMN_ID
FROM _USER_CONS_COLUMNS
WHERE CONSTRAINT_ID = :$ConstraintID_txt
INTO :$Primary_key_field_id_L;
End SQL
End if
If ($Primary_key_field_id_L#0)
ALERT("Table named "+Table name($TableNumber_L)+" has a primary key named
"+Field name($TableNumber_L;$Primary_key_field_id_L))
Else
$TableName_txt:=Table name($TableNumber_L)
$KeyName_txt:=Substring($TableName_txt;1;21)+"_UUIDKey_s"
$KeyName_txt:=replace string($KeyName_txt;" ";"_";9999)
$OUTPUT_txt:=$OUTPUT_txt+"Begin SQL"+Char(Carriage return)+"ALTER TABLE
["+$TableName_txt+"] ADD "+$KeyName_txt+" UUID AUTO_GENERATE PRIMARY
KEY;"+Char(Carriage return)+"End SQL"+Char(Carriage return)
$Output1_txt:=$Output1_txt+"EXECUTE FORMULA("+Char(Double quote)+"SET
INDEX(["+$TableName_txt+"]"+$KeyName_txt+";true)"+Char(Double
quote)+")"+Char(Carriage return)
End if
End for
C_TIME($Doc_tm)
$Doc_tm:=Create document("")
SEND PACKET($Doc_tm;$OUTPUT_txt)
SEND PACKET($Doc_tm;$Output1_txt)
CLOSE DOCUMENT($Doc_tm)
//End ut_CreatePrimaryKeyDoc
Hope this helps
Chuck
--
-----------------------------------------------------------------------------------------
Chuck Miller Voice: (617) 739-0306 Fax: (617) 232-1064
Informed Solutions, Inc.
Brookline, MA 02446 USA Registered 4D Developer
Providers of 4D, Sybase & SQL Sever connectivity
http://www.informed-solutions.com
-----------------------------------------------------------------------------------------
This message and any attached documents contain information which may be
confidential, subject to privilege or exempt from disclosure under
applicable law. These materials are intended only for the use of the
intended recipient. If you are not the intended recipient of this
transmission, you are hereby notified that any distribution, disclosure,
printing, copying, storage, modification or the taking of any action in
reliance upon this transmission is strictly prohibited. Delivery of this
message to any person other than the intended recipient shall not
compromise or waive such confidentiality, privilege or exemption
from disclosure as to this communication.
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ: http://lists.4d.com/faqnug.html
Archive: http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:[email protected]
**********************************************************************
