Entwickler-Ecke
Datenbanken - [MSSQL 2005] Dateienaustausch zwischen SQL-Server und Client
Herby99 - Fr 19.10.07 13:03
Titel: [MSSQL 2005] Dateienaustausch zwischen SQL-Server und Client
Hallo zusammen,
folgendes Projekt möchte ich realisieren: Eine Anwendung läuft mit dbGo Komponenten und SQL 2005 zusammen auf einem Client, der auf den SQL-Server (Express) zugreift. Teil des Datenaustausches sind auch viele große Binärdateien, diese werden u.a. wegen der 4GB-Grenze nicht als BLOB / varbinary abgespeichert, sondern es werden nur die Pfade und Dateinamen auf dem Server in Tabellen gespeichert.
Der Dateiaustausch sollte ohne Windows-Freigaben auf dem Server auskommen (also ohne einen Share-Ordner) und daher durch SQL "getunnelt" werden.
Wie kann man so etwas am besten realisieren? Meine Idee war, auf dem Server eine Stored Procedure (SP) zu erstellen, die mit Angabe einer ID eine Datei von seiner lokalen Platte in ein varbinary einliest und das Ergebnis im Client über TADOStoredProc zurückzulesen. SP's können aber anscheinend nur int zurückgeben, daher wäre der Aufbau eher so:
SQL-Anweisung
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
| CREATE PROCEDURE GetFile
@ID int,
@fstream varbinary(max) = NULL OUT
AS
BEGIN
DECLARE @fname varchar(50);
DECLARE @updatequery varchar(2000);
SELECT @fname = fname FROM FileTable WHERE ID = @ID;
-- diesen weg hier muss man gehen, da OPENROWSET keine variablen erlaubt
SET @updatequery = '
UPDATE FileTable
SET fstream = (SELECT * FROM OPENROWSET(BULK ''' + @fname + ''', SINGLE_BLOB) AS x)
WHERE fname='''+ @fname +''';';
EXEC (@updatequery);
SELECT @fstream = fstream FROM FileTable WHERE ID=@ID;
END |
wobei @fstream dann ein InputOutput wäre. In Delphi liefert mir der folgende Code
Delphi-Quelltext
1:
2:
3:
4:
5:
6:
7:
8:
| ADOStoredProc1.Parameters.Clear;
ADOStoredProc1.ProcedureName := 'GetFile';
ADOStoredProc1.Parameters.Refresh;
ADOStoredProc1.Parameters.ParamByName('@ID').Value := 1;
ADOStoredProc1.ExecProc; |
aber die Fehlermeldung "Ein Parameterobjekt ist nicht ordnungsgemäß definiert. Inkonsistente oder unvollständige Informationen wurden angegeben". Was muss man beim Typ ftVarByte noch beachten? Wie kann man anschliessend das Variant-Ergebnis in einen Stream oder Datei sichern?
Wie sähe das ganze andersherum aus, also einen Stream zur DB übergeben und diesen dort als lokale Datei sichern?
Oder gibt es komplett andere Möglichkeiten soetwas zu realisieren?
Gruß,
Jan
Agawain - Fr 19.10.07 21:20
Hi
da kenn ich mich nicht mit aus, aber da bisher kein anderer geantwortet hat:
Man könnte auch eine kleine eigene Serveranwendung schreiben, die die Daten dann streamt.
Gruß
Aga
alzaimar - Fr 19.10.07 21:34
Wieso lieferst Du das VarBinary nicht über ein
Delphi-Quelltext
1:
| SELECT @MyBinaryData as BinaryData |
und holst die Daten über ein TBlobfield ab?
Allerdings würde ich auch empfehlen, auf dem Server einen kleinen TCP-Server laufen zu lassen, der auf Anforderung einfach zu einem übergebenen Dateinamen dessen Inhalt liefert (oder einfach einen FTP-Server von Indy). Irgendwie sind SQL-Server nicht dazu da, als Quasi FTP-Server zu fungieren.
Herby99 - Mo 22.10.07 13:58
Hallo,
danke für die Antwort! Ich habe es jetzt anhand deines Tips wie folgt umgeändert:
SQL-Anweisung
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
| CREATE PROCEDURE GetFile (
@ID int)
AS
BEGIN
DECLARE @fstream varbinary(max);
DECLARE @fname varchar(50);
DECLARE @path_to_fname varchar(1000);
DECLARE @updatequery varchar(2000)
SET @path_to_fname = 'D:\temp2\myDBFolder\';
-- prüfen, ob fstream geladen werden muss
SELECT @fstream = fstream FROM FileTable WHERE ID=@ID;
IF @fstream IS NULL
BEGIN
-- OPENROWSET unterstützt keine variablen, daher der umweg:
SELECT @fname = fname FROM FileTable WHERE ID = @ID;
SET @updatequery = '
UPDATE FileTable
SET fstream = (SELECT * FROM OPENROWSET(BULK ''' + @path_to_fname + @fname + ''', SINGLE_BLOB) AS x)
WHERE ID='+ @ID +';';
--print @updatequery;
EXEC (@updatequery);
END;
SELECT @fstream = fstream, @fname = fname FROM FileTable WHERE ID=@ID;
SELECT @fstream as BinaryData, @fname as FileName;
END; |
und abgefragt wird in Delphi über:
Delphi-Quelltext
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
| ADOStoredProc1.Close;
ADOStoredProc1.Parameters.Clear;
ADOStoredProc1.ProcedureName := 'dbo.GetFile';
ADOStoredProc1.Parameters.AddParameter.Name := '@ID';
ADOStoredProc1.Parameters.ParamByName('@ID').DataType := ftInteger;
ADOStoredProc1.Parameters.ParamByName('@ID').Direction := pdInput;
ADOStoredProc1.Parameters.ParamByName('@ID').Size := MaxInt;
ADOStoredProc1.Parameters.ParamByName('@ID').Value := ID;
ADOStoredProc1.Open;
JvSaveDialog1.FileName := ExtractFileName(ADOStoredProc1.fields.FieldByName('FileName').Value);
if JvSaveDialog1.Execute = True then
begin
TBlobField(ADOStoredProc1.fields.FieldByName('BinaryData')).SaveToFile(JvSaveDialog1.FileName);
end; |
Anscheinend gibt es wohl immer irgendwie Probleme mit den Parametern - so wie es jetzt da steht geht es, eigentlich sollte es aber auch ohne dass "AddParameter..." gehen?! Der Vollständigkeit halber für Interessierte gibt es noch den Code für das Hochladen und abspeichern auf dem Server:
SQL-Anweisung
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
| CREATE PROCEDURE [dbo].[SendFile] (
@filename varchar(50),
@fstream varbinary(max))
AS
BEGIN
-- in tabelle zwischenspeichern
INSERT INTO dbo.FileTable (fname, fstream) VALUES (@filename, @fstream);
-- lokaler file dump
DECLARE @local_filename varchar(200);
DECLARE @bulk_export_action varchar(2000);
-- zielverzeichnis auf dem server erstmal hart codieren:
SET @local_filename = 'd:\temp2\myDBFolder\' + @filename;
SET @bulk_export_action = 'bcp "SELECT TOP 1 fstream FROM Testdatenbank.dbo.FileTable ORDER BY ID DESC" queryout ' + @local_filename + ' -n -T';
-- shell aktivieren
EXEC sp_configure 'awe enabled', 0; -- setzen auf 1 bei SQL Express 2005 nicht erlaubt
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE WITH OVERRIDE;
-- bulk export ausführen
EXEC xp_cmdshell @bulk_export_action
-- shell wieder deaktivieren
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE;
-- BLOB aus DB löschen
UPDATE FileTable SET fstream = NULL;
-- resultierende ID rausgeben
SELECT TOP 1 ID FROM dbo.FileTable ORDER BY ID DESC;
END |
und in Delphi
Delphi-Quelltext
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
| ADOStoredProc1.Close;
ADOStoredProc1.Parameters.Clear;
ADOStoredProc1.ProcedureName := 'dbo.SendFile';
if JvOpenDialog1.Execute then
begin
ADOStoredProc1.Parameters.AddParameter.Name := '@filename';
ADOStoredProc1.Parameters.AddParameter.Name := '@fstream';
ADOStoredProc1.Parameters.ParamByName('@filename').DataType := ftString;
ADOStoredProc1.Parameters.ParamByName('@filename').Direction := pdInput;
ADOStoredProc1.Parameters.ParamByName('@filename').Size := 50;
ADOStoredProc1.Parameters.ParamByName('@filename').Value := ExtractFileName(JvOpenDialog1.FileName);
ADOStoredProc1.Parameters.ParamByName('@fstream').DataType := ftBlob;
ADOStoredProc1.Parameters.ParamByName('@fstream').Direction := pdInput;
ADOStoredProc1.Parameters.ParamByName('@fstream').Size := MaxInt;
ADOStoredProc1.Parameters.ParamByName('@fstream').LoadFromFile(JvOpenDialog1.FileName, ftBlob);
ADOStoredProc1.Open;
end; |
Zum allgemeinen Handling mit Dateien: Eine FTP-ähnliche Anwendung wäre sicherlich schicker, hat aber einige Nachteile:
- Auf dem Server müsste noch eine weitere Software installiert werden (und vom Client angesteuert werden)
- Der eigene Admin ist eh immer in Panik wenn irgendwas nicht auf Standardports funkt :-)
- Admin beim Kunden dankt es evtl. ebenso
Zudem werden nicht ständig große Mengen hin- und her geschoben, so dass sich die Serverlast in Grenzen hält.
Gruß,
Jan
alzaimar - Mo 22.10.07 14:03
Von hinten durch die Brust ins Auge. Aber wenns funzt, funzt's.
Entwickler-Ecke.de based on phpBB
Copyright 2002 - 2011 by Tino Teuber, Copyright 2011 - 2026 by Christian Stelzmann Alle Rechte vorbehalten.
Alle Beiträge stammen von dritten Personen und dürfen geltendes Recht nicht verletzen.
Entwickler-Ecke und die zugehörigen Webseiten distanzieren sich ausdrücklich von Fremdinhalten jeglicher Art!