Importing SQL Server image type into bytea max size problem

[ghost]

Hi,

I'm trying to import binary data held in a column in SQL Server using the image data type. I map this to bytea and it doesn't error, however, the maximum size of my data seems to be capped at 64512 bytes. Is there is a simple reason for this?

Thanks!

[GeoffMontee]

This is probably related to issue #40. I haven't determined why FreeTDS is capping the length of some types yet.

[nextstopsun]

Any plans for fixing this?
This issue really ruins importing geometries, since they quite often exceed cap.

[kevinob11]

I'm running into this as well, any workarounds here?

[manio]

Guys,
I've got the following problem: I am using a tds_fdw foreign table with a blob (pdf) field. It is properly converted to bytea in postgres. The problem is with the bytea size, so I hope I am writing to proper issue here in github... :)

All my blobs are retrieved as 4096 bytes length, while in remote mssql db these are larger (and differ per row).

I was digging into this problem today and found a workaround.

First I can see that the problematic function is:
dbdatlen(festate->dbproc, ncol + 1);
It is returning 4096 for my blobs which is not true.

Next I found this thread:
https://lists.ibiblio.org/pipermail/freetds/2014q1/028781.html
According to this - I've executed another query right before fetching the data in tdsIterateForeignScan(), the query is:
'SET TEXTSIZE 2147483647'

Guess what? It is working!

Sample debug before:
DEBUG: tds_fdw: Data length is 4096
and after:
DEBUG: tds_fdw: Data length is 102265
The resulting bytea in postgres contains the valid-length pdf data :)

I can see in freetds NEWS file that in dblib there is:
' support DBTEXTSIZE option for dbsetopt;'
and also:
'- Added 'text size' config option which changes textsize on connect'
So maybe we can even pass it as tds_fdw server parameter?

I can prepare a pull-request with this change but I want to know if this is a good solution for this and if it will be accepted...

[GeoffMontee]

Hi @manio,

Thanks for the excellent debugging!

I can prepare a pull-request with this change but I want to know if this is a good solution for this and if it will be accepted...

Yes, it would be accepted. Thanks for the offer!

[manio]

Thanks @GeoffMontee,
PR is ready. I am throwing a warning instead of error when this call is not executed properly...

[kevinob11]

I'm irrationally excited about this thanks @manio!

[GeoffMontee]

Thanks for the PR, @manio! It has been merged.

[kevinob11]

@GeoffMontee any plans for a release? @manio any way to manually do this for now until a release?

[manio]

@kevinob11 just apply a patch and build it manually - IMHO no other way around before release. Maybe a jenkins produced some binaries - but I am not sure...