Thread: [SQLObject] SQL Server varchar(4000) instead of TEXT
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Davide A. <dav...@gm...> - 2007-10-04 10:53:36
|
Hi, I've go a report of a problem with Microsoft SQL Server 9.0: I've a SQLObject database structure which uses a number of UnicodeCol columns without length limit. In other database servers - MySQL, PostgreSQL, ... - these columns are correctly mapped to TEXT types, while in SQL Server a VARCHAR(4000) is used. Looks like this choice is forced in the SOStringLikeCol._mssqlType() method, in the col.py file. My questions are: 1. why this choice? Performances, I assume, but it seems a bit rude to me: I expected to get an unlimited field. 2. any work-around? Modifying SQLObject is not an option: mine is a free software application, and I have no control of the installation base. Thanks! -- Davide Alberani <davide.alberani _at_ gmail.com> [PGP KeyID: 0x465BFD47] http://erlug.linux.it/~da/ <http://erlug.linux.it/%7Eda/> |
From: Oleg B. <ph...@ph...> - 2007-10-04 11:01:59
|
On Thu, Oct 04, 2007 at 12:53:26PM +0200, Davide Alberani wrote: > I've go a report of a problem with Microsoft SQL Server 9.0: I've a > SQLObject database structure which uses a number of UnicodeCol > columns without length limit. > In other database servers - MySQL, PostgreSQL, ... - these > columns are correctly mapped to TEXT types, while in SQL Server > a VARCHAR(4000) is used. Does MSSQL Server have an unlimited (or reasonably big) TEXT type? Since what version? (if I am going to change the type I have at least to document the lower version of MSSQL that SQLObject requires.) > 2. any work-around? Monkey-patching (if you know what the term means). Or answer my previous question and wait until a fix appears in a stable release. Or create tables outside SQLObject. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Davide A. <dav...@gm...> - 2007-10-04 15:16:33
|
2007/10/4, Oleg Broytmann <ph...@ph...>: > > On Thu, Oct 04, 2007 at 12:53:26PM +0200, Davide Alberani wrote: > Does MSSQL Server have an unlimited (or reasonably big) TEXT type? I've never used it directly, I'll have to ask for the details: my contact tried to change 'VARCHAR(4000)' to 'TEXT', in col.py, and it worked (slow as hell, but that's probably a SQLServer issue). Since what version? I can find documentation (in Italian), citing TEXT (up to 2147483647 chars) and NTEXT for unicode (up to 1073741823 chars). It refers to SQL Server 7.0. > > 2. any work-around? > > Monkey-patching (if you know what the term means). Yup - that's always an option, but I prefer to consider it a last resort. Thank you! -- Davide Alberani <dav...@gm...> [PGP KeyID: 0x465BFD47] http://erlug.linux.it/~da/ |
From: Davide A. <dav...@gm...> - 2007-10-06 11:34:39
|
2007/10/4, Davide Alberani <dav...@gm...>: > > Does MSSQL Server have an unlimited (or reasonably big) TEXT type? > > > I've never used it directly, I'll have to ask for the details > Done that. Steven Ovits (who I wish to thank), says: ============================================== SQL Server 6.5 (and 7.0) supported text columns, but they were "out-of-line", meaning they were stored in a separate data area, not on the main pages. You did a normal select using a pointer type, then you passed the pointer to another other api to get the actual data. It was a pain, and not portable at all, although... This is exactly how binary data was handled by a lot of databases. Apparently, this was "fixed" in the 2000 release, and the data is now stored inline (or it's an option or something like that). The tradeoff is you have to scan several pages for each row when you do a query that's not based on an index--when you need to actually scan the data itself. Since noone really puts an index on large text columns, and it's not needed in every query, it makes sense to store those large data objects separately. I have no idea which databases do it which way for varchar. Also, v6.5 was limited to varchar(255) and in 7.0 it was maybe varchar(4000). Here's the same info online. http://www.sqlmag.com/Article/ArticleID/26852/sql_server_26852.html ============================================== I hope this helps. -- Davide Alberani <dav...@gm...> [PGP KeyID: 0x465BFD47] http://erlug.linux.it/~da/ |
From: Davide A. <dav...@gm...> - 2007-10-12 06:02:45
|
2007/10/10, Oleg Broytmann <ph...@ph...>: > > Ok, waiting... > Here we are; Steven Ovits (thanks!) pointed some documentation to me. Short version: TEXT and NTEXT are deprecated since SQL Server 2005, but they still work today. Instead, they suggest to use VARCHAR(MAX) and NVARCHAR(MAX) - yes, with this exact syntax; they have a hard cap of 2GB for field. Performances are OK. I gladly leave to you the decisions about what/how to support. :-) Some info: http://msdn2.microsoft.com/en-us/library/ms143729.aspx http://www.teratrax.com/articles/varchar_max.html http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1098157,00.html -- Davide Alberani <dav...@gm...> [PGP KeyID: 0x465BFD47] http://erlug.linux.it/~da/ |
From: Oleg B. <ph...@ph...> - 2007-10-12 07:43:57
|
On Fri, Oct 12, 2007 at 08:02:42AM +0200, Davide Alberani wrote: > Here we are; Steven Ovits (thanks!) pointed some documentation to me. > > Short version: TEXT and NTEXT are deprecated since SQL Server 2005, > but they still work today. > Instead, they suggest to use VARCHAR(MAX) and NVARCHAR(MAX) - yes, > with this exact syntax; they have a hard cap of 2GB for field. > Performances are OK. > > I gladly leave to you the decisions about what/how to support. :-) > > Some info: > http://msdn2.microsoft.com/en-us/library/ms143729.aspx > http://www.teratrax.com/articles/varchar_max.html > > http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1098157,00.html Thank you. I will put that "(MAX)" into the code. Also I thing I need to replace "IMAGE" type for BLOBCol with "VARCHAR(MAX)". Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2007-10-04 15:41:17
|
On Thu, Oct 04, 2007 at 05:16:29PM +0200, Davide Alberani wrote: > Since what version? > > I can find documentation (in Italian), citing TEXT (up to 2147483647 chars) > and NTEXT for > unicode (up to 1073741823 chars). It refers to SQL Server 7.0. Thank you! There is custom sqlType that's supposed to be used like this: class MyTable(): col = StringCol(sqlType="NTEXT") but it isn't properly implemented. It seems it is used only for StringCol and for a limited list of backends. I will work to extend it to all backends at least for StringCol and UnicodeCol. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2007-10-09 15:45:27
|
On Thu, Oct 04, 2007 at 07:41:13PM +0400, Oleg Broytmann wrote: > There is custom sqlType that's supposed to be used like this: > > class MyTable(): > col = StringCol(sqlType="NTEXT") Fixed for StringCol and UnicodeCol with MSSQL in the revisions 3024-3028 (branches 0.7, 0.8, 0.9, the trunk and docs.) Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2007-10-09 18:54:06
|
On Tue, Oct 09, 2007 at 08:00:06PM +0200, Davide Alberani wrote: > In the trunk I still see "type = 'varchar(4000)'" at line #481 of col.py, in > the _mssqlType() > method of the SOStringLikeCol class. > Or your "fixed" was not referred to the VARCHAR(4000) instead of TEXT > problem? I didn't replace VARCHAR with TEXT. Instead I allowed you to replace it with whatever you want: class MyTable(): col = StringCol(sqlType="NTEXT") sqlType was available before, but wasn't used with MSSQL; I fixed the bug, so sqlType now works for all backends. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2007-10-15 15:07:07
|
On Fri, Oct 12, 2007 at 06:53:41PM +0200, Davide Alberani wrote: > Moreover, I suppose these "MAX" types will break compatibility with versions > older than SQL Server 2005; I don't know if there's a better way to > implement support for this types of columns, sorry. How can I ask the version of the server? Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Davide A. <dav...@gm...> - 2007-10-17 12:45:29
|
2007/10/15, Oleg Broytmann <ph...@ph...>: > > > How can I ask the version of the server? > See these instructions (again suggested by Steven Ovits): http://support.microsoft.com/?id=321185 I'm more and more persuaded that the "S" of "SQL" means "Structured" and not "Standard". ;-) -- Davide Alberani <dav...@gm...> [PGP KeyID: 0x465BFD47] http://erlug.linux.it/~da/ |
From: Oleg B. <ph...@ph...> - 2007-10-17 13:02:24
|
On Wed, Oct 17, 2007 at 02:45:24PM +0200, Davide Alberani wrote: > 2007/10/15, Oleg Broytmann <ph...@ph...>: > > How can I ask the version of the server? > > See these instructions (again suggested by Steven Ovits): > http://support.microsoft.com/?id=321185 Thank you. That is, if I need to test if SQL Server support MAX-types, I have to try to issue the query SELECT @@VERSION If it fails - it is SQL Server 2005. If the query returns a result - I have a server version 5.0+, and any version of it supports MAX-types. Right? > I'm more and more persuaded that the "S" of "SQL" means "Structured" and not > "Standard". ;-) Could it depend on the previous letters? If the letters are 'M' and 'S' - does it make things better or worse? ;) Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Davide A. <dav...@gm...> - 2007-10-12 16:53:44
|
2007/10/12, Oleg Broytmann <ph...@ph...>: > > > Thank you. I will put that "(MAX)" into the code. Also I thing I need > to > replace "IMAGE" type for BLOBCol with "VARCHAR(MAX)". I think "VARBINARY(MAX)" would be a better choice. Usual disclaimer: I've never directly used MS SQL Server/SQLExpress, so I don't really know how well it will work; if anyone has direct experience, please report. :-) Moreover, I suppose these "MAX" types will break compatibility with versions older than SQL Server 2005; I don't know if there's a better way to implement support for this types of columns, sorry. Thanks! -- Davide Alberani <dav...@gm...> [PGP KeyID: 0x465BFD47] http://erlug.linux.it/~da/ |
From: Oleg B. <ph...@ph...> - 2007-10-12 17:08:06
|
On Fri, Oct 12, 2007 at 06:53:41PM +0200, Davide Alberani wrote: > 2007/10/12, Oleg Broytmann <ph...@ph...>: > > Thank you. I will put that "(MAX)" into the code. Also I thing I need > > to > > replace "IMAGE" type for BLOBCol with "VARCHAR(MAX)". > > I think "VARBINARY(MAX)" would be a better choice. Thank you! > I suppose these "MAX" types will break compatibility with versions > older than SQL Server 2005 I will handle this by not changing current stable branches and only change the trunk that will be released as version 0.10. There are many deep changes in the trunk so it requires a long beta period. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Davide A. <dav...@gm...> - 2007-10-09 18:00:14
|
2007/10/9, Oleg Broytmann <ph...@ph...>: > > On Thu, Oct 04, 2007 at 07:41:13PM +0400, Oleg Broytmann wrote: > > There is custom sqlType that's supposed to be used like this: > > > > class MyTable(): > > col = StringCol(sqlType="NTEXT") > > Fixed for StringCol and UnicodeCol with MSSQL in the revisions > 3024-3028 > (branches 0.7, 0.8, 0.9, the trunk and docs.) In the trunk I still see "type = 'varchar(4000)'" at line #481 of col.py, in the _mssqlType() method of the SOStringLikeCol class. Or your "fixed" was not referred to the VARCHAR(4000) instead of TEXT problem? Thanks, -- Davide Alberani <dav...@gm...> [PGP KeyID: 0x465BFD47] http://erlug.linux.it/~da/ |
From: Davide A. <dav...@gm...> - 2007-10-10 06:19:00
|
2007/10/9, Oleg Broytmann <ph...@ph...>: I didn't replace VARCHAR with TEXT. Instead I allowed you to replace it > with whatever you want: > > class MyTable(): > col = StringCol(sqlType="NTEXT") Understood. Any plan to just let SQL Server use TEXT/NTEXT? After all _it is_ supported by the database. (maybe I'm missing something about how SQLObject or databases work in general, sorry) Thanks! -- Davide Alberani <dav...@gm...> [PGP KeyID: 0x465BFD47] http://erlug.linux.it/~da/ |
From: Oleg B. <ph...@ph...> - 2007-10-10 06:29:03
|
On Wed, Oct 10, 2007 at 08:18:58AM +0200, Davide Alberani wrote: > Any plan to just let SQL Server use TEXT/NTEXT? Despite the fact that it's so slow? An how does SQLObject decide what to use - TEXT or NTEXT? TEXT for StringCol and NTEXT for UnicodeCol? Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Davide A. <dav...@gm...> - 2007-10-10 11:39:47
|
2007/10/10, Oleg Broytmann <ph...@ph...>: > > > Any plan to just let SQL Server use TEXT/NTEXT? > > Despite the fact that it's so slow? Long story short: it was slow on a free edition; an update to the developer version showed good results with TEXT and NTEXT (yes: usable respectively for strings and unicode strings), BUT... it seems that TEXT and NTEXT types for some (to me) unintelligible reason were deprecated in SQL Server 2005 and will be removed in a future version. I'll do some more research. Very strange choice for a database, if you ask me. :-) HTH, -- Davide Alberani <dav...@gm...> [PGP KeyID: 0x465BFD47] http://erlug.linux.it/~da/ |
From: Oleg B. <ph...@ph...> - 2007-10-10 13:27:01
|
On Wed, Oct 10, 2007 at 01:39:45PM +0200, Davide Alberani wrote: > BUT... it seems that TEXT and NTEXT types for some (to > me) > unintelligible reason were deprecated in SQL Server 2005 and will be removed > in a future version. > > I'll do some more research. Ok, waiting... Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |