Board index » delphi » Strange character in MS SQL 2000 table

Strange character in MS SQL 2000 table


2005-08-09 11:12:20 PM
delphi160
IDE Delph 7
MS SQL 2000
Using
TSQLConnection;
TDataSource;
TDataSetProvider;
TClientDataSet;
TSQLDataSet;
Some of the fields in a table I am working with are of type -VARCHAR-
Length 6
The application was written by a programmer who has emigrated.
I assume that a varchar was used because it appears, you cannot ORDER BY
with a MS SQL type -TEXT- field.
In a loop this field is populated with a mostly six character string
similar to 'AB0000'.
A select query incorporating this field returns the correct rows.
However if the string is only five characters i.e. 'AB00X'
A select query incorporating this field returns no rows.
I observed in SQL Enterprise manager that record with only 5 characters,
the 6th character is padded with a square character (my understanding is
that this is a unprintable character)
If I pad the string been written to the record with a space and query
the table using the padded string the record is returned.
I wonder if any one knows what this character is and if its possible to
stop the character from being added by MSQL
Regards Ian
 
 

Re:Strange character in MS SQL 2000 table

Quote
IDE Delph 7
MS SQL 2000

Using
TSQLConnection;
TDataSource;
TDataSetProvider;
TClientDataSet;
TSQLDataSet;

Some of the fields in a table I am working with are of type -VARCHAR-
Length 6

The application was written by a programmer who has emigrated.

I assume that a varchar was used because it appears, you cannot ORDER BY
with a MS SQL type -TEXT- field.

In a loop this field is populated with a mostly six character string
similar to 'AB0000'.

A select query incorporating this field returns the correct rows.

However if the string is only five characters i.e. 'AB00X'

A select query incorporating this field returns no rows.

I observed in SQL Enterprise manager that record with only 5 characters,
the 6th character is padded with a square character (my understanding is
that this is a unprintable character)

If I pad the string been written to the record with a space and query
the table using the padded string the record is returned.

I wonder if any one knows what this character is and if its possible to
stop the character from being added by MSQL
Instead, I think that not MS SQL is adding a character, but you actually
stored a non printable character.
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
www.upscene.com
Database development questions? Check the forum!
www.databasedevelopmentforum.com
 

Re:Strange character in MS SQL 2000 table

I have never heard of SQL Server padding values in a varchar field. In
fact, it would be a violation of the SQL standard to do so. The only
padding that the standard allows is to pad values in char fields to
their full length with spaces. The characters have to be comming from
user written code. It may be the application or it may be a stored
procedure or trigger but it must be code that is doing the padding.
--
Bill Todd (TeamB)
 

Re:Strange character in MS SQL 2000 table

Thanks to both. I thought so as well, The code is reading from a
decoded .csv file. I used a string trim function to remove spaces.
and displayed the string in a edit box set to terminal font and
could not see anything. Now that I know I will redouble my efforts
today, Check length etc.
Thanks for the help
Regards Ian Coullie
 

Re:Strange character in MS SQL 2000 table

var
CC1: string;
CC1 gets its value from the stringlist DelivStringList.Strings
if I assign this list to a memo the records with a length 5 show a
space after the last character I have used these trim functions
succesfully
//a check before the string trim functions shows the length of the
//string to be 5
ShowMessage(IntToStr(Length(CC1));
CC1 := strTrimChR(DelivStringList.Strings[1],BLANK);
CC1 := strTrimChR(DelivStringList.Strings[1],NULL);
CC1 := strTrimChR(DelivStringList.Strings[1],BACKSPACE);
CC1 := strTrimChR(DelivStringList.Strings[1],TAB);
CC1 := strTrimChR(DelivStringList.Strings[1],LF);
CC1 := strTrimChR(DelivStringList.Strings[1],CR);
CC1 := strTrimChR(DelivStringList.Strings[1],EOF_);
CC1 := strTrimChR(DelivStringList.Strings[1],ESC);
// after t{*word*220} the length is 5
ShowMessage(IntToStr(Length(CC1));
dmWDB.CustDelivCDS.Insert;
dmWDB.CustDelivCDSCust_Code.AsString := CC1;
dmWDB.CustDelivCDS.Post;
If dmWDB.CustDelivCDS.ChangeCount>0 then
begin
dmWDB.CustDelivCDS.ApplyUpdates(0);
end;
After insert MSQL Shows 6 characters the last one being a square
Has any one any ideas
Regards Ian
 

Re:Strange character in MS SQL 2000 table

Sorry forgot to add that there are only the standard stored
procedures, and no triggers in the default installation of
MSQL 2000
Regards Ian
 

Re:Strange character in MS SQL 2000 table

If you run the INSERT from Query Analyzer do you get the same result?
If so, show us the INSERT statement and the metadata for the table.
--
Bill Todd (TeamB)
 

Re:Strange character in MS SQL 2000 table

Hi I havn't used these features yet but I will out of curiosity.
What I did do was find that there is a feature called
SET ANSI_PADDING { ON | OFF }
According to the documentation this is automatically switched on
if an ODBC connection is created. This application was in the process
of being converted from BDE ODBC to DBExpress when I got involved.
and in the ODBC driver this option was switched on it pads the field
with spaces.
I changed the field type to a nVarchar and the problem has gone
The field acts like Interbase varchar with this option.
Thanks for your help.
Regards Ian
Bill Todd writes:
Quote
If you run the INSERT from Query Analyzer do you get the same result?
If so, show us the INSERT statement and the metadata for the table.

 

Re:Strange character in MS SQL 2000 table

This issue with VARCHAR i believe is addressed post Delphi 7.
Also, there was another issue with TEXT, a similar bug in dbExpress VCL
SQLExpr.pas where the Length is passed (data+ null terminator).
You can fix that issue by changing SQLExpr.pas. Please look at call to
SQLCommand.setParameter() and see if the 8th parameter is passed correctly?
T.Ramesh
"Ian Coullie" <XXXX@XXXXX.COM>writes
Quote
IDE Delph 7
MS SQL 2000

Using
TSQLConnection;
TDataSource;
TDataSetProvider;
TClientDataSet;
TSQLDataSet;

Some of the fields in a table I am working with are of type -VARCHAR-
Length 6

The application was written by a programmer who has emigrated.

I assume that a varchar was used because it appears, you cannot ORDER BY
with a MS SQL type -TEXT- field.

In a loop this field is populated with a mostly six character string
similar to 'AB0000'.

A select query incorporating this field returns the correct rows.

However if the string is only five characters i.e. 'AB00X'

A select query incorporating this field returns no rows.

I observed in SQL Enterprise manager that record with only 5 characters,
the 6th character is padded with a square character (my understanding is
that this is a unprintable character)

If I pad the string been written to the record with a space and query
the table using the padded string the record is returned.

I wonder if any one knows what this character is and if its possible to
stop the character from being added by MSQL

Regards Ian