| View previous topic :: View next topic |
| Author |
Message |
chris421
Joined: 07 Apr 2005
Posts: 53
|
 Posted: Mon Jun 26, 2006 8:03 Post subject: SQL2005 GetPersistentObject Prob |
 |
|
Wanted to migrate from MySQL to MS SQL 2005 (SP1).
After much converting syntax betwen the MySQL scripts to MS SQL, I finally did get the NWN database recreated.
Having a problem now with BLOB select statements, (possibly inserts as well).
Since MS SQL has no BLOB variable type, I chose "image"...binary, very large size, etc.
When I store (SetPersistentObject) a copy of a simple item, I see its record. Whether or not the data is complete trash, I'm not sure.
But a definite problem occurs when I attempt to select it (GetPersistentObject)
This from my NWNX ODBC log:
| Quote: | o Got request (scorco): SELECT val FROM pwbankdata WHERE player='chris421' AND tag='Kagome' AND name='Item_0'
! SQL Error: Critical error - object too large (>1048576 bytes). |
No way all the item objects I tried to store are more than a meg a piece. Any ideas?
Thanks. |
|
| Back to top |
|
 |
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Mon Jun 26, 2006 14:00 Post subject: Re: SQL2005 GetPersistentObject Prob |
|
|
| chris421 wrote: | Wanted to migrate from MySQL to MS SQL 2005 (SP1).
After much converting syntax betwen the MySQL scripts to MS SQL, I finally did get the NWN database recreated.
Having a problem now with BLOB select statements, (possibly inserts as well).
Since MS SQL has no BLOB variable type, I chose "image"...binary, very large size, etc.
When I store (SetPersistentObject) a copy of a simple item, I see its record. Whether or not the data is complete trash, I'm not sure.
But a definite problem occurs when I attempt to select it (GetPersistentObject)
This from my NWNX ODBC log:
| Quote: | o Got request (scorco): SELECT val FROM pwbankdata WHERE player='chris421' AND tag='Kagome' AND name='Item_0'
! SQL Error: Critical error - object too large (>1048576 bytes). |
No way all the item objects I tried to store are more than a meg a piece. Any ideas?
Thanks. |
You have to define the data type as varchar(<size>) or varbinary(<size>)
What is happening is that ODBC(SQLServer2005) is returning 0xFFFFFFFF (-1) to signify the 'largeobject' - normally this is the datatype size - NWNX uses an unsigned int for this - so 0xFFFFFFFF is a very large number (4294967295).
Hence your error.
To avoid this issue specify the maximum size of the datatype (ie 8000 - 1
block)
Cheers
Gryphyn |
|
| Back to top |
|
|
odenien
Joined: 26 Sep 2005
Posts: 37
|
| Posted: Mon Jun 26, 2006 14:19 Post subject: |
|
|
For ODBC column size should be a signed int and programmed that way. When describing the image column the ODBC driver should report the max size as max (signed int) or whatever SET TEXTSIZE (not sure of exact syntax) has been set to.
You might be able to set the text/image max limit on the server, to get around this problem. And the ODBC add-on should probably be changed to use signed values for column sizes. |
|
| Back to top |
|
|
Papillon
x-man
Joined: 28 Dec 2004
Posts: 1060
Location: Germany
|
| Posted: Tue Jun 27, 2006 8:24 Post subject: |
|
|
Signed int (16bit) means a max value of 32.000 something. How would that help in this case ?
IIRC, I use a fixed buffer to read the blob into. This is where the maximum size limit comes from. If the server returns the maximum possible size for the column instead of the actual blob size, that would be a bit ... inconvenient.
_________________
Papillon |
|
| Back to top |
|
|
chris421
Joined: 07 Apr 2005
Posts: 53
|
| Posted: Tue Jun 27, 2006 9:22 Post subject: |
|
|
Redefining the col to varbinary(8000) works for storing and retrieving objects smaller than 8K--too small for many objects.
Attempted to create a User-Defined Data Type.
Tried "image" as my data type template. Wanted to cap it to 1048575 bytes. Unfortunately size can't be altered. The other potential binary template types weren't much larger than 8K.
So I guess the problem is how to prevent NWNX from learning the max size of the image datatype. |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Wed Jun 28, 2006 0:34 Post subject: |
|
|
| chris421 wrote: | Redefining the col to varbinary(8000) works for storing and retrieving objects smaller than 8K--too small for many objects.
|
What objects are you storing that are more than 8K?
Most objects -inventory stuff- is in the 2-4 range.
If you have larger objects you can make the number (size) larger. 8K is just the SQLServer2005 block size, objects can be larger than 1 block* - your only criteria is not to exceed the ODBC cap [31^2]
*this was a limit in SQLServer2000
Cheers
Gryphyn |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Wed Jun 28, 2006 0:43 Post subject: |
|
|
| Papillon wrote: | Signed int (16bit) means a max value of 32.000 something. How would that help in this case ?
IIRC, I use a fixed buffer to read the blob into. This is where the maximum size limit comes from. If the server returns the maximum possible size for the column instead of the actual blob size, that would be a bit ... inconvenient. |
The fact that you get a 0xFFFFFFFF (-1) signifies that the length is 'undetermined'. (At least in SQLServer). You then need to use other mechanisms to make sure you retrieve all the data (size might be encoded in the first 4bytes [bigint]) But this would involve changing NWNX which then might not work for all databases.
Cheers
Gryphyn |
|
| Back to top |
|
|
chris421
Joined: 07 Apr 2005
Posts: 53
|
| Posted: Wed Jun 28, 2006 4:28 Post subject: |
|
|
| Quote: | What objects are you storing that are more than 8K?
Most objects -inventory stuff- is in the 2-4 range. |
Persistent Henchmen for starters. Had a pretty slick working system under MySQL.
| Quote: | | If you have larger objects you can make the number (size) larger. 8K is just the SQLServer2005 block size, objects can be larger than 1 block* - your only criteria is not to exceed the ODBC cap [31^2] |
Don't know what to tell you. The length of the image datatype appears to be unalterable. And both via sp_addtype and the GUI, varbinary caps at 8000.
The image cap (or lack thereof) creates an error condition for NWNX, and 8000 bytes is too small for me.
Unless someone has a stroke of genius, I'd say for persistent NWN object storage SQL2005 would not be the platform of choice. I missed Navicat anyway.  |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Wed Jun 28, 2006 6:27 Post subject: |
|
|
| chris421 wrote: | | Quote: | What objects are you storing that are more than 8K?
Most objects -inventory stuff- is in the 2-4 range. |
Persistent Henchmen for starters. Had a pretty slick working system under MySQL.
| Quote: | | If you have larger objects you can make the number (size) larger. 8K is just the SQLServer2005 block size, objects can be larger than 1 block* - your only criteria is not to exceed the ODBC cap [31^2] |
Don't know what to tell you. The length of the image datatype appears to be unalterable. And both via sp_addtype and the GUI, varbinary caps at 8000.
The image cap (or lack thereof) creates an error condition for NWNX, and 8000 bytes is too small for me.
Unless someone has a stroke of genius, I'd say for persistent NWN object storage SQL2005 would not be the platform of choice. I missed Navicat anyway. |
I'm using a varbinary(4000) for inventory items, this works fine.
No reason - that I can see - that you can't set this to more than 8000.
Don't use the image or text data-types - they return the (-1)
NWNX already handles retrieval of 'chucks' of data, you should be fine up to varbinary(32000). I'll check the doco again...
[Ed: Yep, 8000 seems to be the limit - and if you change it to a varbinary(max) you end up with the same (-1) being returned.
The first 2 bytes of what is returned is the actual length of the data (+ the 2 bytes) - a change to NWNX]
[Ed2: Even if you could fake the column size setting SQLServer2005 takes ANY column with more that 8000 bytes and treats it as a BLOB (this is all hard-coded *grrrr*). As a consequence ODBC will always return a (-1) to NWNX for these columns. *A new NWNX plugin is required for SQLServer2005, to handle this*]
Cheers
Gryphyn |
|
| Back to top |
|
|
Papillon
x-man
Joined: 28 Dec 2004
Posts: 1060
Location: Germany
|
| Posted: Thu Jun 29, 2006 11:56 Post subject: |
|
|
@Gryphyn: Could you please post a link to the relevant part of the documentation ? It shouldn't be hard to update the ODBC plugin for this case.
_________________
Papillon |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Fri Jun 30, 2006 2:27 Post subject: |
|
|
Link
| Quote: | SQL Server 2005 Books Online
SQLDescribeCol
For executed statements, the SQL Native Client ODBC driver does not need to query the server to describe columns in a result set. In this case, SQLDescribeCol does not cause a server roundtrip. Like SQLColAttribute and SQLNumResultCols, calling SQLDescribeCol on prepared but not executed statements generates a server roundtrip.
When a Transact-SQL statement or statement batch returns multiple result row sets, it is possible for a column, referenced by ordinal, to originate in a separate table or to refer to an entirely different column in the result set. SQLDescribeCol should be called for each set. When the result set changes, the application should rebind data values prior to fetching row results. For more information about handling multiple result set returns, see SQLMoreResults.
The SQL Native Client ODBC driver uses the SET FMTONLY statement to reduce server overhead when SQLDescribeCol is called for prepared but not executed statements. Column attributes are reported for only the first result set when multiple result sets are generated by a prepared batch of SQL statements.
For large value data types, the value returned in DataTypePtr is SQL_VARCHAR, SQL_VARBINARY, or SQL_NVARCHAR. A value of SQL_SS_LENGTH_UNLIMITED in ColumnSizePtr indicates that the size is “unlimited”. |
and link
| Quote: | SQL Server 2005 Books Online
SQLGetTypeInfo
The SQL Native Client ODBC driver reports the additional column USERTYPE in the result set of SQLGetTypeInfo. USERTYPE reports the DB-Library data type definition and is useful to developers porting existing DB-Library applications to ODBC.
SQL Server treats identity as an attribute, whereas ODBC treats it as a data type. To resolve this mismatch, SQLGetTypeInfo returns the data types: int identity, smallint identity, tinyint identity, decimal identity, and numeric identity. The SQLGetTypeInfo result set column AUTO_UNIQUE_VALUE reports the value TRUE for these data types.
For varchar, nvarchar and varbinary, the SQL Native Client ODBC driver continues to report 8000, 4000 and 8000 respectively for the COLUMN_SIZE value, even though it is actually unlimited. This is to ensure backward compatibility.
For the xml data type, the SQL Native Client ODBC driver reports SQL_SS_LENGTH_UNLIMITED for COLUMN_SIZE to denote unlimited size. |
Still finding the evidence...but this is a start
Link to usage
I haven't been able to confirm that SQL_SS_LENGTH_UNLIMITED == -1 but the evidence from a ODBC Trace indicates this to be the case.
The doco seem to suggest that this is 0(Zero), but if that is the case MAX_SIZE would not be exceeded.
Cheers
Gryphyn |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
