Campos AutoIncrement - Key Violation

[gustavosv]

Hola a todos, se me presenta el siguiente problema: tengo una tabla en MySQL cuya llave principal es un campo AutoIncrement, la cual acceso desde Delphi con DBExpress de la forma TQuery <- TDataSetProvider <- TClientDataSet, cuando creó un solo registro no hay problema ya que al campo AutoIncrement no le incluyo información y el Post y luego ApplyUpdates no devuelven error. El asunto es cuando debo grabar 2 o más registros ya que en el primer Post se pone NULL en el AutoIncrement y lo graba, pero el segundo también lleva NULL y el Post arroja 'Key Violation'.

Alguien ha resuelto dicho inconveniente ...?

Saludos,

GustavoSV

[bulc]

Con RDBMS Firebird deberías usar un generador y un trigger basado en él. Otra solución es consultar el generador para extraer el nº que guarda e incrementarlo para luego colocarlo en el Insert/Append que esté haciendo. Si se guarda el dato, entonces se incrementa el generador.
En comandos ISQL crearás un generador usando: CREATE GENERATOR GEN_TABLAX; // En principio será cero su valor.
Para ver el nº que guarda puedes usar la sentencia: SHOW GENERATORS; También puedes usar: SELECT GEN_ID(GEN_TABLAX,0)FROM RDB$DATABASE; // EL valor cero hace que no se altere el generador.
Para modificar (en más o menos +1 o -1) usa esta otra: SELECT GEN_ID(GEN_TABLAX,-1) FROM RDB$DATABASE;
Desde Delphi puedes consultar el generador y recoger el dato. Yo lo muestro en un TLabel desde la creación/activación de la aplicación. Así puedes recoger el dato del Label.
Por otro lado el Trigger es muy fiable, pero se ejecuta después de la edición. Puedes poner como condición que actúe cuando el dato sea Cero y colocar ese dato de modo inicial. Al guardar datos, si se encuentra un cero, se actualizará el generador y se colocará su contador automáticamente. Es lo bueno de los triggers!
Aquí va mi función que chequea el generador:

Código Delphi [-]
Function REVISA_GEN( IBQuery1: TIBQuery; PasaUno : Boolean ; Label3: TLabel) : Integer;
Var
  Avance : String;   // Si EsNuevo is True, then Avance is 1.
begin
    if PasaUno then Avance:='1' else Avance:='0';
    IBQuery1.Close;
    IBQuery1.SQL.Clear;
    IBQuery1.SQL.ADD('SELECT GEN_ID( GEN_WB,'+Avance+' ) FROM RDB$DATABASE');
    IBQuery1.Open;  // Primero se abre y luego se recoge el resultado;
    Result := IBQuery1.Fields[0].AsInteger;
    Label3.Caption:= IntToStr(Result);
    //Siempre que esta función se llama, se actualiza el Label3.
    //ShowMessage('Result from Funtion:  '+  IntToStr( Result));
end;

[gustavosv]

Gracias por el dato, voy a adaptarla a MySQL.

[gustavosv]

bueno explico un poco lo que me sucedía ...

uso MySQL y siempre he manejado las tablas con llave principal AUTOINCREMENT independiente de que tenga otra posible llave primaria, como por decir, el NroFactura, la razón, entiendo que es una buena práctica en cuanto a la estructuración de la BD.

en Delphi uso los componentes DBExpress TSQLQuery <- TDataSetProvider <- TClientDataSet, donde genero los campos persistentes del TQuery y a todos les quito el parámetro Required, adicionalmente al campo Id (AutoInc) le selecciono el ProviderFlags->pfInKey indicando que esa es la llave principal, de igual manera procedo en el TClientDataSet para finalmente ene l TDataSetProvider dejar la propiedad UpdateMode como 'upWhereKeyOnly'. Lo anterior para que las actualizaciones en las tablas se hagan usando la llave primaria (el Id AutoInc).

Como tengo la configuración antes descrita, al grabar un registro no le indicaba ningún valor al campo Id (AutoInc) esperando que el servidor se lo asignara automáticamente, lo cual funciona pero SOLO cuando trabajaba 1 registro (situación normal en mi caso) ya que al no indicar valor, el toma su valor por defecto (p.ej. NULL) y en el ApplyUpdates al grabar el registro físicamente el servidor le asignaba el Id automáticamente. Ahora al tener 2 o más registros como para el Id se mueve el valor por defecto, al hacer el segundo Post éste también tendría el mismo valor que el primero y produce el error 'Key Violation'.

[gustavosv]

bueno después de investigar y leer en varios artículos, lo he resuelto de la siguiente manera:
1. en lugar de dejar el campo Id (AutoIncr) sin valor, he dispuesto ponerle un valor único a cada registro usando para ello un número entero negativo garantizando que no se me confunda con algún otro número por ahí

Código Delphi [-]
K := -1;
....
begin
  ClientDataSet1.FieldByName('Id').AsInteger := K;
  ClientDataSet1.Post;
  Dec(K);
end;

2. el evento BeforeUpdateRecord del TDataSetProvider se ejecuta con la instrucción ApplyUpdates antes de hacer la actualización física en el servidor, allí se implementa un mecanismo para conocer cuál sería el próximo número del Id y se actualiza usando la propiedad NewValue, para que llegue al servidor con el número que corresponde

Código Delphi [-]
procedure TDM.Provider1BeforeUpdateRecord(Sender: TObject;
  SourceDS: TDataSet; DeltaDS: TCustomClientDataSet; UpdateKind: TUpdateKind;
  var Applied: Boolean);
var
  NroRgtro: Integer;
begin
  if UpdateKind = TUpdateKind.ukInsert then
  begin
    spIdMAX.ExecProc;  // procedimiento almacenado que obtiene el último número asignado al AutoInc
    NroRgtro := spIdMAX.Params.ParamByName('IdMAX').AsInteger;
    DeltaDS.FieldByName('Id').NewValue := NroRgtro + 1;
  end;
end;

[bulc]

Sí, creo que está bien. En vez de usar un Trigger usas un StoreProcedure. Además colocas un nº negativo provisional. Me parece un poco floreado, pero si te va bien, adelante. Tu solución abre nuevos horizontes y es original (dentro de mis conocimientos). Enhorabuena!!

[[AzidRain]]

En MySQL para obtener el último número asignado se debe usar LAST_INSERT_ID() el cual te garantiza devolverte el último número que se haya asignado a tu campo sin importar que haya pasado después. Si haces lo haces con el método de usar MAX corres el riesgo de que en el transcurso otro usuario haya insertado registros por lo que ya no será correcto el dato que obtengas.

[roman]

Empezado por gustavosv

al grabar un registro no le indicaba ningún valor al campo Id (AutoInc) esperando que el servidor se lo asignara automáticamente, lo cual funciona pero SOLO cuando trabajaba 1 registro (situación normal en mi caso) ya que al no indicar valor, el toma su valor por defecto (p.ej. NULL) y en el ApplyUpdates al grabar el registro físicamente el servidor le asignaba el Id automáticamente.

¿Y qué pasaba si en lugar de no indicarle valor le dabas el valor cero? Eso debería bastar para que el servidor asigne el autoinc adecuado.

// Saludos