locked
Problem with inserting base64 string to table RRS feed

  • Question

  • Hi,

    I have a very simple sp for insert data in SQL Server table:

     

    ALTER PROCEDURE [dbo].[spUbaciVozilo]

    @city NVARCHAR(50),
    @pic1 NVARCHAR(MAX),

    AS
    BEGIN

    declare @file1 varchar(200), @logstatus varchar(MAX)

    set @file1 = 'VP_'+convert(varchar, getdate(), 112)+'.txt'
    set @logstatus = convert(varchar, getdate(), 120)+' Picture 1:'+@pic1
    exec dbo.sp_W2F @logstatus, 'c:\SQLlog', @file1

    insert into tVozila (city,pic1) 
    values (@city,@pic1)


    END

    So, everything is OK while is Pic1 small. If I send a biger image (800 x 600), I get empty field for pic1! In the log file I have a complete base64 string...

    Any idea?

    Thanks,

           Jovo



    Sunday, December 29, 2013 10:01 PM

Answers

  • Thanks to all...

    Sorry, it was may fault :(

    I can not see anything in table, but I was try call webservice from program

        Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
            Dim ws As New wsPauk.wspaukSoapClient("wspaukSoap")
            Dim ds As New DataSet
    
            ds = ws.dajvozila(16)
            If ds.Tables(0).Rows.Count > 0 Then
                TextBox1.Text = ds.Tables(0).Rows(0).Item("pic1")
            End If
    
    
        End Sub

    and I get whole string...

    After that, I am converting that, I have picture :)

     Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim bytes As Byte() = Convert.FromBase64String(TextBox1.Text)
    
            Dim ms As New MemoryStream(bytes)
            Dim img As Image = System.Drawing.Image.FromStream(ms)
    
            PictureBox1.BackgroundImage = img
            PictureBox1.BackgroundImageLayout = ImageLayout.Stretch
    
        End Sub

    Thanks again.

    Regards,

           Jovo

    • Marked as answer by jovomirkovic Wednesday, January 1, 2014 8:02 PM
    Wednesday, January 1, 2014 7:07 PM

All replies

  • Please try once by changing the datatype of @logstatus as "nvarchar". Currently it is "varchar".

    What is the datatype of "pic1" field in the table tVozila. Please set it as "nvarchar(max)"

    Also please post the exact error message.

    Regards, RSingh


    Monday, December 30, 2013 2:00 AM
  • Hi,

    I have a very simple sp for insert data in SQL Server table:

     

    ALTER PROCEDURE [dbo].[spUbaciVozilo]

    @city NVARCHAR(50),
    @pic1 NVARCHAR(MAX),

    AS
    BEGIN

    declare @file1 varchar(200), @logstatus varchar(MAX)

    set @file1 = 'VP_'+convert(varchar, getdate(), 112)+'.txt'
    set @logstatus = convert(varchar, getdate(), 120)+' Picture 1:'+@pic1
    exec dbo.sp_W2F @logstatus, 'c:\SQLlog', @file1

    insert into tVozila (city,pic1) 
    values (@city,@pic1)


    END

    So, everything is OK while is Pic1 small. If I send a biger image (800 x 600), I get empty field for pic1! In the log file I have a complete base64 string...

    Any idea?

    Thanks,

           Jovo



    Hi ,

    For storing image , use VARBINARY data type  - http://technet.microsoft.com/en-us/library/ms188362.aspx ,

    or try to implement FILESTREAM / FILETABLE

    http://technet.microsoft.com/en-us/library/gg471497.aspx

    http://technet.microsoft.com/en-us/library/ff929144.aspx


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Monday, December 30, 2013 2:25 AM
  • Hi,

    To insert image into table , use OPENROWSET - http://stackoverflow.com/questions/416881/insert-picture-into-sql-server-2005-image-field-using-only-sql

    Storing Base64 data in SQL Server - http://social.msdn.microsoft.com/Forums/en-US/cfbf3d4c-349c-41c5-8ad3-58bd03ade6a4/storing-base64-data-in-sql-server


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.


    • Edited by SathyanarrayananS Monday, December 30, 2013 2:51 AM added msdn thread
    • Proposed as answer by Fanny Liu Monday, December 30, 2013 8:30 AM
    Monday, December 30, 2013 2:34 AM
  • Is that possible to Openrowset to the insert the images?

    USE tempdb
    IF OBJECT_ID('MyBLOBTable') IS NOT NULL DROP TABLE MyBLOBTable
    GO
    CREATE TABLE MyBLOBTable (blob_id int identity PRIMARY KEY, blob_description varchar(200), blob_data 
    varbinary(max))
    GO

    INSERT INTO
        MyBLOBTable (blob_description, blob_data)
    SELECT
     'Pippi, our dog'
    ,BulkColumn FROM Openrowset(Bulk 'C:\BlobHandling\theDog.JPG', SINGLE_BLOB) AS blob
    GO

    SELECT * FROM MyBLOBTable


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, December 30, 2013 6:16 AM
    Answerer
  • Please try once by changing the datatype of @logstatus as "nvarchar". Currently it is "varchar".

    What is the datatype of "pic1" field in the table tVozila. Please set it as "nvarchar(max)"

    Also please post the exact error message.

    Regards, RSingh


    It is same, noting is changed... "pic1" is nvarchar(max) in table.

    There are no error message, CITY is regulary in the table, but PIC1 is still empty :(

    In the LOG_FILE you can see whole base64 string..

    Regards,

        Jovo

    Wednesday, January 1, 2014 5:00 PM
  • I ran the script below with the example data you provided and was unable to reproduce you issue.  Just to be clear, you are saying the row is inserted but the pic1 value is empty (or NULL), not truncated?

    As a side note, I suggest you use varchar(MAX) instead of nvarchar(MAX) since a base64 encoded string will only have ASCII characters.  This will significantly reduce database space requirements.

    CREATE TABLE dbo.tVozila (
    	city nvarchar(50)
    	,pic1 nvarchar(MAX)
    	);
    GO
    CREATE PROCEDURE [dbo].[spUbaciVozilo]
    	@city nvarchar(50),
    	@pic1 nvarchar(MAX)
    
     AS
    INSERT INTO dbo.tVozila (city,pic1)
    	VALUES (@city,@pic1);
    
    RETURN @@ERROR;
    END
    GO
    EXEC dbo.spUbaciVozilo
    	 @city =N'some city'
    	,@pic1 = N'<173477 character value here>';
    GO
    
    SELECT DATALENGTH(pic1) AS pic1_length, pic1
    FROM dbo.tVozila;
    GO


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, January 1, 2014 6:23 PM
    Answerer
  • Thanks to all...

    Sorry, it was may fault :(

    I can not see anything in table, but I was try call webservice from program

        Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
            Dim ws As New wsPauk.wspaukSoapClient("wspaukSoap")
            Dim ds As New DataSet
    
            ds = ws.dajvozila(16)
            If ds.Tables(0).Rows.Count > 0 Then
                TextBox1.Text = ds.Tables(0).Rows(0).Item("pic1")
            End If
    
    
        End Sub

    and I get whole string...

    After that, I am converting that, I have picture :)

     Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim bytes As Byte() = Convert.FromBase64String(TextBox1.Text)
    
            Dim ms As New MemoryStream(bytes)
            Dim img As Image = System.Drawing.Image.FromStream(ms)
    
            PictureBox1.BackgroundImage = img
            PictureBox1.BackgroundImageLayout = ImageLayout.Stretch
    
        End Sub

    Thanks again.

    Regards,

           Jovo

    • Marked as answer by jovomirkovic Wednesday, January 1, 2014 8:02 PM
    Wednesday, January 1, 2014 7:07 PM