Question Re: MSSQL : converting IMAGE to varbinary(max) in Technology Q&A

MSSQL : converting IMAGE to varbinary(max)

Former Member — Wed, 29 Oct 2014 04:05:17 GMT

Problem 1:

What's the best way to convert columns from IMAGE to varbinary(MAX)?

I'm just testing my application which runs on sql 2008 r2 (but on compatibility mode 80 (sql server 2000).

I probably have a lot of work to do., mainly to do with deprecated features (like no more pb syntax *= but using inner join)/

so I find out that IMAGE datatype will be deprecated ., but it should be a straightforward copy to varbinary(MAX).

now, the issue here is the 3 largest tables in my db are 76gb, 32gb, 7gb

eg 1: this might work (please let me know otherwise). except it fills up the db log and kills it.

update tblcasereporttrans set userreportimage2 = userreportimage

ALTER TABLE tblcasereporttrans DROP COLUMN userreportimage

exec sp_rename 'tblcasereporttrans.userreportimage2','userreportimage','COLUMN'

personally I think a restore from db might be the best way to do it, but how can I get it to restore to a different column type.

restoring the full database into a test db takes 13 minutes, 786.896 seconds (154.579 MB/sec).

I suspect any of the other ways will take much longer.

-----

PROBLEM 2:

so i'm doing a test now with (so I suppose if need be, I can loop it 5000 times and keep the log file in check..(still, its not going to do any good for my offsite/snapshot backups I think)

SET ROWCOUNT 40

WHILE EXISTS (SELECT * FROM tblcasereporttrans where userreportimage2 is null)

BEGIN

update tblcasereporttrans set userreportimage2 = userreportimage where userreportimage2 is null

END

ALTER TABLE tblcasereporttrans DROP COLUMN userreportimage

exec sp_rename 'tblcasereporttrans.userreportimage2','userreportimage','COLUMN'

then I go and pull out my data (ms word document) and it appears to be corrupt.

selectblob tblCaseReportTrans.UserReportImage

into :lb_doc

from tblCaseReportTrans

where tblCaseReportTrans.JobReferenceNo = :ls_refno and

tblCaseReportTrans.Reportid = :ls_reportid and

tblCaseReportTrans.create_date = :ldt_create;

if SQLCA.SQLCode < 0 then

f_display_msg("E", 5000, SQLCA.SQLErrText)

end if

if IsNull(lb_doc) then

Messagebox("error","error in blobdata")

return

end if

f_write_blob(lb_doc, f_gettemppath() + "CTSTEMP-CO-" +ls_reportid +"-" + string(f_getdate(),"yyyymmddhhmm") + "-" + ls_refno + ".doc")

ole_1.objectdata = lb_doc // FAILS HERE

which I find is that although datalength(userreportimage) is 149kb. the datasize on disk (ie: whatever was returned by selectblob) is 32767 only

yet my sql server settings in pbodb125.ini is

PBMaxBlobSize='2147483647'

PBMaxTextSize='2147483647'

so, its a selectblob problem (pb 12.5.2 5652) (maybe not working with varbinary(max) (but works with IMAGE)

so any better suggestions to both problems?

Re: MSSQL : converting IMAGE to varbinary(max)

Former Member — Wed, 29 Oct 2014 08:29:41 GMT

Hello,

Problem 1:

Regarding to your legitimate concerns about the log file, you could test to set the database recovery model in SQL Server to 'simple' during the conversion, that should keep file size of the log quite low.

http://msdn.microsoft.com/de-de/library/ms189275.aspx

When we did our conversion from image to varbinary (max) some time ago we even did a direct ALTER TABLE not using a second column.

ALTER TABLE tblcasereporttrans ALTER COLUMN userreportimage VARBINARY ( MAX );

But in our case the max size of the affected table was about 20GB, so a lot smaller than your example.

Problem 2:

When we changed the datatype we hadn't to change anything else in the code. So SELECTBLOB works fine with varbinary(max) (we use PB 12.5 too). Could this be a driver issue? How to you connect to your db?

On our side we use SQLNCLI10 (SQL Native Client for SQL Server 2008).

HTH

Re: MSSQL : converting IMAGE to varbinary(max)

Former Member — Wed, 29 Oct 2014 08:56:41 GMT

I am using odbc to connect.

the settings in pbodb125.ini just needs to be there for maxblobsize.

problem right now is the code works for IMAGE, not for varbinary(MAX) )

Re: MSSQL : converting IMAGE to varbinary(max)

Former Member — Wed, 29 Oct 2014 09:37:48 GMT

If possible you should do a cross check with SNC instead of ODBC. It could be that varbinary (max) is not supported with ODBC. It's not clearly stated that ODBC won't function, but documentation always mentions SNC only.

SyBooks Online

[...]The PowerBuilder SNC interface maps the Varbinary(max) datatype to a BLOB datatype, so to retrieve or update filestream data, use the SelectBlob or UpdateBlob SQL statements, respectively.[...]

Have you tried it with smaller blobs to narrow the problem?

Since varbinary (max) is mapped to BLOBs, maxblobsize could be an issue.

FYI:

http://www.linkedin.com/groups/Help-on-varbinary-max-issue-1082737.S.245805926

SAP Sybase Forums - PowerBuilder - Database - UPDATEBLOB on MS SQL Server 2008 - Function Sequence Error

Re: MSSQL : converting IMAGE to varbinary(max)

Former Member — Wed, 29 Oct 2014 09:58:56 GMT

Hi, I've marked you as correct.

a) I tried an odbc connection using sql server native client 10. but it didn't work. (ie: under windows odbc, the selected driver sql server native client

b) my pbodb125.ini is deployed with the app and also updated in (both the application directory, as well as the development's machine pb shared files folder), but that doesn't help (pbmaxblobsize is updated to

PBMaxBlobSize='2147483647'

PBMaxTextSize='2147483647'

)

changing my db login to snc worked though. but it will involve tons of testing before I can deploy.

I suppose I will procrastinate instead.

Re: MSSQL : converting IMAGE to varbinary(max)

Former Member — Wed, 29 Oct 2014 11:13:02 GMT

Hi Chris;

I believe the recommendation is to use the SNC driver ...

SQLCA.DBMS = "SNC"

SQLCA.DBParm = " Provider='SQLNCLI10' "

Note: This is not the same as using ODBC with the SNC option.

HTH

Regards ... Chris

Re: MSSQL : converting IMAGE to varbinary(max)

Former Member — Wed, 29 Oct 2014 11:27:53 GMT

yeap. my connect string for snc is like so

//snc login

//SQLCA.DBMS = "SNC SQL Native Client(OLE DB)"

//SQLCA.LogPass = ls_password

//SQLCA.ServerName = ProfileString(gs_CTSINI, "Database", "ServerName", "")

//SQLCA.LogId =sle_userid.text

//SQLCA.AutoCommit = False

//SQLCA.DBParm = "Database='"+ls_database+"',Provider='SQLNCLI10',CommitOnDisconnect='No',OJSyntax='PB'"

---

I'm just worried if there's anything going to go wrong if I just change it like that..much testing will be needed.

Re: MSSQL : converting IMAGE to varbinary(max)

Former Member — Wed, 29 Oct 2014 11:33:48 GMT

That looks good & yes, in changing any DB drivers I would recommend thorough regression testing on your entire application - just to be safe.

Re: MSSQL : converting IMAGE to varbinary(max)

Former Member — Wed, 29 Oct 2014 12:47:33 GMT

I agree. Though I would recommend the SNC driver instead of ODBC for future use, it's not the thing you just change in a production system without much testing. It's for sure a litte "invest".

Re: MSSQL : converting IMAGE to varbinary(max)

Former Member — Thu, 06 Nov 2014 01:37:21 GMT

Hi all, I got an update from support

Previously I modified pbodb*.ini in these locations

C:\Program Files (x86)\Sybase\Shared\PowerBuilder

and

application directory (ie: dev directory c:\pb12.5compile and the network share where i host the .exe/.pbd \\serverA\pb12.5 )

and that works just fine for IMAGE datatype.

however, I was informed there are other locations for pbodb*.ini , so that also needs to be sync with on the development machine

C:\Users\[user]\AppData\Local\Sybase\PowerBuilder 12.5

C:\Users\[user]\AppData\Local\Sybase\PowerBuilder 12.6

I've tested it and it works fine now using ODBC and varbinary(max)