Moin,
hier noch mal einen kleinen Denkanstoß zum eigentlichen Thema.
Wir hatten vor einiger Zeit mal ähnliche Konstellation und haben dann einfach auf
Firbeird basierend einen minimale
API realisiert, die nicht anderes ist als eine
Stored procedure, der man den auszuführenden Befehl als Blob übergibt und die dann
ggf sich daraus ergebende Resultsets in einem Blob zurückgibt.
Hier der Quellcode der SP
Code:
create or alter procedure BRPAPI (
CMD blob sub_type 1 segment size 80)
returns (
RES blob sub_type 1 segment size 80)
as
declare variable RESLINE blob sub_type 1 segment size 80;
begin
if (cmd starting with 'SELECT') then
begin
res='';
for execute statement :cmd into :resline
do res=res||'
'||resline;
end
else
begin
execute statement :cmd;
res='EXECUTED';
end
suspend;
end
Wie benutzt man die dann? Auch recht simpel, z.B.
Code:
select res from brpapi('SELECT ID||'';''||TXT FROM KUNDE')
Die Antwortmenge wird durch das
SQL einfach schon mal als
CSV Format vorgegeben, damit müsste dann ggf der Client klar kommen.
Hier für Interessierte ein Protokoll aus dem TCPIPExpert Tool mit dem gesamten
TCP/
IP Traffic zu einer
SQL Abfrage mit Übergabe
der Ergebnismenge (Ausführung war hier mit isql.exe)
Code:
## 18:59:59:136 Channel 1; DATA (Client -> Server): Length= 8
00 00 00 5B 00 00 00 02 [
## 18:59:59:435 Channel 1; DATA (Client -> Server): Length= 124
00 00 00 3E 00 00 00 00 00 00 00 44 00 00 00 02 > D
FF FF FF FF 00 00 00 03 00 00 00 3A 73 65 6C 65 :sele
63 74 20 72 65 73 20 66 72 6F 6D 20 62 72 70 61 ct res from brpa
70 69 28 27 53 45 4C 45 43 54 20 49 44 7C 7C 27 pi('SELECT ID||'
27 3B 27 27 7C 7C 54 58 54 20 46 52 4F 4D 20 4B ';''||TXT FROM K
55 4E 44 45 27 29 00 00 00 00 00 19 15 04 07 09 UNDE')
0B 0C 0D 0E 10 11 12 13 08 05 07 09 0B 0C 0D 0E
10 11 12 13 08 00 00 00 FF FF 80 00
## 18:59:59:464 Channel 1; DATA (Server -> Client): Length= 156
00 00 00 09 00 00 00 03 00 00 00 00 00 00 00 01
00 00 00 00 00 00 00 01 00 00 00 00 00 00 00 00
00 00 00 09 00 00 00 04 00 00 00 00 00 00 00 00
00 00 00 5A 15 04 00 01 00 00 00 04 07 04 00 01 Z
00 00 00 09 04 00 01 00 00 00 0B 04 00 09 02 00
00 0C 04 00 01 00 00 00 0D 04 00 04 00 00 00 0E
04 00 08 00 00 00 10 03 00 52 45 53 11 06 00 42 RES B
52 50 41 50 49 12 06 00 53 59 53 44 42 41 13 03 RPAPI SYSDBA
00 52 45 53 08 05 07 04 00 00 00 00 00 01 00 00 RES
00 00 00 01 00 00 00 00 00 00 00 00
## 18:59:59:466 Channel 1; DATA (Client -> Server): Length= 56
00 00 00 3F 00 00 00 03 00 00 00 01 00 00 00 00 ?
00 00 00 00 00 00 00 00 00 00 00 41 00 00 00 03 A
00 00 00 0C 05 02 04 00 02 00 09 00 07 00 FF 4C L
00 00 00 00 00 00 0A A8
## 18:59:59:496 Channel 1; DATA (Server -> Client): Length= 68
00 00 00 09 00 00 00 01 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 01 00 00 00 00 00 00 00 00
00 00 00 42 00 00 00 00 00 00 00 01 00 00 00 00 B
00 00 00 33 00 00 00 00 00 00 00 42 00 00 00 64 3 B d
00 00 00 00
## 18:59:59:503 Channel 1; DATA (Client -> Server): Length= 20
00 00 00 38 00 00 00 00 00 00 00 01 00 00 00 00 8
00 00 00 33 3
## 18:59:59:530 Channel 1; DATA (Server -> Client): Length= 32
00 00 00 09 00 00 00 04 00 00 00 00 00 00 00 01
00 00 00 00 00 00 00 01 00 00 00 00 00 00 00 00
## 18:59:59:532 Channel 1; DATA (Client -> Server): Length= 16
00 00 00 24 00 00 00 04 00 00 40 00 00 00 00 00 $ @
## 18:59:59:562 Channel 1; DATA (Server -> Client): Length= 216
00 00 00 09 00 00 00 02 00 00 00 00 00 00 00 01
00 00 00 B6 A0 00 0D 0A 34 30 30 30 30 30 31 39 40000019
33 37 34 3B 42 61 72 0D 0A 34 30 30 30 30 30 31 374;Bar 4000001
39 34 30 38 3B 47 65 6D 65 69 6E 64 65 20 48 75 9408;Gemeinde Hu
64 65 0D 0A 34 30 30 30 30 30 31 39 36 32 31 3B de 40000019621;
57 53 0D 0A 34 30 30 30 30 30 31 39 38 30 38 3B WS 40000019808;
2D 0D 0A 34 30 30 30 30 30 32 30 36 39 33 3B 4B - 40000020693;K
61 72 73 74 65 6E 20 44 69 65 72 73 20 47 6D 62 arsten Diers Gmb
48 0D 0A 34 30 30 30 30 30 32 33 34 30 36 3B 46 H 40000023406;F
6C 6F 72 65 73 20 41 70 6F 74 68 65 6B 65 0D 0A lores Apotheke
34 30 30 30 30 30 32 39 37 35 34 3B 44 52 4B 20 40000029754;DRK
48 75 64 65 0D 0A 12 00 34 30 30 30 30 30 33 30 Hude 40000030
38 36 31 3B 5A 6F 72 62 61 73 00 00 00 00 00 01 861;Zorbas
00 00 00 00 00 00 00 00
## 18:59:59:571 Channel 1; DATA (Client -> Server): Length= 36
00 00 00 27 00 00 00 04 00 00 00 5B 00 00 00 01 ' [
00 00 00 43 00 00 00 03 00 00 00 01 00 00 00 5B C [
00 00 00 01
## 18:59:59:598 Channel 1; DATA (Server -> Client): Length= 32
00 00 00 09 00 00 00 00 00 00 00 00 00 00 00 01
00 00 00 00 00 00 00 01 00 00 00 00 00 00 00 00
hier noch mal mit anderem
SQL
Code:
## 19:00:40:601 Channel 1; DATA (Client -> Server): Length= 144
00 00 00 5B 00 00 00 02 00 00 00 44 00 00 00 02 [ D
00 00 00 03 00 00 00 03 00 00 00 4F 73 65 6C 65 Osele
63 74 20 72 65 73 20 66 72 6F 6D 20 62 72 70 61 ct res from brpa
70 69 28 27 53 45 4C 45 43 54 20 49 44 7C 7C 27 pi('SELECT ID||'
27 3B 27 27 7C 7C 54 58 54 20 46 52 4F 4D 20 4B ';''||TXT FROM K
55 4E 44 45 20 57 48 45 52 45 20 49 44 3D 34 30 UNDE WHERE ID=40
30 30 30 30 32 30 36 39 33 27 29 00 00 00 00 19 000020693')
15 04 07 09 0B 0C 0D 0E 10 11 12 13 08 05 07 09
0B 0C 0D 0E 10 11 12 13 08 00 00 00 FF FF 80 00
## 19:00:40:631 Channel 1; DATA (Server -> Client): Length= 156
00 00 00 09 00 00 00 03 00 00 00 00 00 00 00 01
00 00 00 00 00 00 00 01 00 00 00 00 00 00 00 00
00 00 00 09 00 00 00 04 00 00 00 00 00 00 00 00
00 00 00 5A 15 04 00 01 00 00 00 04 07 04 00 01 Z
00 00 00 09 04 00 01 00 00 00 0B 04 00 09 02 00
00 0C 04 00 01 00 00 00 0D 04 00 04 00 00 00 0E
04 00 08 00 00 00 10 03 00 52 45 53 11 06 00 42 RES B
52 50 41 50 49 12 06 00 53 59 53 44 42 41 13 03 RPAPI SYSDBA
00 52 45 53 08 05 07 04 00 00 00 00 00 01 00 00 RES
00 00 00 01 00 00 00 00 00 00 00 00
## 19:00:40:633 Channel 1; DATA (Client -> Server): Length= 56
00 00 00 3F 00 00 00 03 00 00 00 01 00 00 00 00 ?
00 00 00 00 00 00 00 00 00 00 00 41 00 00 00 03 A
00 00 00 0C 05 02 04 00 02 00 09 00 07 00 FF 4C L
00 00 00 00 00 00 0A A8
## 19:00:40:661 Channel 1; DATA (Server -> Client): Length= 68
00 00 00 09 00 00 00 01 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 01 00 00 00 00 00 00 00 00
00 00 00 42 00 00 00 00 00 00 00 01 00 00 00 00 B
00 00 00 3F 00 00 00 00 00 00 00 42 00 00 00 64 ? B d
00 00 00 00
## 19:00:40:669 Channel 1; DATA (Client -> Server): Length= 20
00 00 00 38 00 00 00 00 00 00 00 01 00 00 00 00 8
00 00 00 3F ?
## 19:00:40:697 Channel 1; DATA (Server -> Client): Length= 32
00 00 00 09 00 00 00 04 00 00 00 00 00 00 00 01
00 00 00 00 00 00 00 01 00 00 00 00 00 00 00 00
## 19:00:40:698 Channel 1; DATA (Client -> Server): Length= 16
00 00 00 24 00 00 00 04 00 00 40 00 00 00 00 00 $ @
## 19:00:40:727 Channel 1; DATA (Server -> Client): Length= 68
00 00 00 09 00 00 00 02 00 00 00 00 00 00 00 01
00 00 00 24 02 00 0D 0A 1E 00 34 30 30 30 30 30 $ 400000
32 30 36 39 33 3B 4B 61 72 73 74 65 6E 20 44 69 20693;Karsten Di
65 72 73 20 47 6D 62 48 00 00 00 01 00 00 00 00 ers GmbH
00 00 00 00
## 19:00:40:734 Channel 1; DATA (Client -> Server): Length= 16
00 00 00 27 00 00 00 04 00 00 00 5B 00 00 00 01 ' [
## 19:00:40:735 Channel 1; DATA (Client -> Server): Length= 20
00 00 00 43 00 00 00 03 00 00 00 01 00 00 00 5B C [
00 00 00 01
## 19:00:40:760 Channel 1; DATA (Server -> Client): Length= 32
00 00 00 09 00 00 00 00 00 00 00 00 00 00 00 01
00 00 00 00 00 00 00 01 00 00 00 00 00 00 00 00
und hier mal als Update statement
Code:
## 19:01:49:811 Channel 1; DATA (Client -> Server): Length= 156
00 00 00 5B 00 00 00 02 00 00 00 44 00 00 00 02 [ D
00 00 00 03 00 00 00 03 00 00 00 5A 73 65 6C 65 Zsele
63 74 20 72 65 73 20 66 72 6F 6D 20 62 72 70 61 ct res from brpa
70 69 28 27 55 50 44 41 54 45 20 4B 55 4E 44 45 pi('UPDATE KUNDE
20 53 45 54 20 54 58 54 3D 27 27 4B 41 52 53 54 SET TXT=''KARST
45 4E 20 44 49 45 52 53 20 47 6D 62 48 27 27 20 EN DIERS GmbH''
57 48 45 52 45 20 49 44 3D 34 30 30 30 30 30 32 WHERE ID=4000002
30 36 39 33 27 29 00 00 00 00 00 19 15 04 07 09 0693')
0B 0C 0D 0E 10 11 12 13 08 05 07 09 0B 0C 0D 0E
10 11 12 13 08 00 00 00 FF FF 80 00
## 19:01:49:907 Channel 1; DATA (Server -> Client): Length= 156
00 00 00 09 00 00 00 03 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 01 00 00 00 00 00 00 00 00
00 00 00 09 00 00 00 04 00 00 00 00 00 00 00 00
00 00 00 5A 15 04 00 01 00 00 00 04 07 04 00 01 Z
00 00 00 09 04 00 01 00 00 00 0B 04 00 09 02 00
00 0C 04 00 01 00 00 00 0D 04 00 04 00 00 00 0E
04 00 08 00 00 00 10 03 00 52 45 53 11 06 00 42 RES B
52 50 41 50 49 12 06 00 53 59 53 44 42 41 13 03 RPAPI SYSDBA
00 52 45 53 08 05 07 04 00 00 00 00 00 01 00 00 RES
00 00 00 01 00 00 00 00 00 00 00 00
## 19:01:49:908 Channel 1; DATA (Client -> Server): Length= 56
00 00 00 3F 00 00 00 03 00 00 00 01 00 00 00 00 ?
00 00 00 00 00 00 00 00 00 00 00 41 00 00 00 03 A
00 00 00 0C 05 02 04 00 02 00 09 00 07 00 FF 4C L
00 00 00 00 00 00 0A A8
## 19:01:49:967 Channel 1; DATA (Server -> Client): Length= 68
00 00 00 09 00 00 00 01 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 01 00 00 00 00 00 00 00 00
00 00 00 42 00 00 00 00 00 00 00 01 00 00 00 00 B
00 00 00 80 00 00 00 00 00 00 00 42 00 00 00 64 B d
00 00 00 00
## 19:01:49:975 Channel 1; DATA (Client -> Server): Length= 20
00 00 00 38 00 00 00 00 00 00 00 01 00 00 00 00 8
00 00 00 80
## 19:01:50:042 Channel 1; DATA (Server -> Client): Length= 32
00 00 00 09 00 00 00 04 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 01 00 00 00 00 00 00 00 00
## 19:01:50:044 Channel 1; DATA (Client -> Server): Length= 16
00 00 00 24 00 00 00 04 00 00 40 00 00 00 00 00 $ @
## 19:01:50:087 Channel 1; DATA (Server -> Client): Length= 44
00 00 00 09 00 00 00 02 00 00 00 00 00 00 00 00
00 00 00 0A 08 00 45 58 45 43 55 54 45 44 00 00 EXECUTED
00 00 00 01 00 00 00 00 00 00 00 00
## 19:01:50:092 Channel 1; DATA (Client -> Server): Length= 36
00 00 00 27 00 00 00 04 00 00 00 5B 00 00 00 01 ' [
00 00 00 43 00 00 00 03 00 00 00 01 00 00 00 5B C [
00 00 00 01
## 19:01:50:177 Channel 1; DATA (Server -> Client): Length= 32
00 00 00 09 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 01 00 00 00 00 00 00 00 00
Du siehst das jeder Befehl ca. 10 Datenpaket generiert, 5 vom client zum Server und 5 vom Server zum Client.
Wenn du dir eine geeignete clientseitige
API aufbaust, die dann eben mit der Serverapi klar kommt, dann
hast du den Vorteil, auch aus langsamen Netzen alles rausholen zu können und verzichtest trotzdem nicht
auf die ganzen Firebird Möglichkeiten.
wenn du den ganzen Dataset schnickschnack mit im Grid editieren usw. haben willst, dann ist das
ziemlich aufwändig, eine eigene Middleware auf der o.a. Technik aufzusetzen, aber das werden dir
auch andere Datenbanken kaum schneller liefern können.
Gruß
Holger