Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Vfpoledb and binary parameters
Message
De
28/02/2007 10:03:23
Cetin Basoz
Engineerica Inc.
Izmir, Turquie
 
 
À
26/02/2007 13:55:30
Alan Krack
Mammography Reporting System
Seattle, Washington, États-Unis
Information générale
Forum:
Visual FoxPro
Catégorie:
Visual FoxPro et .NET
Versions des environnements
Visual FoxPro:
VFP 9 SP1
OS:
Windows XP SP2
Divers
Thread ID:
01198862
Message ID:
01199538
Vues:
29
This message has been marked as the solution to the initial question of the thread.
Alan,
I think I found the reason. Initially I thought it was fault of VFPOLEDB but now I doubt. Looks like a .Net weirdness.
The byte array is passed as null when it's over 8000. Up to 8000 bytes you can pass multiple byte arrays to a procedure.
It works right with ADO even if over 8000 (OTOH it works right with ADO.Net when used with SQLOLEDB if I didn't overlook anything).
Anyway there is also a workaround.

You SP modified:
PROCEDURE AddRespArch( theNewID AS INTEGER, ;
    tPatID AS INTEGER, ;
    tRespDate AS DATETIME, ;
    tXml as String,;
    tSignature as String,;
    tIsSignatureHexEncoded as Boolean)

  LOCAL newresparchid AS INTEGER
  LOCAL lSignature as String
  lSignature = IIF( m.tIsSignatureHexEncoded, ;
    STRCONV(m.tSignature,16), ;
    m.tSignature)

  newresparchid = m.theNewID
  *newresparchid = NewID('RespArch','iresparchid')

  INSERT INTO RespArch ;
    ( ;
    iresparchid, ;
    ipatientid, ;
    tresponse, ;
    xml, ;
    bsignature ;
    ) ;
    VALUES ;
    ( ;
    m.newresparchid, ;
    m.tPatID, ;
    m.tRespDate, ;
    m.tXml, ;
    m.lSignature ;
    )
  theNewID = newresparchid
  RETURN theNewID && GetNewIDResSet(theNewID)
ENDPROC
And .Net test:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace VFPSpCall
{
    class Program
    {
        static void Main(string[] args)
        {
            string strCon = @"Provider=VFPOLEDB;Data Source=c:\temp\NetSample.dbc";
            string[] fileNames = new string[]
     {
         @"c:\temp\graphics\suyamich.gif",
         @"c:\temp\graphics\buchstev.gif",
         @"c:\temp\graphics\davonanc.gif",
         @"c:\temp\graphics\dodsanne.gif",
         @"c:\temp\graphics\fullandr.gif"
     };

            OleDbConnection cn = new OleDbConnection(strCon);

            // Prepare command and parameters
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "AddRespArch";
            cmd.Connection = cn;

            OleDbParameter p1 = cmd.Parameters.Add("theNewID", OleDbType.Integer);
            OleDbParameter p2 = cmd.Parameters.Add("tPatID", OleDbType.Integer);
            OleDbParameter p3 = cmd.Parameters.Add("tRespDate", OleDbType.Date);
            OleDbParameter p4 = cmd.Parameters.Add("tXML", OleDbType.Char);
            OleDbParameter p5 = cmd.Parameters.Add("tSignature", OleDbType.PropVariant);
            OleDbParameter p6 = cmd.Parameters.Add("tSignatureHexEncoded", OleDbType.Boolean);
            p6.Value = true;

            cn.Open();
            // insert 5 records
            for (int i = 0; i < 5; i++)
            {
                byte[] image = File.ReadAllBytes(fileNames[i]);
                Console.WriteLine(image.Length);

                p1.Value = i;
                p2.Value = i * 10;
                p3.Value = DateTime.Now.AddDays(-i);
                p4.Value = String.Format("<SomeTag>SomeValue {0}</SomeTag>", i);
                p5.Value = BitConverter.ToString(image).Replace("-",String.Empty);
                int retValue = (int)cmd.ExecuteScalar();
                Console.WriteLine("Inserted record with ID: {0}", retValue);
            }
            cn.Close();
        }
    }
}
Cetin

>Here is the section where the parameters that are sent to the stored procedures are set up.
>
>....
>.Parameters.Add(myDataBaseProvider.CreateParameter("@tRespDate", _response))
>.Parameters.Add(myDataBaseProvider.CreateParameter("@tXML", _xML))
>.Parameters.Add(myDataBaseProvider.CreateParameter("@tSignature", IIf(buffer Is Nothing, DBNull.Value, buffer), DbType.Binary))
>
>The third parameter is an image that's been converted to a binary stream. At this point the value of "buffer" is correct and contains the binary data. This is sent to a VFP store parameter that creates a new record and saves the data. This signature field in the VFP table is a blob field that accepts null values.
>
>The stored procedure looks like this:
>
>PROCEDURE AddRespArch
>LPARAMETERS theNewID AS INTEGER, ;
>			tPatID AS INTEGER, ;
>			tRespDate AS DATETIME, ;
>			tXml as String, ;
>			tSignature as Image
>			
>LOCAL newresparchid AS INTEGER
>
>NewID('RespArch','iresparchid',1,@newresparchid)
>	
>	INSERT INTO RespArch ;
>		( ;
>		iresparchid, ;
>		ipatientid, ;
>		tresponse, ;
>		xml, ;
>		bsignature ;
>		) ;
>	VALUES ;
>		( ;
>		newresparchid, ;
>		tPatID, ;
>		tRespDate, ;
>		tXml, ;
>		tSignature ;
>		)
>	theNewID = newresparchid
>	RETURN GetNewIDResSet(theNewID)
>ENDPROC
>
>
>
>If the value of tSignature is tested immediately after the LPARAMETERS statement, it always NULL, the other values are correct.
Çetin Basöz

The way to Go
Flutter - For mobile, web and desktop.
World's most advanced open source relational database.
.Net for foxheads - Blog (main)
FoxSharp - Blog (mirror)
Welcome to FoxyClasses

LinqPad - C#,VB,F#,SQL,eSQL ... scratchpad
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform