Leeren Text "" in MS-SQL Feld NOT NULL Problem

[berens]

Hallo zusammen,
ich sitze gerade daran, meine Software mit Access-Datenbank auf einen SQL skalieren zu können.

Siehe dazu auch https://www.delphipraxis.net/204437-...g-welches.html .

Um direkt in Zukunft Probleme wie dieses vermeiden zu können: https://www.delphipraxis.net/204363-...oder-null.html
lege ich nun auf dem Server direkt alle Felder aller Tabellen mit "NOT NULL" und "DEFAULT 0" oder "DEFAULT ''" an:

Delphi-Quellcode:

function CreateTable_Layout(_DatabaseType: TDatabaseType; q: TADOQuery): Boolean;
begin
  Result := False;
  try
    try
      q.Close;
      q.SQL.Clear;

      q.SQL.Add('CREATE TABLE Layout');
      q.SQL.Add('  (                                        ' );
      q.SQL.Add('ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL, ' );
      // viele weitere Felder
      q.SQL.Add('MeinText TEXT        NOT NULL DEFAULT '''' ' );
      q.SQL.Add('  )                                        ' );

      q.Prepared := True;
      q.ExecSQL;

      Result := True;
    finally
      q.Close;
      q.SQL.Clear;
    end;
  except
    on E: SysUtils.Exception do begin
      Log('CreateTable_Layout', M, E.Message, ws_SEVERITY_EXCEPTION);
    end;
  end;
end;

Das Problem ist, wenn ich einen leeren, aber -meine erachtens- nicht-NULL String speichern möchte, er meckert, dass keine NULL-Werte in diesem Feld zulässig sind. Scheinbar werden Felder vom Typ TEXT doch anders gespeichert als VARCHAR(255), und er wandelt leere Strings Server-intern in NULL um?

Delphi-Quellcode:

          
var
  t: string;
begin
  _Target.Insert;
  _Target.FieldByName('Foo').AsInteger := _Source.FieldByName('Foo').AsInteger;
  _Target.FieldByName('Bar').AsInteger := _Source.FieldByName('Bar').AsInteger;
  t := '';
  _Target.FieldByName('MeinText').AsString := t;
  _Target.Post; // Fehler: MeinText darf nicht NULL sein!

In anderen Beiträgen wurde geraten, die TEXT-Felder der Tabelle auf VARCHAR(MAX) zu ändern. Zwar glaube ich eigentlich nicht, dass "MeinText" jemals größer als ~65000 Zeichen werden sollte, da die Access-Datenbank aber mit dem "MEMO"-Feldtyp (Neuerdings bezeichnet als "Langer Text") bis k.a. wieviel MB/GB Daten aufnehmen konnte, würde ich nun ungern in mein Programm eine zusätzliche Fehlerquelle einbauen indem sich die Feldtypen in der Access-Datenbank von den Feldtypen auf dem SQL-Server unterscheiden.

Andererseits sind die meisten "TEXT"-Felder auf der neuen Server Datenbank wirklich so "irrelevant", dass sie wahrscheinlich in den Abfragen eh nicht namentlich gefiltert werden, und es letztendlich wirklich egal wäre, ob sie leer, NULL und gefüllt sind.

Dennoch geht es mir hier auch ein wenig um's Prinzip:
Wie kann ich leere Strings über TAdoQuery in einen MS-SQL Server speichern, bei dem das TEXT Feld NOT NULL DEFAULT "" ist?

Vielen Dank für Meldungen und Meinungen zu dem Thema!



Edit 1:
Es könnte an der Umwandlung von AsString liegen? So würde es gehen, schließlich ist #0 kein NULL String. Aber diese Nachverarbeitung an allen Stellen im Quelltext für alle Datenfelder vom Typ TEXT, *obwohl* ein DEFAULT-Wert vorgegeben ist, ist imo vom Aufwand her nicht begründbar.

Delphi-Quellcode:

  t := trim(_Source.FieldByName(MeinText).AsString);
  if trim(t) = '' then begin
    _Target.FieldByName(MeinText).AsString                   := #0;
  end else begin
    _Target.FieldByName(MeinText).AsString                   := t;
  end;

[p80286]

Das wichtigste steht wohl hier https://stackoverflow.com/questions/...char-data-type
Falls Du dich unbedingt an ACCESS orentieren willst, ist das ungefähr so als wolltest Du ein Auto mit Vergaser statt Einspritzanlage kaufen. Vergiss ACCESS und setze die neue DB auch neu auf!

Zitat:

Andererseits sind die meisten "TEXT"-Felder auf der neuen Server Datenbank wirklich so "irrelevant", dass sie wahrscheinlich in den Abfragen eh nicht namentlich gefiltert werden, und es letztendlich wirklich egal wäre, ob sie leer, NULL und gefüllt sind.

Wenn etwas irrelevant ist, dann hat es in einer DB nichts zu suchen! Diese Gedanken sollte man sich aber machen bevor man die DB definiert.

Gruß
K-H

[Delphi.Narium]

Wenn's irrelevant ist, kann's auch Null sein, dann ist "Not Null" eigentlich eher "Quatsch"

Wenn es Probleme gibt, spendiere der MS-SQL-Tabelle 'nen Trigger, der Dir einen Defaultwert in die Spalte schreibt.

Definiere die Spalte als NOT NULL und sorge datenbankseitig bei geliefertem Null, Leerstring, ..., für eine Dir entsprechende Ersetzung. Wobei ein #0 als Not-Null-Equivalent für 'nen Leerstring eigentlich nicht so wirklich meinen Vorstellungen entsprechen würde.

Wenn der Text eigentlich eher nicht oder nur sehr sehr sehr selten in 'ner Abfrage vorkommen kann, dann kann man dort auf den Inhalt, Teilstrings mit Like abfragen. Das macht keine Probleme, wenn die Spalte nicht als Not Null definiert ist. Man kann aber die leeren Sätze mit Is Null abfragen und muss da dann nicht nach (1 bis n) Leerzeichen, #0 oder sonstwas abfragen. Das ist meiner Meinung nach unnütz kompliziert (und garantiert fehleranfällig).

Wenn in einer Spalte nix drinne sein darf, dann ist dieses Nix eben Null und nicht irgendwas anderes, was ich da reinbekommen kann (obwohl ich es eigentlich sowieso so gut wie fast nie benötige), was wie Nix aussieht, aber irgendwie doch was ist.
Eine derartige Lösung bekäme bei mir das Prädikat: Schlecht.

[berens]

Danke für den Link. Dass TEXT deprecated ist, ist einerseits natürlich 'ne wichtige Info, andererseits macht es mir auch die Entscheidung leichter. Ich nehme dann VARCHAR(max), dann sind die kurzen und langen Stringfelder wenigstens auch vom grundlegend selben Datentyp, und hoffe, dass die generierten Inhalte da auch wirklich alle reinpassen. Wenn das aktuell wirklich 2 GB sind statt 65'000 Zeichen, ist das definitiv kein Problem. Wobei ich realisitisch auch eigentlich erwarte, dass die Inhalte für diesen Feldtypen niemals 65'000 Zeichen überschreiten, das wären knapp 100 vollgeschriebene DIN A4 Seiten, und soviel gibt der Benutzer niemals für unseren Verwendungszweck ein.

> Wenn etwas irrelevant ist, dann hat es in einer DB nichts zu suchen! Diese Gedanken sollte man sich aber machen bevor man die DB definiert.
Sorry, das war falsch formuliert. Zwar ist es durchaus pro Zeile ein rein optionales Feld, dennoch wird es im Programm (und somit auch der Datenbank) gebraucht. Das "irrelevant" bezog sich ausschließlich auf das hier genannte NULL <--> "" Problem https://www.delphipraxis.net/204363-...oder-null.html bei Abfragen ("Zeige alle Datensätze, deren Feld nicht leer oder NULL ist"), da das "MeinText"-Feld niemals Filterkriterium ist. Das Feld wird zwar bei allen betroffenen Zeilen der SELECT Abfrage ausgelesen, um die -optional- gespeicherten Werte zu verarbeiten - ob da jetzt aber NULL, "" oder ein Wert drinnen steht, ist als solches egal: bei NULL oder "" werden die Standardwerte (vom Programm, nicht vom SQL-Server) verwendet.

Mir ging es ursächlich darum, eben in diesem neuen Datenbanksystem global das Dilemma mit NULL <--> "" bei Abfragen vermeiden zu können, indem ich die Datenbank entsprechend designe. Dass es einem jetzt von der anderen Seite ("Beim Schreiben WERDEN leere Text == NULL) wieder um die Ohren fliegt, ist schier zum kotzen.

Die Verwendung von VARCHAR(MAX) sollte dann aber hoffentlich wirklich BEIDE Probleme lösen: durch DEFAULT '' sollte ist es bis jetzt scheinbar egal, wenn leere nicht-null Strings in die Datenbank gespeichert werden, bzw. das Feld gar nicht gesetzt wird; es greift ja dann der Standardwert. Und auch die Abfragen in den anderen Tabellen, wo ich dann wieder sage: SELECT * FROM Bla WHERE TemplateName <> ""; sollten garantiert fehlerfrei laufen, da ich hier NULL-Werte nicht berücksichtigen muss, da es keine geben kann. Richtig so?

Ich denke/hoffe das Problem gilt damit als gelöst. Vielen Dank!


Edit:
@Delphi.Narium
Danke für den Beitrag. Ohne die Diskussion jetzt über (Un-)Sinn von "NOT NULL" führen zu wollen, geht es mir eigentlich darum, dass ich ja definitiv einen Standardwert in der Datenbank für dieses Feld gesetzt habe (leerer String "", nicht NULL!), und über _Target.FieldByName(MeinText).AsString := ''; ja auch einen GÜLTIGEN leeren String (nicht: NULL bzw. in Delphi NIL!) übergebe, und Dieser dann fälschlicherweise von der Drittanbieter-Komponente oder dem Server intern umgewandelt wird zu NULL, was imo nicht sein darf.

[Delphi.Narium]

Hab' nochmal die Suchmaschine meiner Wahl 'ne ganze Weile "gequält". Dabei bin ich auf dashier gestoßen: Unable to pass empty string into non-null database field
Wenn ich das dort recht verstehe, wird der Fehler von Delphi (ADO) verursacht und zwar dann, wenn eine Spalte vom Typ ftMemo ist (was ja bei einem Text-Blob zu erwarten ist). Wenn man den Typ auf ftString ändert, tritt das Problem nicht mehr auf.

Wäre in dem Text ein Lösungsansatz für Dich zu finden?

[berens]

@Delphi.Narium: Vielen Dank, die Problembeschreibung unter diesem Link trifft den Nagel auf den Kopf.

Ich habe zwischenzeitlich schon die Tabelle mit dem besagten Feld als VARCHAR(MAX) neu erstellt, und habe das identische Problem wie vorher. Selbst in der einfachst-möglichen Form:

Delphi-Quellcode:

      _Target.SQL.Add('SELECT TOP 1 * FROM Layout');
      _Target.Open;

      _Target.Insert;
      _Target.FieldByName('Bla').AsInteger  := Random(65000);
      _Target.FieldByName('Text2').AsString := '';
      _Target.FieldByName('MeinText').AsString := '';
      _Target.Post;

Tritt das Problem wieder auf. Jedoch NICHT bei "Text2", denn das ist VARCHAR(255). Wie unter dem Link beschrieben und von mir auch so festgestellt, funktioniert es, solange das Feld mit VARCHAR nicht MAX ist. Selbst mit VARCHAR(7500) geht es, aber das ist mir dann doch echt zu knapp von der Zeichenanzahl her, also kommt ich von ftMemo als solches nicht weg, denn:
Der DataType des besagten Feldes ist übrigens tatsächlich bei VARCHAR(7500) noch ftString, bei VARCHAR(MAX) dann ftMemo. Dies ist aber eine Nur-Lesen Eigenschaft und kann durch das AdoQuery nicht verändert werden.

Auf der verlinkten Seite wird geraten, einen anderen Provider für die AdoConnection zu verwenden (den nativen vom SQL Server 12). Jetzt weiß ich jedoch nicht, ob der bei allen Windows 10 Installationen von Hause aus mit dabei ist, denn sowas will ich nun bei allen Kunden nicht auch noch extra installieren müssen. Zudem kann es sein, dass Kunden die Datenbank evtl. auf einem bestehenden Server installieren möchten, und wenn der < Version 12 ist (von wann ist die?) habe ich sehr wahrscheinlich wieder unerwartete nicht vorhersehbare Probleme beim erstellen und migrieren der Datenbank(?). In einem weiteren Beitrag wurde aber gesagt, dass dieser besagte Provider jetzt schon wieder depracated ist, und MS zurück zu OLEDB geht.

Code:

The bug is in BufferToVar -> Data := Variant(Buffer^);. – kobik Jan 29 '18 at 8:29

Äh, kann ich da sebst ne Prozedur schreiben, die das richtig Umwandelt?

Bzw. da wären wir ja schon wieder bei meiner scherzhaft gemeinten Lösung, bei "leeren String" einfach #0 in die DB schreiben zu lassen. Bessere Ideen?

[Delphi.Narium]

VarChar(Max - 1)?

Edit:

In meinem ollen Delphi 7 finde ich in der ADODB.pas folgendes:

Delphi-Quellcode:

procedure TCustomADODataSet.SetFieldData(Field: TField; Buffer: Pointer; NativeFormat: Boolean);

  procedure BufferToVar(var Data: OleVariant);
  begin
    case Field.DataType of
      ftString, ftFixedChar, ftGuid:
        Data := WideString(PChar(Buffer));
      ftWideString:
        Data := WideString(Buffer^);
      ftAutoInc, ftInteger:
        Data := LongInt(Buffer^);
      ftSmallInt:
        Data := SmallInt(Buffer^);
      ftWord:
        Data := Word(Buffer^);
      ftBoolean:
        Data := WordBool(Buffer^);
      ftFloat, ftCurrency:
        Data := Double(Buffer^);
      ftBlob, ftMemo, ftGraphic, ftVariant:
        Data := Variant(Buffer^);
      ftInterface:
        Data := IUnknown(Buffer^);
      ftIDispatch:
        Data := IDispatch(Buffer^);
      ftDate, ftTime, ftDateTime:
        if NativeFormat then
          DataConvert(Field, Buffer, @TVarData(Data).VDate, False) else
          Data := TDateTime(Buffer^);
      ftBCD:
        if NativeFormat then
          DataConvert(Field, Buffer, @TVarData(Data).VCurrency, False) else
          Data := Currency(Buffer^);
      ftBytes, ftVarBytes:
        if NativeFormat then
          DataConvert(Field, Buffer, @Data, False) else
          Data := OleVariant(Buffer^);
      ftLargeInt:
        Data := LargeInt(Buffer^);
      else
        DatabaseErrorFmt(SUsupportedFieldType, [FieldTypeNames[Field.DataType],
          Field.DisplayName]);
    end;
  end;

var
  Data: OleVariant;
  RecBuf: PChar;
begin
  with Field do
  begin
    if not (State in dsWriteModes) then DatabaseError(SNotEditing, Self);
    GetActiveRecBuf(RecBuf);
    if FieldNo > 0 then
    begin
      if ReadOnly and not (State in [dsSetKey, dsFilter]) then
        DatabaseErrorFmt(SFieldReadOnly, [DisplayName]);
      Validate(Buffer);
      if FModifiedFields.IndexOf(Field) = -1 then
        FModifiedFields.Add(Field);
    end;
    if Buffer = nil then
      Data := Null else
      BufferToVar(Data);
    PVariantList(RecBuf+SizeOf(TRecInfo))[Field.Index] := Data;
    if not (State in [dsCalcFields, dsInternalCalc, dsFilter, dsNewValue]) then
      DataEvent(deFieldChange, Longint(Field));
  end;
end;

In BufferToVar ist eine "Case-Kaskade".

Äquivalent in Deiner Delphiversion suchen und dann "einfach" ftMemo an der vorhandenen Stelle "rausschmeißen" und bei ftString hinzufügen.

Das in 'ner eigenen "Variante" der entsprechenden Unit anpassen und diese, statt des Originals einbinden.

Sprich: Original umbenennen und durch Deine Variante ersetzen

oder

die eigene Variante ins Projekt aufnehmen, im Bibliothekspfad, Suchpfad, ... so platzieren, dass sie vor dem Original vom Compiler gefunden wird.

Am Sinnvollsten wird es wohl dann sein, die eigene Variante ins Projekt aufzunehmen und ins Projektverzeichnis zu legen, damit andere Projekte davon unberührt bleiben.

[berens]

> VarChar(Max - 1)?
Leider nein, denn MAX ist für den Server ein Schlüsselwort und keine berechenbare Konstante, von der man einfach 1 abziehen kann.

Code:

---------------------------
Microsoft SQL Server Management Studio
---------------------------
Setting for Length must be from 1 to 8000.


---------------------------
OK   Hilfe   
---------------------------

Wäre es nicht denkbar, statt mit .AsString anders auf das Datenfeld zuzugreifen, und diese fehlerhafte Umwandlung zu umgehen? AsVariant und AsWideString haben beide leider nicht geklappt. TField.SetData wäre eine Möglichkeit, aber mit meinem fehlerhaften Wissen zu Pointern bekomme ich das ad hoc nicht hin:

Delphi-Quellcode:

      
var
  s: string;
...
  s := 'test';
  afield.SetData(@s); // geht aber tatsächlich gut, wenn s = ''. k.a. ob's dann aber wirklich in die DB geschrieben wird...

[Jumpy]

Kann man nicht einfach NULL in das Feld schreiben lassen und das dann nachher mit einem Update per SQL auf Leerstring setzen? Es geht doch nur im die Migration und nicht den dauerbetrieb, oder?

[berens]

Das Problem ist, dass es bei AdoQuery1.Post eine Exception ausgelöst wird, und die Daten in's Nirvana verpuffen. Gerade in der alten Datenbank ist in diesem -optionalen- Feld ja selten etwas eingetragen.

Mir fällt gerade ein, dass ich ja zumindestet die folgende Abfrage machen: wenn der zu schreibende String leer ist, schreibe ihn einfach nicht. Denn lustigerweise greift DANN tatsächlich der Standardwert. Es knallt wirklich nur in dem einen konkreten Fall, dass ich AsString := '' setze. NACHTRAG: Nein, geht nicht: Wenn ich den Datensatz mit .Edit bearbeite, der Benutzer hat den Wert aus dem Feld gelöscht und ein leerer Wert soll geschrieben werde, bekommt er das Feld nie im Leben wieder gelöscht. Was soll das???

Das ist wirklich ein Bug von Delphi (ich habe 2010) mit den ADO-Komponenten, und in den vielen Seiten die ich eben im Browser aufhatte, wurde auch mal die QualityCentral verlinkt zu dem Thema. Leider hab ich die Seite zu früh geschlossen, und finde die nun nicht mehr.

Eigentlich wollte ich jetzt echt die neue Datenbank von Grund auf so "sauber" designen, dass ich mir nie wieder im Leben gedanken um SELECT abfragen mit oder ohne NULL-Werte machen muss (deshalb Standardwerte="") - Performance/Sinnhaftigkeit von NULL oder NOT NULL mal außen vor gelassen. Aber scheinbar führt wohl kein Weg dran vorbei, in den ehem. "TEXT" Feldern (nun VARCHAR(MAX) ) eben leider doch NULL-Werte zuzulassen und halt an ALLEN Stellen im Quelltext bei den Abfragen: WHERE BLA="" eben umzuändern auf WHERE (BLA="") OR (BLA IS NULL) ... Das macht's weder einfacher, noch übersichtlicher, noch "schöner", aber eh dass ich mich in zwei Jahren an unerwarteten Stellen damit rumärgern muss, dass beim Speichern eine Exception auftritt, weil der Benutzer ein Feld leer gelassen hat (was er darf!), mache ich das jetzt lieber so mit dieser Krücke.

Ja, NULL und NOT NULL sind eine Glaubensfrage. Ich persönlich sehe in NULL Werten ausschließlich die damit einhergehenden Probleme.