search

tds-fdw / tds_fdw

A PostgreSQL foreign data wrapper to connect to TDS databases (Sybase and Microsoft SQL Server)
Other
381 stars 102 forks source link

Importing SQL Server image type into bytea max size problem #81

Closed ghost closed 3 years ago

ghost commented 8 years ago

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 commented 8 years ago

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

nextstopsun commented 4 years ago

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

kevinob11 commented 3 years ago

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

manio commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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

kevinob11 commented 3 years ago

I'm irrationally excited about this thanks @manio!

GeoffMontee commented 3 years ago

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

kevinob11 commented 3 years ago

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

manio commented 3 years ago

@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...