BLOB fields in MSSQL Server

[Jose]

Hello everyone

I have the following:

Web2py on Freebsd.
Database: SQLServer 2008

Connection: web2py <-> pyodbc <-> UnixODBC <-> FreeTDS <-> SQLServer

I'm trying to store some uploaded files into a blob field, instead than on the file system.

The table was created by the administrator SQLServer

CREATE TABLE [dbo].[DocumentosBlob](
[Id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[Nombre] [varchar](50) NULL,
[Archivo] [varchar](512) NULL,
[Archivo_Blob] [text] NULL,
[Archivo_Blob2] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The adapter defines SQLServer blob fields as IMAGE ('blob': 'IMAGE',). Then use for testing the Archivo_Blob2 field is set to IMAGE
In my model I have:

tb_documentos_blob = db.define_table('DocumentosBlob',
    Field('nombre', length=50),
    Field('archivo', 'upload', uploadfield='archivo_blob2'),
    Field('archivo_blob2', 'blob', writable=False, readable=False),
    migrate=False,
)

But it happens that when trying to upload a file upload the following error occurs:

DataError: ('22018', '[22018] [FreeTDS][SQL Server]Operand type clash: text is incompatible with image (206) (SQLExecDirectW)')

Then I changed the field, using one type TEXT (Archivo_Blob). The model will now be:

tb_documentos_blob = db.define_table('DocumentosBlob',
    Field('nombre', length=50),
    Field('archivo', 'upload', uploadfield='archivo_blob'),
    Field('archivo_blob', 'blob', writable=False, readable=False),
    migrate=False,
)

Now it works, I can upload and download files, but only with very small files. Tested with 20KB. With one of 250KB upload it, but at download it is broken. I imagine that was truncated when stored in the database.

How I can fix this?

Best Regards
José

[Richard Vézina]

You say uploading don't works? What is you web server? Nginx were having a bug and limit the size of the file...

Also, why you want to put your file in blob... I am going away from that, it bloat database and make db dump longer, etc.

Richard

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jose]

El martes, 10 de febrero de 2015, 13:01:36 (UTC-3), Richard escribió:

You say uploading don't works? What is you web server? Nginx were having a bug and limit the size of the file...

It is not a server problem. This web2py internal server using for development. The problem is the field type.
 

[Richard Vézina]

You may look to adpater side, I guess SQL Server adapter is not creating blob type correctly for SQL server...

Richard

[Raul Monares]

I had a similar problem with truncated info in blob fields. This line fixed for me:

    db.executesql('SET TEXTSIZE 2147483647')

right after the DAL connection

[Niphlod]

this has nothing to do with the aforementioned error (operand type clash).
In addition to that, TEXTSIZE just limits what a text, image, varbinary, varchar field is allowed to be fetched, not stored.
Since web2py is using odbc, and odbc uses the max limit that you mention by default, if you're seeing something truncated it's not a web2py issue, but something related to your middleware setup (unixodbc or freetds). According to the docs, freetds sets implicitely 4294967295 (4 GB), so if you're not setting it explicitely, you should be good to go without further issues.

[Niphlod]

But it happens that when trying to upload a file upload the following error occurs:

DataError: ('22018', '[22018] [FreeTDS][SQL Server]Operand type clash: text is incompatible with image (206) (SQLExecDirectW)')

This is perfectly fine. web2py adopts a convention that simplifies the code a lot when dealing with different adapters: it stores the content as a base64 encoded string into TEXT fields. No wonder that the insert fails on an "image" field.
 

Then I changed the field, using one type TEXT (Archivo_Blob). The model will now be:

tb_documentos_blob = db.define_table('DocumentosBlob',
    Field('nombre', length=50),
    Field('archivo', 'upload', uploadfield='archivo_blob'),
    Field('archivo_blob', 'blob', writable=False, readable=False),
    migrate=False,
)

Now it works, I can upload and download files, but only with very small files. Tested with 20KB. With one of 250KB upload it, but at download it is broken. I imagine that was truncated when stored in the database.

this model is correctly tied to the underlying structure (and web2py "conventions"). Read my previous post on TEXTSIZE setting throughout your middlewares.

[Jose]

this model is correctly tied to the underlying structure (and web2py "conventions"). Read my previous post on TEXTSIZE setting throughout your middlewares.

OK, I'm going to try.
Thank You

[Jose]

SOLVED.

I changed the "text size" of the connection, the configuration of FreeTDS (freetds.conf).
I tried with large files and it works great

Thank you very much for the help

[Niphlod]

no problem. mssql is to blame for some things, but this is definitely not one of them ^_^