Renvoie (auto) la valeur générée à partir de l'instruction d'insertion (pas d'identifiant, pas de return)

désolé, si le titre de la question est trompeur ou pas assez précis, mais je ne vois pas comment le requestr en une phrase.

Disons que nous avons une table où le PK est une string (les nombres de '100.000' à '999.999', la virgule est pour la lisibilité seulement). Disons aussi que le PK n'est pas séquentiellement utilisé.

Maintenant, je veux insert une nouvelle ligne dans la table en utilisant java.sql et montrer le PK de la ligne insérée à l'user. Comme le PK n'est pas généré par défaut (par exemple, insert des valeurs sans PK ne fonctionne pas, quelque chose comme generate_keys n'est pas disponible dans l'environnement donné) j'ai vu deux approches différentes:

dans deux instructions différentes, searchz d'abord une key suivante possible, puis essayez d'insert (et attendez qu'une autre transaction utilise la même key dans le time entre les deux instructions) – est-il valide de réessayer jusqu'au succès ou pourrait-il parameters / verrous aider ici? comment puis-je réaliser cela dans java.sql?

pour moi, c'est une solution décevante, à cause du comportement non-déterministe (peut-être pourriez-vous me convaincre du contraire), alors j'en ai cherché un autre:

insert avec une instruction select nestede qui search le PK suivant possible. En recherchant d'autres réponses sur la génération du PK moi-même, je me suis rapproché d'une solution de travail avec cette affirmation (en laissant de côté les transtypages de ssortingng en int):

INSERT INTO mytable (pk,othercolumns) VALUES( (SELECT MIN(empty_numbers.empty_number) FROM (SELECT t1.pk + 1 as empty_number FROM mytable t1 LEFT OUTER JOIN mytable t2 ON t1.pk + 1 = t2.pk WHERE t2.pk IS NULL AND t1.pk > 100000) as empty_numbers), othervalues);

cela fonctionne comme un charme et a (afaik) une solution plus prévisible et stable que ma première approche, mais: comment puis-je récupérer le PK généré à partir de cette déclaration? J'ai lu qu'il n'y a aucun moyen de returnner la ligne insérée (ou des colonnes) directement et la plupart des résultats google que j'ai trouvés, pointent vers les keys générées – même si ma key est générée, elle n'est pas générée par le SGBD directement, mais par ma déclaration.

Notez que le SGBD utilisé dans le développement est MSSQL 2008 et que le système de production est actuellement un DB2 sur AS / 400 (je ne sais pas quelle version), donc je dois restr proche des standards SQL. Je ne peux pas modifier la structure db de quelque façon que ce soit (par exemple, utiliser des keys générées, je ne suis pas sûr des procédures stockées).

DB2 for i autorise les keys générées, les procédures stockées et les fonctions définies par l'user – à peu près tout ce que SQL Server peut faire. La mise en œuvre exacte est différente, mais c'est ce que les manuels sont pour 🙂 Demandez à votre administrateur quelle version d'IBM i ils exécutent, puis click Infocenter pour plus de détails.

Le facteur contraignant est que vous ne pouvez pas modifier la design de la database; vous êtes bloqué avec des process apparemment multiples essayant d'INSÉRER tout en remplissant des «trous» dans l'espace de keys existant. C'est une noix très difficile à casser. Parce que vous ne pouvez pas modifier la design de database, il n'y a rien à faire sauf pour permettre et gérer les collisions PK. Il n'y a pas d'astuce SQL qui va aider – la manière SQL est d'avoir le DB générer le PK, pas l'application.

Il y a plusieurs alternatives à suggérer, dans le cas où certains changements sont autorisés. Tous ont des problèmes nécessitant une solution de contournement, mais cela est inévitable à ce stade en raison de la design de l'application.

Créez un file UDF que tous les clients INSERT utilisent pour récupérer le prochain file PK disponible. Utilisez un tableau des «numéros disponibles» et supprimez-les au fur et à mesure qu'ils sont émis.
Pré-INSÉRER tous les numéros disponibles. Forcer les clients à faire une mise à jour. Faites-les FETCH … POUR UPDATE où (rest de données = non renseigné). Ceci verrouillera la rangée, évitant des collisions aussi bien que rendra le PK immédiatement disponible.
Laissez la database et les autres programmes d'application en utilisant cette table telle quelle, mais requestz à votre process INSERT de dessiner à partir d'un bloc de keys qui a été mis de côté pour votre usage. Conservez le numéro disponible suivant dans une séquence SQL ou dans une zone de données IBM i. Cela ne fonctionne que s'il y a un très grand trou dans l'espace de keys qui n'est pas encore utilisé.