I am running the following SQL:



SELECT
 Name + " (Customer/Supplier)" as Name,
 CAST(Email as CHAR(200)) as Email

FROM CustomerSupplier

WHERE Email LIKE "%@%"



UNION
SELECT
 Contact + " (Person)" as Name,
 CAST(Email as CHAR(200)) as Email

FROM Contact

WHERE Email LIKE "%@%"



UNION
SELECT
 "*** GROUP: " + Name as Name,
 CAST(EmailList as CHAR(200)) as Email

FROM StaffContact

WHERE EmpID = 37
AND EmailList LIKE "%@%"

ORDER BY Name

--

It causes my app & even DBSys to fall over totally.

The reason seems to be that "EmailList" (the second field in the 3rd UNION) is a MEMO.

DBISAM is happy with the CAST to CHAR for a MEMO, but not CASTing to CHAR from VARCHAR &
UNIONING this with MEMO.

... Version 3.3

Do I have to upgrade to fix this?


Adam

Adam,

<< It causes my app & even DBSys to fall over totally.

The reason seems to be that "EmailList" (the second field in the 3rd UNION)
is a MEMO.

DBISAM is happy with the CAST to CHAR for a MEMO, but not CASTing to CHAR
from VARCHAR &
UNIONING this with MEMO.

.. Version 3.3

Do I have to upgrade to fix this? >>

I tried the following here with 3.30 and it worked okay:

SELECT "Species No", CAST('' AS CHAR(200))
FROM biolife
UNION
SELECT "Species No", CAST(Notes AS CHAR(200))
FROM biolife
UNION
SELECT "Species No", CAST(Notes AS CHAR(200))
FROM biolife

Does that SQL statement work for you ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Dear Tim,

Thank you, but I no longer have the biolife tables ... perhaps they are somewhere on the
DBISAM site, in which case I could download them.

Following your message I have created a set of sample tables (from the original tables,
but each with just a few rows of data) which I attach as a ZIP.

I found slightly interesting behaviour with these.

I created copies of my original database tables & then used DELETE SQL to remove most of
the records.

If you run the same SQL:

SELECT
Name + " (Customer/Supplier)" as Name,
CAST(Email as CHAR(200)) as Email

FROM CustomerSupplier

WHERE Email LIKE "%@%"



UNION
SELECT
Contact + " (Person)" as Name,
CAST(Email as CHAR(200)) as Email

FROM Contact

WHERE Email LIKE "%@%"



UNION
SELECT
"*** GROUP: " + Name as Name,
CAST(EmailList as CHAR(200)) as Email

FROM StaffContact

WHERE EmpID = 37
AND EmailList LIKE "%@%"

ORDER BY Name


On these files BEFORE Optimizing the tables (i.e. when there are only a few records, but
the table files are still large, containing the deleted records "hidden") I got the same
error as before, i.e. an AV. which crashed DBSys.exe immediately.

Once I had optimized the tables (so the file-size shrinks) I no longer get an AV ... BUT
the SQL doesn't run, in DBSys it starts, gets to 36% complete & freezes.

--

Note that part of my issue with this is that running the above SQL on my SERVER actually
crashed it! ... which amazed me. I would have thought even if the SQL generated an AV it
wouldn't have stopped the server.

Thank you in advance for looking at this ... I hate to drag you away from working on
Version 5 ... as I plan to buy it once it appears (which may well solve these problems
anyway).

Adam

Adam,

<< Thank you, but I no longer have the biolife tables ... perhaps they are
somewhere on the DBISAM site, in which case I could download them. >>

If you have Delphi, then you have the biolife tables.  They ship with the
DBDEMOS alias as part of the BDE installation.

<< Following your message I have created a set of sample tables (from the
original tables, but each with just a few rows of data) which I attach as a
ZIP. >>

No ZIP found.

<< I found slightly interesting behaviour with these.

I created copies of my original database tables & then used DELETE SQL to
remove most of the records.

<< On these files BEFORE Optimizing the tables (i.e. when there are only a
few records, but the table files are still large, containing the deleted
records "hidden") I got the same error as before, i.e. an AV. which crashed
DBSys.exe immediately.

Once I had optimized the tables (so the file-size shrinks) I no longer get
an AV ... BUT the SQL doesn't run, in DBSys it starts, gets to 36% complete
& freezes.>>

That's just random behavior.  There's obviously a pointer/memory issue here
somewhere.

<< Note that part of my issue with this is that running the above SQL on my
SERVER actually crashed it! ... which amazed me. I would have thought even
if the SQL generated an AV it wouldn't have stopped the server. >>

It depends upon the nature of the AV.  Windows isn't supposed to allow this
sort of thing, but it can still happen if the memory overwrite or access is
particularly nasty..

<< Thank you in advance for looking at this ... I hate to drag you away from
working on Version 5 ... as I plan to buy it once it appears (which may well
solve these problems anyway). >>

Version 4.x solves these problems.  And yes, ElevateDB also solves these
problems also.

--
Tim Young
Elevate Software
www.elevatesoft.com

>>No ZIP found.

Dear Tim,

Sorry you can't find the ZIP ... I uploaded it onto the Web Newsgroups & it is there ... I
have just downloaded it myself!

Should I email it to you?

Adam

Adam,

<< Sorry you can't find the ZIP ... I uploaded it onto the Web Newsgroups &
it is there ... I have just downloaded it myself! >>

Damn Outlook Express.   It keeps hiding or missing UUEncoded attachments.
I've got it now, ran it, and yes, you're correct, it blows up.  Here's the
workaround SQL:

SELECT
"*** GROUP: " + Name as Name,
CAST(SUBSTRING(EmailList FROM 1 FOR 60) as CHAR(200)) as Email

INTO MEMORY temp
FROM StaffContact

WHERE EmpID = 37
AND EmailList LIKE "%@%";

SELECT
Name + " (Customer/Supplier)" as Name,
CAST(Email as CHAR(200)) as Email

FROM CustomerSupplier

WHERE Email LIKE "%@%"

UNION
SELECT
Contact + " (Person)" as Name,
CAST(Email as CHAR(200)) as Email

FROM Contact

WHERE Email LIKE "%@%"

UNION
SELECT *
FROM MEMORY Temp

ORDER BY Name;

--
Tim Young
Elevate Software
www.elevatesoft.com

Dear Tim,

Thank you for this I will use it immediately ... I am just out of hospital having had my
appendix removed a few days back!

... I am still curious as to why there is the problem & would it occur with the same data
in V4?

Adam

Adam,

<< Thank you for this I will use it immediately ... I am just out of
hospital having had my appendix removed a few days back! >>

Wow, ouch !   I hope you're doing okay and didn't get into any infection
trouble ?

<<.. I am still curious as to why there is the problem & would it occur with
the same data in V4? >>

It was simply a bug in 3.x.  As for 4.x, it works fine in that version as
expected.

--
Tim Young
Elevate Software
www.elevatesoft.com

Dear Tim,

Thanks for the regards ... luckily I was in the UK at the time & could just walk into our local hospital. I was released 2 days later after 45 minutes of
keyhole surgery. Other friends have had appendixes burst & it is supposed to be dreadful, so I'm really glad I caught it in time.

Adam

adam


Which bit of the UK is that then - Teflon Tony's own constituency just so the figures look good?

Roy Lambert