Going Nuts - TEXT/Blobs need to be the last field in a table

Going Nuts - TEXT/Blobs need to be the last field in a table

Post by Dion Wiggin » Sun, 28 Dec 1997 04:00:00



Spent 5 hours trying to work out why when I change the value of a totally
unrelated column, the results come back different from a SQL Query.
I found a nice bug in SQL Server. You *MUST* put your blobs last in your
table.

The below structure works correctly:

CREATE TABLE dbo.QuestFields (
 QuestID int NOT NULL ,
 FieldID int NOT NULL ,
 EffDate datetime NOT NULL ,
 UpdateID int NOT NULL ,
 FieldProperties text NOT NULL
)

The next one does not:

CREATE TABLE dbo.QuestFields (
 QuestID int NOT NULL ,
 FieldID int NOT NULL ,
 EffDate datetime NOT NULL ,
 FieldProperties text NOT NULL ,
 UpdateID int NOT NULL
)

A very simple select was used:

SELECT * FROM QuestFields WHERE Field QuestID = 17

If the value of UpdateID is not 0 then the blob returned is the incorrect
blob. If it is 0 then it returns the correct data.

By changing the order of the fields the problem went away.

It works fine in ISQL, but fails using RDO in VB5 via ODBC unless the field
order is adjusted.

Has anyone else had similar problems?

Dion

 
 
 

Going Nuts - TEXT/Blobs need to be the last field in a table

Post by Roy Harv » Sun, 28 Dec 1997 04:00:00


Dion,

Quote:>Spent 5 hours trying to work out why when I change the value of a totally
>unrelated column, the results come back different from a SQL Query.
>I found a nice bug in SQL Server. You *MUST* put your blobs last in your
>table.

There was a known bug with SP2 for 6.5and image or text columns;
putting the column last was at least a partial workaround.  This was
supposed to be fixed in SP3 and SP4.  What service pack level are you
at?

Roy

 
 
 

Going Nuts - TEXT/Blobs need to be the last field in a table

Post by Cláudio A Rodrigue » Sun, 28 Dec 1997 04:00:00


I wouldn't say it's a bug because this behavior is documented. You must put
your long fields on the right of the select statement. Let's say it's a
undesired behavior...  :)


> Spent 5 hours trying to work out why when I change the value of a totally
> unrelated column, the results come back different from a SQL Query.
> I found a nice bug in SQL Server. You *MUST* put your blobs last in your
> table.

> The below structure works correctly:

> CREATE TABLE dbo.QuestFields (
>  QuestID int NOT NULL ,
>  FieldID int NOT NULL ,
>  EffDate datetime NOT NULL ,
>  UpdateID int NOT NULL ,
>  FieldProperties text NOT NULL
> )

> The next one does not:

> CREATE TABLE dbo.QuestFields (
>  QuestID int NOT NULL ,
>  FieldID int NOT NULL ,
>  EffDate datetime NOT NULL ,
>  FieldProperties text NOT NULL ,
>  UpdateID int NOT NULL
> )

> A very simple select was used:

> SELECT * FROM QuestFields WHERE Field QuestID = 17

> If the value of UpdateID is not 0 then the blob returned is the incorrect
> blob. If it is 0 then it returns the correct data.

> By changing the order of the fields the problem went away.

> It works fine in ISQL, but fails using RDO in VB5 via ODBC unless the field
> order is adjusted.

> Has anyone else had similar problems?

> Dion

--
Cludio Avolio Rodrigues
Systems Especialist
IBM GS

Home page: http://www.geocities.com/SunsetStrip/4994
 
 
 

Going Nuts - TEXT/Blobs need to be the last field in a table

Post by Cláudio A Rodrigue » Sun, 28 Dec 1997 04:00:00


Actually I saw something about it readnig ADO papers on MS Site I couldn't tell
you exactly where but was there. I'm sorry.


> Hi Cludio, thanks for the tip. Can you point me in the direction of the
> documentation you mentioned. I've looked in the help and in the knowledge
> base, but could not find anything.

> Cheers

> Dion


> >I wouldn't say it's a bug because this behavior is documented. You must put
> >your long fields on the right of the select statement. Let's say it's a
> >undesired behavior...  :)


> >> Spent 5 hours trying to work out why when I change the value of a totally
> >> unrelated column, the results come back different from a SQL Query.
> >> I found a nice bug in SQL Server. You *MUST* put your blobs last in your
> >> table.

> >> The below structure works correctly:

> >> CREATE TABLE dbo.QuestFields (
> >>  QuestID int NOT NULL ,
> >>  FieldID int NOT NULL ,
> >>  EffDate datetime NOT NULL ,
> >>  UpdateID int NOT NULL ,
> >>  FieldProperties text NOT NULL
> >> )

> >> The next one does not:

> >> CREATE TABLE dbo.QuestFields (
> >>  QuestID int NOT NULL ,
> >>  FieldID int NOT NULL ,
> >>  EffDate datetime NOT NULL ,
> >>  FieldProperties text NOT NULL ,
> >>  UpdateID int NOT NULL
> >> )

> >> A very simple select was used:

> >> SELECT * FROM QuestFields WHERE Field QuestID = 17

> >> If the value of UpdateID is not 0 then the blob returned is the incorrect
> >> blob. If it is 0 then it returns the correct data.

> >> By changing the order of the fields the problem went away.

> >> It works fine in ISQL, but fails using RDO in VB5 via ODBC unless the
> field
> >> order is adjusted.

> >> Has anyone else had similar problems?

> >> Dion

> >--
> >Cludio Avolio Rodrigues
> >Systems Especialist
> >IBM GS

> >Home page: http://www.geocities.com/SunsetStrip/4994

--
Cludio Avolio Rodrigues
Systems Especialist
IBM GS

Home page: http://www.geocities.com/SunsetStrip/4994
 
 
 

Going Nuts - TEXT/Blobs need to be the last field in a table

Post by Dion Wiggin » Mon, 29 Dec 1997 04:00:00


I'm running SP3, I have not dared to install SP4 yet. Ill wait a little for
feedback from other people installing it.

Dion


Quote:>Dion,

>>Spent 5 hours trying to work out why when I change the value of a totally
>>unrelated column, the results come back different from a SQL Query.
>>I found a nice bug in SQL Server. You *MUST* put your blobs last in your
>>table.

>There was a known bug with SP2 for 6.5and image or text columns;
>putting the column last was at least a partial workaround.  This was
>supposed to be fixed in SP3 and SP4.  What service pack level are you
>at?

>Roy

 
 
 

Going Nuts - TEXT/Blobs need to be the last field in a table

Post by Dion Wiggin » Mon, 29 Dec 1997 04:00:00


Hi Cludio, thanks for the tip. Can you point me in the direction of the
documentation you mentioned. I've looked in the help and in the knowledge
base, but could not find anything.

Cheers

Dion


>I wouldn't say it's a bug because this behavior is documented. You must put
>your long fields on the right of the select statement. Let's say it's a
>undesired behavior...  :)


>> Spent 5 hours trying to work out why when I change the value of a totally
>> unrelated column, the results come back different from a SQL Query.
>> I found a nice bug in SQL Server. You *MUST* put your blobs last in your
>> table.

>> The below structure works correctly:

>> CREATE TABLE dbo.QuestFields (
>>  QuestID int NOT NULL ,
>>  FieldID int NOT NULL ,
>>  EffDate datetime NOT NULL ,
>>  UpdateID int NOT NULL ,
>>  FieldProperties text NOT NULL
>> )

>> The next one does not:

>> CREATE TABLE dbo.QuestFields (
>>  QuestID int NOT NULL ,
>>  FieldID int NOT NULL ,
>>  EffDate datetime NOT NULL ,
>>  FieldProperties text NOT NULL ,
>>  UpdateID int NOT NULL
>> )

>> A very simple select was used:

>> SELECT * FROM QuestFields WHERE Field QuestID = 17

>> If the value of UpdateID is not 0 then the blob returned is the incorrect
>> blob. If it is 0 then it returns the correct data.

>> By changing the order of the fields the problem went away.

>> It works fine in ISQL, but fails using RDO in VB5 via ODBC unless the
field
>> order is adjusted.

>> Has anyone else had similar problems?

>> Dion

>--
>Cludio Avolio Rodrigues
>Systems Especialist
>IBM GS

>Home page: http://www.geocities.com/SunsetStrip/4994

 
 
 

1. I am going *#&^! NUT about JDBC

Yo,

Has anyone successfully executed a prepared statement multiple times
without re-creating it with SUN's JDBC-ODBC bridge and MS SQL Server
6.5?  Specifically, something like the following:

      PreparedStatement pstmt;
      pstmt = con1.prepareStatement("select * from authors\n");

      for(int t = 0; t < 5; t++)
      {
        System.out.println("t: " + t);
        rs = pstmt.executeQuery();
        while(rs.next())
        {
          System.out.println(rs.getString(1) + "  " + rs.getString(2));
        }
        rs.close();
      }

I am using Windows NT 4.0, JDK 1.2beta4, and MS SQL Server 6.5 and I
keep getting this.  I am looping through the crap 5 times, but I fail
on the 2nd round, why, why oh my why????

t: 0
172-32-1176  White
213-46-8915  Green
238-95-7766  Carson
267-41-2394  O'Leary
274-80-9391  Straight
341-22-1782  Smith
409-56-7008  Bennet
427-17-2319  Dull
472-27-2349  Gringlesby
486-29-1786  Locksley
527-72-3246  Greene
648-92-1872  Blotchet-Halls
672-71-3249  Yokomoto
712-45-1867  del Castillo
722-51-5454  DeFrance
724-08-9931  Stringer
724-80-9391  MacFeather
756-30-7391  Karsen
807-91-6654  Panteley
846-92-7186  Hunter
893-72-1158  McBadden
899-46-2035  Ringer
998-72-3567  Ringer
t: 1
java.sql.SQLException: Invalid state for getResultSet
        at
sun.jdbc.odbc.JdbcOdbcStatement.getResultSet(JdbcOdbcStatement.java:2
53)
        at
sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeQuery(JdbcOdbcPrepared
Statement.java:106)
        at JdbcOdbc.main(JdbcOdbc.java:49)

Please note 't: '...a million kudos to whoever that can help me...:-)

2. Stepping Through

3. Full-text searching (Microsoft Search service) goes nuts

4. Oracle DBA, Database Administrator Retrain from AP, Permanent

5. Identify fields values go nuts after reboot

6. DB Connection for Administrative API Functions?

7. TEXT field in Blob Space vs Table Space

8. Northern California Unify Users Group News

9. Fixed field text, and variable length last field

10. going to next record after leaving last field in form

11. Go to First Record and Go to Last Record

12. Transfer a string from BLOB-TEXT-field to NCHAR-field

13. Am I nuts or......