jdbc stored png image at sql server 2008 r2 gives

问题:

I want to save base64 image to my sql server 2008 r2, i used this:

Connection con = Database.getConnection();
        CallableStatement callableStatement = null;
        try {
            callableStatement = con
                    .prepareCall("{call insertRestaurantFoodImage2(?,?)}");
            callableStatement.setInt(1, ID);
            ;
            callableStatement.setString(2, stringImage);
            callableStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

the stored procedure to save the string is :

ALTER PROCEDURE dbo.insertRestaurantFoodImage2
    (
    @restaurantFoodID INT,
    @image VARCHAR(MAX)
    )
AS
BEGIN
    SET NOCOUNT ON
    UPDATE Food_Restaurant
    SET [image] = @image
    WHERE ID = @restaurantFoodID
END

retrive data like this:

Connection con = Database.getConnection();
            CallableStatement callableStatement = null;
            try {
                callableStatement = con
                        .prepareCall("{call getRestaurantFoodImage2(?,?)}");
                callableStatement.setInt(1, getID());
                callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
                callableStatement.execute();
                image = callableStatement.getString(2);
            } catch (SQLException e) {
                e.printStackTrace();
            }

the stored procedure to get image is :

ALTER PROCEDURE dbo.insertRestaurantFoodImage2
    (
    @restaurantFoodID INT,
    @image VARCHAR(MAX)
    )
AS
BEGIN
    SET NOCOUNT ON
    UPDATE Food_Restaurant
    SET [image] = @image
    WHERE ID = @restaurantFoodID
END

when i check the string before insert it and after retirve it , the result was n't equaled, the retirve image has about the first 500 (less or more) chars, what am i doing wrong please.

I know u will tell me, try to use varbinary instead of varchar, so please look at this question, i tried and had problems encode and decode base64 excpetion storing to sql server 2008 r2

please help , please help

回答1:

As I also commented older version of SQL Server had a limit of 8kb for varbinary and varchar. Either the driver simply doesn't support longer for getBytes or getString, or you might be using an older JDBC driver. Another possibility is that you need to register the OUT parameter as java.sql.types.LONGVARBINARY (or LONGVARCHAR).

In any case (also in the other question), I am wondering why you are first encoding in base64 before storing your data. Just store the raw bytes in a VARBINARY.