要往數據庫里面寫入一個長文本字段，這個字段是svg文件中polyline元素的points屬性。
設計時，數據庫字段設置為varchar(4000)
使用python讀取svg,將points屬性寫入數據庫，一切ok
使用python根據查詢數據庫，重新生成svg時，讀出來的points數據少了一大截，
剛學python，算了，不搞了
用Java寫了一個，一切ok
回頭看看，Java映射成的是varchar(MAX)
重新運行python，讀出來的數據一切ok
暈死，原來是varchar(4000)的原因，
附：
python3連接mssql麻煩，改用python2.6
連接使用pymssql-1.0.2.win32-py2.6.exe
轉點mssl的幫助，mark下
1.? What are the new data types or enhanced data types in SQL Server 2005??
??? Microsoft SQL Server 2005 introduces a new data type, the XML data type.? The XML data type lets you store XML documents and fragments in a SQL Server database.? An XML fragment is an XML instance that is missing a single top-level element.? You can create columns and variables of XML type and store XML instanced in them.
??? In addition to the new XML data type, Microsoft SQL Server 2005 has enhanced three of the existing SQL Server data types, namely the VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data types.? The VARCHAR(MAX) data type indicates that the maximum storage size for the VARCHAR data type is 2^31-1 bytes.? The NVARCHAR data types indicates that the maximum storage size fr the NVARCHAR data type is 2^31-1 bytes.? Lastly, the VARBINARY(MAX) data type indicates that the maximum storage size for the VARBINARY data type is 2^31-1 bytes.
2.? What’s the maximum length for VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data types?
??? The maximum storage size for VARCHAR(MAX) is 2^31-1 bytes (2,147,483,647 bytes or 2GB - 1 bytes).? The storage size is the actual length of data entered + 2 bytes.? The data entered can be 0 characters in length.? Since each character in a VARCHAR data type uses one byte, the maximum length for a VARCHAR(MAX) data type is 2,147,483,645.
?? The maximum storage size for NVARCHAR(MAX) is also 2^31-1 bytes (2,147,483,647 bytes or 2GB - 1 bytes).? The storage size, in bytes, is two times the number of characters entered + 2 bytes.? The data entered can be 0 characters in length.? Since each Unicode character in an NVARCHAR data type uses two bytes, the maximum length for an NVARCHAR(MAX) data type is 1,073,741,822.
?? The maximum storage size for VARBINARY(MAX) is the same as the maximum storage size for VARCHAR(MAX) and NVARCHAR(MAX), which is 2^31-1 (2,147,483,647 bytes or 2GB - 1 bytes).? The storage size is the actual length of the data entered + 2 bytes.? The data that is entered can be 0 bytes in length.
3.? Since you can use VARCHAR(MAX), can I define any length for a VARCHAR data type such as VARCHAR(10000)?
??? When the the length is specified in declaring a VARCHAR variable or column, the maximum length allowed is still 8000.? If the length is greater than 8000, you have to use the? MAX specifier as the length.? If a length greater than 8000 is specified, the following error will be encountered (assuming that the length specified is 10000):
??? Server: Msg 131, Level 15, State 3, Line 1
??? The size (10000) given to the type 'varchar' exceeds the maximum? allowed for any data type (8000).
4.? Will VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) replace TEXT, NTEXT and IMAGE data types?
?? Yes, VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) will replace the TEXT, NTEXT and IMAGE data types, respectively.? TEXT, NTEXT and IMAGE data types will be removed in the future version of Microsoft SQL Server.? Avoid using these data types when using SQL Server 2005 and use VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) instead.
5. Can I declare a local variable of VARCHAR(MAX), NVARCHAR(MAX) or VARBINARY(MAX)?
?? Yes, you can declare local variables of VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data types.? This is unlike the data types that these enhanced data types are replacing because declaring local variables of TEXT, NTEXT and IMAGE data types is not allowed.? You will encounter the following error message when a local variable of TEXT, NTEXT or IMAGE data type is defined:
??? Server: Msg 2739, Level 16, State 1, Line 7
??? The text, ntext, and image data types are invalid for local variables.
6.? If there are VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data types, are there also CHAR(MAX), NCHAR(MAX) and BINARY(MAX) data types?
??? No, there is no CHAR(MAX), NCHAR(MAX) or BINARY(MAX).? The main reason why the MAX specifier is not included for these data types is because these data types are fixed-length data.? If the MAX specifier was included for these data types, it will be a big waste of disk space as each column will consume 2GB-1 bytes even if only a short string value is assigned to the column.
7.? How can I make sure that my VARCHAR column will only contain 10,000 characters?? Defining it as VARCHAR(10000) generates an error of "The size (10000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000)."
To create a column of VARCHAR data type with a maximum of 10,000 characters, you declare the column as VARCHAR(MAX) data type and simply add a CHECK constraint to the column by checking the length of the column and making sure it is less than or equal to 10,000 characters.? To illustrate, here's how it will look like:
CREATE TABLE [dbo].[VarChar10000] ( [VarChar10000] VARCHAR(MAX) )
GO
ALTER TABLE [dbo].[VarChar10000]
??? ADD CONSTRAINT [MaxLength10000]
??? CHECK (DATALENGTH([VarChar10000]) <= 10000)
GO
With the column defined as VARCHAR(MAX), it can accept a string longer than 8,000 characters.? But with the CHECK constraint, it will not allow a string with a length of more than 10,000 characters.? To verify that the column will not allow a string of more than 10,000 characters, try executing the following INSERT command, which will insert a string value of 11,000 characters:
???? INSERT INTO [dbo].[VarChar10000] ( [VarChar10000] )
???? VALUES (REPLICATE(CAST('A' AS VARCHAR(MAX)), 11000))
The following error will be shown when the INSERT statement above is executed:
Server: Msg 547, Level 16, State 1, Line 1
The INSERT statement conflicted with the CHECK constraint "MaxLength10000".
The conflict occurred in database, table "dbo.VarChar10000", column 'VarChar10000'.
The statement has been terminated.
設計時，數據庫字段設置為varchar(4000)
使用python讀取svg,將points屬性寫入數據庫，一切ok
使用python根據查詢數據庫，重新生成svg時，讀出來的points數據少了一大截，
剛學python，算了，不搞了
用Java寫了一個，一切ok
回頭看看，Java映射成的是varchar(MAX)
重新運行python，讀出來的數據一切ok
暈死，原來是varchar(4000)的原因，
附：
python3連接mssql麻煩，改用python2.6
連接使用pymssql-1.0.2.win32-py2.6.exe
轉點mssl的幫助，mark下
1.? What are the new data types or enhanced data types in SQL Server 2005??
??? Microsoft SQL Server 2005 introduces a new data type, the XML data type.? The XML data type lets you store XML documents and fragments in a SQL Server database.? An XML fragment is an XML instance that is missing a single top-level element.? You can create columns and variables of XML type and store XML instanced in them.
??? In addition to the new XML data type, Microsoft SQL Server 2005 has enhanced three of the existing SQL Server data types, namely the VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data types.? The VARCHAR(MAX) data type indicates that the maximum storage size for the VARCHAR data type is 2^31-1 bytes.? The NVARCHAR data types indicates that the maximum storage size fr the NVARCHAR data type is 2^31-1 bytes.? Lastly, the VARBINARY(MAX) data type indicates that the maximum storage size for the VARBINARY data type is 2^31-1 bytes.
2.? What’s the maximum length for VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data types?
??? The maximum storage size for VARCHAR(MAX) is 2^31-1 bytes (2,147,483,647 bytes or 2GB - 1 bytes).? The storage size is the actual length of data entered + 2 bytes.? The data entered can be 0 characters in length.? Since each character in a VARCHAR data type uses one byte, the maximum length for a VARCHAR(MAX) data type is 2,147,483,645.
?? The maximum storage size for NVARCHAR(MAX) is also 2^31-1 bytes (2,147,483,647 bytes or 2GB - 1 bytes).? The storage size, in bytes, is two times the number of characters entered + 2 bytes.? The data entered can be 0 characters in length.? Since each Unicode character in an NVARCHAR data type uses two bytes, the maximum length for an NVARCHAR(MAX) data type is 1,073,741,822.
?? The maximum storage size for VARBINARY(MAX) is the same as the maximum storage size for VARCHAR(MAX) and NVARCHAR(MAX), which is 2^31-1 (2,147,483,647 bytes or 2GB - 1 bytes).? The storage size is the actual length of the data entered + 2 bytes.? The data that is entered can be 0 bytes in length.
3.? Since you can use VARCHAR(MAX), can I define any length for a VARCHAR data type such as VARCHAR(10000)?
??? When the the length is specified in declaring a VARCHAR variable or column, the maximum length allowed is still 8000.? If the length is greater than 8000, you have to use the? MAX specifier as the length.? If a length greater than 8000 is specified, the following error will be encountered (assuming that the length specified is 10000):
??? Server: Msg 131, Level 15, State 3, Line 1
??? The size (10000) given to the type 'varchar' exceeds the maximum? allowed for any data type (8000).
4.? Will VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) replace TEXT, NTEXT and IMAGE data types?
?? Yes, VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) will replace the TEXT, NTEXT and IMAGE data types, respectively.? TEXT, NTEXT and IMAGE data types will be removed in the future version of Microsoft SQL Server.? Avoid using these data types when using SQL Server 2005 and use VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) instead.
5. Can I declare a local variable of VARCHAR(MAX), NVARCHAR(MAX) or VARBINARY(MAX)?
?? Yes, you can declare local variables of VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data types.? This is unlike the data types that these enhanced data types are replacing because declaring local variables of TEXT, NTEXT and IMAGE data types is not allowed.? You will encounter the following error message when a local variable of TEXT, NTEXT or IMAGE data type is defined:
??? Server: Msg 2739, Level 16, State 1, Line 7
??? The text, ntext, and image data types are invalid for local variables.
6.? If there are VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data types, are there also CHAR(MAX), NCHAR(MAX) and BINARY(MAX) data types?
??? No, there is no CHAR(MAX), NCHAR(MAX) or BINARY(MAX).? The main reason why the MAX specifier is not included for these data types is because these data types are fixed-length data.? If the MAX specifier was included for these data types, it will be a big waste of disk space as each column will consume 2GB-1 bytes even if only a short string value is assigned to the column.
7.? How can I make sure that my VARCHAR column will only contain 10,000 characters?? Defining it as VARCHAR(10000) generates an error of "The size (10000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000)."
To create a column of VARCHAR data type with a maximum of 10,000 characters, you declare the column as VARCHAR(MAX) data type and simply add a CHECK constraint to the column by checking the length of the column and making sure it is less than or equal to 10,000 characters.? To illustrate, here's how it will look like:
CREATE TABLE [dbo].[VarChar10000] ( [VarChar10000] VARCHAR(MAX) )
GO
ALTER TABLE [dbo].[VarChar10000]
??? ADD CONSTRAINT [MaxLength10000]
??? CHECK (DATALENGTH([VarChar10000]) <= 10000)
GO
With the column defined as VARCHAR(MAX), it can accept a string longer than 8,000 characters.? But with the CHECK constraint, it will not allow a string with a length of more than 10,000 characters.? To verify that the column will not allow a string of more than 10,000 characters, try executing the following INSERT command, which will insert a string value of 11,000 characters:
???? INSERT INTO [dbo].[VarChar10000] ( [VarChar10000] )
???? VALUES (REPLICATE(CAST('A' AS VARCHAR(MAX)), 11000))
The following error will be shown when the INSERT statement above is executed:
Server: Msg 547, Level 16, State 1, Line 1
The INSERT statement conflicted with the CHECK constraint "MaxLength10000".
The conflict occurred in database, table "dbo.VarChar10000", column 'VarChar10000'.
The statement has been terminated.
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
