[SQL 2005] Dateienaustausch SQL-Server <-> Client-Anwendung

[Herby99]

Hallo zusammen,

folgendes Projekt möchte ich realisieren: Eine Anwendung läuft mit ADO / 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-Code:

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-Quellcode:

ADOStoredProc1.Parameters.Clear;
  ADOStoredProc1.ProcedureName := 'GetFile';

  ADOStoredProc1.Parameters.Refresh;
  ADOStoredProc1.Parameters.ParamByName('@ID').Value := 1;
  //ADOStoredProc1.Parameters.ParamByName('@fstream').Value := null; // <- müsste im Prinzip nicht initialisiert werden?
  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

[mr2]

Hallo,

ich vermute mal, dass Du die maximale Größe des Parameters @fstream angeben musst:

Delphi-Quellcode:

  ADOStoredProc1.Parameters.ParamByName('@fstream').Size := MaxInt;

Alternativ könntest Du Dir die Daten auch als Recordset zurückgeben lassen, in dem Du den Out-Parameter weglässt und nur den Select ans Ende der Procedur schreibst.
Dann musst Du allerdings das DataSet mit Open statt Execute öffnen und als CommandText

SQL-Code:

  exec dbo.GetFile :ID

verwenden.

mr2

P.S.: Die Prozedur solltest Du immer voll qualifizieren (d.h. 'dbo.' angeben), da der SQL-Server sonst immer zu erst im Schema des angemeldeten Nutzer nach der Prozedur sucht.

[Herby99]

Hallo,

Ich habe das ganze nun so gelöst:

Zunächst der Delphi Code:

Delphi-Quellcode:

  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;
  
  // speichern
  JvSaveDialog1.FileName := ExtractFileName(ADOStoredProc1.fields.FieldByName('FileName').Value);
  if JvSaveDialog1.Execute = True then
  begin
    TBlobField(ADOStoredProc1.fields.FieldByName('BinaryData')).SaveToFile(JvSaveDialog1.FileName);
  end;

Wie man sieht, greife ich nun auf das Ergebnis per Recordset zu, welches ich am Ende der SP herausgebe:

SQL-Code:

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

Falls jemand interessiert ist, ich habe auch noch Code für das abspeichern einer Clientdatei auf dem Server...

Gruß,
Jan