BorlandTalk.com

Harald Feigel · Guest

this one drives me crazy. I convert a BDE app to dbExpress. My environment:
XP Professional, Delphi7 Enterprise, Interbase 5.5 (also tested with IB 6.5)

My problem:
- my BDE app inserts a record into an IB-Table "patient" where e.g a column
called "Zipcode" is defined as e.g. Varchar(16). The BDE app has inserted ""
into that field. Means no space and not NULL. My BDE app runs fine in
retrieving this record again.
I do have non-db aware controls on my form for editing or inserting a
record. Means just TEdit's. When i insert a record, i do that via TQuery
where the statement is a simple:
"INSERT INTO PATIENT (ID, Zipcode..) VALUES (:ID, :Zipcode...)"
I do fill that query with:
Query1.ParamByName('zipcode').asString := Zipcode_Edit.Text
Query1.ExecSQL

I retrieve a record with a "SELECT * FROM Patient Where ID = :ID" statement.
Then i fill my TEdit's like:
Zipcode_Edit.Text := Query1.FieldByName('Zipcode').AsString;

This all run's fine in my BDE app. BUT, if i do just the retrieve with a
TSQLQuery under DBExpress i always get one space " " from my
TSQLQuery.FieldByName('Zipcode').AsString, even if my BDE app retrieves it
correctly. I have inserted the "Trim char=TRUE" parameter for my
TSQLConnection. And that seems to create my problem. If not using that
parameter, i get 16 spaces if my field in the DB is a varchar(16). But i
don't want to write extra code for trimming each field.

Even, if i update my record with a TSQLQuery like
"UPDATE Patient SET Zipcode = :ZipCode Where ID = :ID" and having assigned
SQLQuery1.ParamByName('ZipCode').AsString := ZipCode_Edit.Text
(where the field is of course empty), after executing that query and trying
to retrieve it again, i get this stupid space character in the column
"zipcode".

Who can help me out,

Harry

Bill Todd (TeamB) · Guest

You need to install all of the Delphi and dbExpress driver service
packs and updates.

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

Harald Feigel · Guest

Hi Bill,

thanks for that hint. But i did not find any dbExpress updates or service
packs. I only found "Software Updates for Delphi7". From there i downloaded
and installed:
D76_db2update.zip (that does only update the db2 dbexpress driver)
Rave_be_5_0_8.exe (of course, this didn't help in any way)
mssqlupdate.zip (and that only updates the MSSql dbexpress driver)

That's all i could do.

Some additional information:
If there is a NULL value, then everything's fine. In that case, the TEdit
which is filled by a
TEdit.Text := TSQLQuery.FieldByName('ZipCode').AsString
is really empty.

All the records in the database have been inserted by my older BDE
application which behaves correctly.

I wrote a little application that does both receive the values via dbExpress
and via BDE. If fetched through the BDE then the field is empty. If fetchted
by DBExpress the field contains one space character. And it's definitely a
space character, cause i checked that with a Edit2.Text :=
StringReplace(Edit1.Text, ' ', 'X');

Strange is, that the following query, even if different, executed with
Interbase Windows ISQL, does display exactly the same result set.
SELECT * FROM PATIENT WHERE ZIPCODE = ""
SELECT * FROM PATIENT WHERE ZIPCODE = " "
wheres, the second query contains a space-character between the
quotation-marks.

Could that cause the problem? Do i have to convert all my existing customer
databases with e.g.
UPDATE PATIENT SET ZIPCODE = NULL WHERE ZIPCODE = ""

And if i insert some new records with a dbExpress connection? I will check
that out immediately.

Harry

"Bill Todd (TeamB)" <no (AT) no (DOT) com> schrieb im Newsbeitrag
news:nnkd10t05rlf4kcn7qvi5rt2r7ki5d279h (AT) 4ax (DOT) com...

Harald Feigel · Guest

Another hint.
If i use a varchar field in interbase, it's fine. If it's a char-field, it
returns me a single space-character when fetched by
TSQLQuery.FieldByName('...').AsString. The varchar field returns an empty
string which is what i suspected it to do for a char also.

So there might be a parameter for the TSQLConnection which solves that? I
don't want to convert all my database char-fields to varchars. Even if i
would like to, i can't, cause i made extensive use of Domains. And you can't
drop a Domain.

Please remember, that with a BDE application, the char-field AND the
varchar-field are returned as an empty string.

How can i make my dbExpress application work as it behaved with a BDE
connection?

"Bill Todd (TeamB)" <no (AT) no (DOT) com> schrieb im Newsbeitrag
news:nnkd10t05rlf4kcn7qvi5rt2r7ki5d279h (AT) 4ax (DOT) com...

Bill Todd (TeamB) · Guest

A the value from a CHAR field should always be padded to its full
length with trailing spaces. The value from a VARCHAR field should
always have trailing spaces removed.

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)