How to save byte[] using a procedure?

Question

This stored procedure does not save the data, it seems to be a problem with the VARBINARY. I am passing a byte[] to it, but then it doesn't work. If I send this parameter as NULL it works.

I'm calling the procedure with the following code:

public Community AddCommunity(string name, string description, byte[] picture, User owner, int? venue, int communityID) 
{
    using (var database = new Database())
    {
        return database.Scope.GetSqlQuery<Community>("QP_AddCommunity ?, ?, ?, ?, ?, ?", "VARCHAR Name, VARCHAR Description, VARBINARY Picture, INTEGER Owner, INTEGER Venue, INTEGER ID").GetResult(name, description, picture, owner.ID, venue, communityID);
    }
}

The procedure is the following:

CREATE PROCEDURE [dbo].[QP_AddCommunity]
    @Name VARCHAR(120),
    @Description VARCHAR(MAX),
    @Picture VARBINARY(MAX),
    @Owner INTEGER,
    @Venue INTEGER,
    @ID INTEGER

AS
BEGIN
    SET NOCOUNT ON;

    IF(SELECT COUNT(*) FROM QT_Community WHERE ID = @ID) = 0
        INSERT INTO QT_Community(Name, [Description], Picture, [Owner], Venue) VALUES(@Name, @Description, @Picture, @Owner, @Venue);

    ELSE
        UPDATE QT_Community SET Name = @Name, [Description] = @Description, Picture = @Picture, [Owner] = @Owner, Venue = @Venue WHERE ID = @ID;

    SELECT * FROM QT_Community WHERE ID = @@IDENTITY;


END

What's wrong with this code? Isn't VARBINARY a byte[] ?

This code works when executing on SQL Server Management Studio.

DECLARE @X varbinary(20)
Set @X = CAST('Testing' As varbinary(20))
EXECUTE [QP_AddCommunity] 'aaaaa', 'descricao', @X, 216, NULL, 0;

But when calling from the GetSqlQuery method with something on the byte[] the transaction says it's not active and not dirty. BUT if the byte[] is null it works as it should.

Define 'doesn't work'. Do you get an error? Does the insert/update work but the picture is missing? Does the insert/update work and the picture is corrupted? btw, code that checks for COUNT(*) and then inserts or updates is broken under concurrency. You should use `MERGE` http://technet.microsoft.com/en-us/library/bb510625.aspx — Remus Rusanu, Sep 24 '10 at 14:45

score 5 · Accepted Answer · answered Sep 24 '10 at 17:51

i found that it is impossible as this answer shows

Hello gaurav, currently our GetSqlQuery method cannot operate properly with parameters of type LongVarBinary or VarBinary, thus making it impossible for the stored procedure to work as expected. We are aware of this problem and we are working on fixing it. As a work around you should try and use Linq to achieve your goal. Greetings, Petar the Telerik team

score 1 · Answer 2 · answered Sep 24 '10 at 16:20

1

Accordingly to this table it seems either BLOB, BINARY, VARBINARY would be valid types for [] of primitive type.

You could try to ask on their forums, maybe someone will be able to help you.

answered Sep 24 '10 at 16:20

BrunoLM

97,872
84
296
452

i have already asked here http://www.telerik.com/community/forums/orm/development/save-image-with-procedure.aspx – Gadonski Sep 24 '10 at 17:13

Joe Stefanelli · Answer 3 · 2010-09-24T14:35:49.657

0

Try using the .WRITE method. On your INSERT, insert 0x for Picture, then update independently.

UPDATE QT_Community 
    SET Picture.Write (@Picture, 0, DATALENGTH(Picture))
    WHERE ID = @ID

edited Sep 24 '10 at 14:35

answered Sep 24 '10 at 14:29

Joe Stefanelli

132,803
19
237
235

2

I don't mind a downvote if I'm wrong. I do however dislike anonymous downvotes with no explanation. Can you explain your objection? – Joe Stefanelli Sep 24 '10 at 14:45
This seems totally unrelated to the problem. It's not a problem on SQLServer, but a problem on Telerik's ORM. He can't pass a `byte[]` to the procedure. He can however send a `null`, proving the problem is on the ORM. – BrunoLM Sep 24 '10 at 15:11
@BrunoLM: Thanks for the feedback. It's certainly possible that I've misunderstood the problem. That will be for the OP to determine. I thought I was offering a reasonable work around that could at least be *tried*. – Joe Stefanelli Sep 24 '10 at 15:15
`But when calling from the GetSqlQuery method with something on the byte[] the transaction says it's not active and not dirty. BUT if the byte[] is null it works as it should.`. How he is supposed to pass `@Picture` if the method will not work? – BrunoLM Sep 24 '10 at 15:32
@BrunoLM: Looking at the edit history on the question, those details were added *after* I posted my answer. – Joe Stefanelli Sep 24 '10 at 15:36
1

He actually described this behavior before the edit. `it seems to be a problem with the VARBINARY. I am passing a byte[] to it, but then it doesn't work. If I send this parameter as NULL it works.` – BrunoLM Sep 24 '10 at 16:08
@Joe Stefanelli: Hi, thanks for your answer but, my problem is not in procedure. Its a problem when i send a `byte[]`, it doesnt crashes when i send `null`. – Gadonski Sep 24 '10 at 17:12

score 0 · Answer 4 · answered Sep 24 '10 at 17:20

Example (Ado.Net):

byte[] ba = UlongsToBytes(ul);
try
{
string source = @"packet size=4096;integrated security=SSPI;data source=MyPC\MyNamedInstance;persist security info=False;initial catalog=Sandbox";
SqlConnection conn = new SqlConnection(source);
conn.Open();
SqlCommand a = new SqlCommand("INSERT BigintsTarget(bi) SELECT * FROM dbo.ParseImageIntoBIGINTs(@BIGINTs)", conn);
a.CommandType = System.Data.CommandType.Text;
a.Parameters.Add(new SqlParameter("@BIGINTs", System.Data.SqlDbType.Image,2147483647));
for(int q=0; q<10; q++)
{
a.Parameters[0].Value = ba;
int res = a.ExecuteNonQuery();
}
d2 = DateTime.Now;
SqlCommand b = new SqlCommand("INSERT BigintsTarget1(bi) SELECT * FROM dbo.ParseVarcharMAXIntoBIGINTs(@BIGINTs)", conn);
b.CommandType = System.Data.CommandType.Text;
b.Parameters.Add(new SqlParameter("@BIGINTs", System.Data.SqlDbType.VarChar,2147483647));
for(int q=0; q<10; q++)
{
b.Parameters[0].Value = sss;
int res = b.ExecuteNonQuery();
}
//b.ExecuteNonQuery();
conn.Close();
}
catch(Exception ex)
{
string s = ex.Message;
int t=0;
t++;
}

}

How to save byte[] using a procedure?

4 Answers4