FB: Prepared Query mit In-Statement ?

[hoika]

Hallo,
ich habe eine Query, die wird 100mal ausgeführt.

Select Feld3 From Tab
Where
(Feld1=:Feld1) And
(Feld2 In :Feld2)

Feld1 ist Integer
Feld2 ein String oder String-Liste

Leider kann ich die Query nicht preparen wegen dem In.

Geht das irgendwie anders ??

[mkinzler]

Was steht den in dem Parameter Feld2?

[Delphi.Narium]

Warum geht das nicht?

Fehlermeldung?

Quelltext mit Parameterbefüllung und konkreten Beispieldaten?

[mkinzler]

IN unterstützt keine Parameter.

[Delphi.Narium]

Jain, so absolut stimmt das nicht.

Je Parameter darf nur ein Wert übergeben werden.

Dashier funktioniert:

Delphi-Quellcode:

  qry.Close;
  qry.SQL.Clear;
  qry.SQL.Add('select feld3 from tabelle where feld1 = :feld1 ');
  qry.SQL.Add('and feld2 in (:feld2) ');
  qry.Prepare;
  qry.Params[0].AsInteger := 1;
  qry.Params[1].AsString  := '''Feldinhalt''';
  qry.Open;
  ShowMessage(Format('%d',[qry.RecordCount]));
  qry.Close;

Dashier funktioniert ebenfalls:

Delphi-Quellcode:

  qry.Close;
  qry.SQL.Clear;
  qry.SQL.Add('select feld3 from tabelle where feld1 = :feld1 ');
  qry.SQL.Add('and feld2 in (:feld2a,:feld2b,:feld2c) ');
  qry.Prepare;
  qry.Params[0].AsInteger := 1;
  qry.Params[1].AsString  := '''FeldinhaltA''';
  qry.Params[2].AsString  := '''FeldinhaltB''';
  qry.Params[3].AsString  := '''FeldinhaltC''';
  qry.Open;
  ShowMessage(Format('%d',[qry.RecordCount]));
  qry.Close;

Dashier funktioniert jedoch nicht:

Delphi-Quellcode:

  qry.Close;
  qry.SQL.Clear;
  qry.SQL.Add('select feld3 from tabelle where feld1 = :feld1 ');
  qry.SQL.Add('and feld2 in (:feld2) ');
  qry.Prepare;
  qry.Params[0].AsInteger := 1;
  qry.Params[1].AsString  := '''FeldinhaltA'',''FeldinhaltB'',''FeldinhaltC''';
  qry.Open;
  ShowMessage(Format('%d',[qry.RecordCount]));
  qry.Close;

[hoika]

Hallo,
Fehlermeldung beim Prepare "Token ? unknown"

Feld2 ist eine variable Anzahl von Strings.

Ich habe hier IBExpert und IBDAC.

Das das Prepare nicht geht, ist ja bekannt, wie könnte man das denn lösen?
Ich möchte ungern X prepared Queries (pro Anzahl der Strings eine) bauen, sehe das aber als einzige Lösung.

[Delphi.Narium]

Token unknown lässt auf einen Syntaxfehler im SQL schließen, vermutlich ist das aber nur eine Teilmenge der Fehlermeldung.
Bitte mal das vollständige SQL und die vollständige Fehlermeldung. So ist das nur was für die :glaskugel:

Ein Parameter = ein Wert.
Wenn ein Wert aus mehreren Zeichenfolgen besteht, geht das.

Wenn ein Parameter aber mehrere einzelne Werte enthält, die eigentlich separat ins IN sollen, dann geht das nicht.

Delphi-Quellcode:

qry.SQL.Text := 'select * from x where feld2 IN (:Parameter)';
qry.Params[0].AsString := 'Das ist jetzt mal etwas Text.';

funktioniert.
Das wird zu

SQL-Code:

select * from x where feld2 IN ('Das ist jetzt mal etwas Text.')

, wobei hier das IN auch durch ein = ersetzt werden könnte.

Delphi-Quellcode:

qry.SQL.Text := 'select * from x where feld2 IN (:P1,:P2,:P3,:P4,:P5,:P6)';
qry.Params[0].AsString := 'Das'
qry.Params[1].AsString := 'ist'
qry.Params[2].AsString := 'jetzt'
qry.Params[3].AsString := 'mal'
qry.Params[4].AsString := 'etwas'
qry.Params[5].AsString := 'Text.'

funktioniert, kann aber zu einem deutlich anderen Ergebnis, als die vorherige Variante, führen.

Delphi-Quellcode:

qry.SQL.Text := 'select * from x where feld2 IN (:Parameter)';
qry.Params[0].AsString := '''Das'',''ist'',''jetzt'',''mal'',''etwas'',''Text.''';

funktioniert eventuell technisch. Im IN steht aber nur ein Wert, nach dem gesucht werden soll, und nicht sechs Werte, wie der Aufbau des Parameters suggerieren könnte. Und wenn hier die Anzahl der ' nicht so ganz passt, könnte die daraus resultierende Fehlermeldung auch 'Token unknown' enthalten.

[DeddyH]

Das IN ist ja faktisch nur eine Kurzform für mehrere OR-verknüpfte Werte. Und mehrere Werte bekommt man eben nicht dynamisch parametrisiert. Klingt blöd, is aber so.

[hoika]

Hallo,
der Fehler kommt bereits beim Prepare,
hat also nichts mit der Anzahl der Strings im Parameter zu tun.

FB macht das schon richtig so, dass es sagt, IN kann nicht prepared werden.


Token unknown lässt auf einen Syntaxfehler im SQL schließen, vermutlich ist das aber nur eine Teilmenge der Fehlermeldung.

Nein, ohne das IN funktioniert es.

[Delphi.Narium]

Zitat:

Zitat von hoika (Beitrag 1389890)

Select Feld3 From Tab
Where
(Feld1=:Feld1) And
(Feld2 In :Feld2)

Das geht nicht, nur so:

SQL-Code:

Select Feld3 From Tab
Where 
  (Feld1=:Feld1) And
  (Feld2 In (:Feld2))

Die Klammern dürfen nicht Teil des Parameterinhaltes sein. Ist dem so, so ist "Token unknown" ein Teil der Fehlermeldung.

Vollständig dürfte sie in etwa

Zitat:

SQL Error: Dynamic SQL Error SQL error code = -104 Token unknown - line 1, column 35 ?. Error Code: -104. Invalid token The SQL: select id from tabbel where id in ?;

lauten.

Zitat:

Zitat von hoika (Beitrag 1389890)

Hallo,
der Fehler kommt bereits beim Prepare,
hat also nichts mit der Anzahl der später tatsächlichen Parameter zu tun.

FB macht das schon richtig so, dass es sagt, IN kann nicht prepared werden.

Doch, das hat mit den Parametern zu tuen. Die sind in Deinem Statement schlicht und einfach falsch und deshalb hat Prepare kein gültiges SQL, das es später mit Parametern versorgen kann.

Um Die Syntax für das Prepare zu prüfen, gehe ich zum Testen immer her und mache um die Parameter Hochkommata.

Also

SQL-Code:

Select Feld3 From Tab
Where 
  (Feld1=':Feld1') And
  (Feld2 In ':Feld2')

bei Deinem Statement. Das wird auch scheitern, da es ein ungültiges SQL ist. Die Klammern beim IN fehlen!!!
Parameter können nur Werte enthalten, aber nicht Teile des SQLs. Die Klammern sind aber Teile des SQLs.

SQL-Code:

Select Feld3 From Tab
Where 
  (Feld1=:Feld1) And
  (Feld2 :Feld2)

Delphi-Quellcode:

qry.params[0] := 'in (1,2,3)';

geht ja auch nicht oder gar

SQL-Code:

Select Feld3 From Tab
Where 
  (Feld1=:Feld1) And
  (:Feld2)

Delphi-Quellcode:

qry.params[0] := 'feld2 in (1,2,3)';

geht auch nicht.

[jobo]

"100 mal eine Query"

Klingt etwas nach der berühmten Whileschleife für Queries.

Häufig kann man das IN zu einem JOIN auflösen. Das hilft nicht unbedingt weiter, außer in einem speziellen Fall. Wenn nämliche die vielen IN Werte alle an einem oder wenigen "Parent" Werten einer übergeordneten Tabelle hängen.
Gegenanzeige wäre bspw. das in dem IN disjunkte Werte aus einer Suchmaske stecken.

Wenn also die Zahl 100 darauf hindeutet, das in dieser 100er Schleife eine Dimension oder Menge durchgearbeitet wird, die man auch insgesamt in eine "große" Query auflösen kann, wäre das eine mögliche Lösung, das IN fliegt dann idealer Weise raus.

Also Frage wäre: Gibt es zu den 100 Abfragen eine Master Query?
Und wenn ja, wie sieht die aus?

Handarbeitslösung:
Die IN Werte in eine (temporäre) User spezifische Tabelle eintragen und die jetzige Query dagegen joinen. Oder bei einer bekannten Maximalanzahl von IN Werten, z.B. 5, mit 5 verschiedenen Queries arbeiten und jeweils die Query starten, die dazu dieser Parameter Anzahl entspricht.
Oder den fixen Teil der Query in einen View packen und auf Parametrieung und Prepare der IN Anweisung verzichten (dann ist wenigsten der View fertig prepared).

[IBExpert]

Zitat:

Zitat von hoika (Beitrag 1389890)

Geht das irgendwie anders ??

Hängt ein wenig von der Datenmenge ab, geht aber immer auch anders (ich hab mal die params P genannt)

1. bei kleineren Datenmengen so

Code:

Select Feld3 From Tab
Where 
(Feld1=:P1) And
(:P2 containing ';'||Feld2||';' )

dabei musst du dann einfach deinen Parameter P2 als semikolon getrennte Liste mit führendem und abschliessendem
Semikolon als String übergeben, z.B. so

Code:

qry.Params[0].AsInteger := 123;
qry.Params[1].AsString := ';a;b;x;';

Das sollte klappen, ist aber von der Performance nur für kleine Datenmenge sinnvoll, weil es garantiert einen
Full Table Scan macht

2. Je nach Netzwerkspeed und Anzahl an Parametern sollte man die Vorteile von prepare nicht überschätzen
und einfach den kompletten SQL so wie du den brauchst mit konstanten im SQL Text übertragen , d.h.

Code:

Select Feld3 From Tab
Where 
(Feld1=123) And
(Feld2 in ('a','b','x') )

3. Die optimalere Lösungs ggf auch für große Datenmenge
Erstelle eine Global Temporary Table mit on commit delete rows wo nur mögliche Parameter
reinkommen, Index anlegen ggf nicht vergessen
und packe deine Parameter in die Tabelle

Code:

CREATE GLOBAL TEMPORARY TABLE BRPARRAY (
    VAL   VARCHAR(80) NOT NULL PRIMARY KEY
) ON COMMIT DELETE ROWS;
commit;
INSERT INTO BRPARRAY VALUES ('a');
INSERT INTO BRPARRAY VALUES ('b');
INSERT INTO BRPARRAY VALUES ('x');
--achtung hier kein commit, sonst ist wegen on commit delete rows die tabelle wieder leer

Select Feld3 From Tab
join brparray on brparray.val=tab.feld2 
Where 
(Feld1=123)

In der Global Temporary Table GTT siehst du immer nur die Daten deiner eigenen Connection.
Mit on commit delete rows ist die nach dem commit leer
Mit on commit preserve rows ist die auch nach dem commit noch gefüllt, dann ggf einfach
selber entscheiden wann gelöscht werden soll.


Wir nutzen GTT zB dafür, das beim Programmstart die Userrechte und Translations einmalig
ausgelesen werden und immer in den GTTs drin sind, man also bei jeder Abfrage zB PLZ
Werte entsprechend der eigenen Rechte da drin zu haben und jede Abfrage kann immer an
eine oder mehrere passende GTTs gejoint werden.

[hoika]

Hallo,
die 100 habe ich mal so hingeschrieben.
Es können auch mehr sein.
Die betreffende Query hat noch keinen Outer Join,
der macht das Umbauen in eine einzige Query noch viel langsamer.

Das Containing klingt doch schon mal gut.
Aber das kann doch nicht stimmen,
ich habe z.B. 12 und 12B in der Datenbank,
suche ich jetzt über Containg ;12;, wird auch das 12B gefunden, richtig?

Das könnte ich aber über eine nachträgliches If beim Durchlaufen der Query lösen.

Ein Full Table Scan ist es nicht, weil Field1 indiziert ist.

PS:
Der Code ist alt. Ich versuche ihn zu Optimieren,
ohne alles neu schreiben zu müssen.

[p80286]

Zitat:

Zitat von hoika (Beitrag 1389936)

Der Code ist alt. Ich versuche ihn zu Optimieren,
ohne alles neu schreiben zu müssen.

Der Versuch ist nicht strafbar, aber trotzdem hätte ich ein schlechtes Gefühl dabei. Auch mich macht der mehrfache Aufruf erst einmal stutzig. Du solltest vielleicht eine Ebene höher ansetzen und Deine Abfragen hinterfragen, statt an ihrer Syntax zu basteln.

Gruß
K-H

[hoika]

Hallo,
das Problem ist nicht die Abfrage, sondern der dahinterliegende Code.
Da habe ich einfach keine Lust, den zu ändern.

Der Mehrfachaufruf kommt halt von der prozeduralen Rangehensweise:
- ermittle Anzahl der Gruppenmitglieder
- ermittle pro Gruppenmitglied diverse Daten (for-Schleife)
je nach Art des Mitgliedes sind es andere Daten

[Delphi.Narium]

Verzichte auf das Prepare und baue das SQL jeweils zusammen.

Die Verwendung von IN im Zusammenhang mit Parametern ist eher grenzwertig, insbesondere dann, wenn die Menge der Werte innerhalb des IN von Statement zu Statement variiert.

[hoika]

Hallo,
genauso läuft es ja jetzt.
Ich habe etwa 500 variierende Mitglieder in einer Personengruppe.
Jedes Mitglied hat 1 bis x Aktivitäten, die angezeigt werden sollen.
Von den 500 Mitgliedern sind immer so 100-200 aktiv.

[jobo]

Zitat:

Zitat von hoika (Beitrag 1389954)

das Problem ist nicht die Abfrage, sondern der dahinterliegende Code.
Da habe ich einfach keine Lust, den zu ändern.

Der Mehrfachaufruf kommt halt von der prozeduralen Rangehensweise:
- ermittle Anzahl der Gruppenmitglieder
- ermittle pro Gruppenmitglied diverse Daten (for-Schleife)
je nach Art des Mitgliedes sind es andere Daten

Ich will nicht nerven, aber mein Geschwafel weiter vorne dreht sich genau darum. Die Idee ist die Iteration im Code zu entfernen bzw. zurückzubauen, Ermittlung der Anzahl und Aktivitäten der Mitglieder in einem "großen" Statement.
Die DB würde sich freuen, vielleicht sogar der Codeleser und Versteher.

SQL ist genau dafür geschaffen, Daten zu verknüpfen und zu transformieren. Für eine prozedurale Lösung- zur Datenzusammenstellung wohlgemerkt- gibt es nur selten Argumente.
Eine Fall basierte Business Logik, die anhand der Daten dann unterschiedliche Aktivitäten(! nicht Selektionen) startet, ist im Client gut aufgehoben. (Geht auch anders, ist aber hier nicht das Thema)
Guten Rutsch!

[IBExpert]

Zitat:

Zitat von hoika (Beitrag 1389936)

ich habe z.B. 12 und 12B in der Datenbank,
suche ich jetzt über Containg ;12;, wird auch das 12B gefunden, richtig?

nein, wenn sich das auf meine Lösung bezieht, weil um den wert aus der Tabelle, mit dem
im parameter gesucht wird, ja immer ein Semikolon davor und dahinter dynamisch ergänzt ist

';12B;13;13A;' containing ';12;' ist false

du musst deinen parameter sauber mit Trenner zusammenbauen

[hoika]

Hallo,
Jobs
ja, will ich aber nicht :)

Holger
dann verstehe ich das Containing nicht
Ah, auch das Select anpassen!

[hoika]

Hallo,
so, ich hatte jetzt mal Zeit, das Prepare nach Holgers Anleitung einzubauen.

Ergebnis:
nur minimale Verbesserung der Performance

Die Anzahl der Queries ist hier halt doch entscheidend.