classmate
asked on
Combination of BDE/Interbase won't accept UPPER in sql statements
Why is this?
This is my query, resulting from a general function :
select *
from customer
where (ID = 1)
and (upper(enabled) = upper ('T'))
This works with BDE/paradox, and with the interbase ISQL, but not with BDE/interbase.
When using Delphi, the error message is :
'Capability not supported'.
Are there some params I can change in my BDE alias, or any other solution to this?
regards,
classmate
This is my query, resulting from a general function :
select *
from customer
where (ID = 1)
and (upper(enabled) = upper ('T'))
This works with BDE/paradox, and with the interbase ISQL, but not with BDE/interbase.
When using Delphi, the error message is :
'Capability not supported'.
Are there some params I can change in my BDE alias, or any other solution to this?
regards,
classmate
ASKER
Originally SQLQueryMode was blank, but i've also tried the 'server' option without any result.
The SQL Passthru Mode was originally Shared Autocommit, but i also have tested Not Shared.
Some other params:
COMMIT RETAIN=FALSE
LANGDRIVER= (blank)
WAIT ON LOCKS=FALSE
ENABLE SCHEMA CACHE=FALSE
It seems like the query actually executes and finishes, before the BDE does a test afterwards that results in an error.
I've tried the query through database desktop and paradox 9, and both places the SQL hourglass is visible for a while, and then an error appears (The message is somewhat different than the one delphi gives).
If i remove upper(), everything goes fine.
The SQL Passthru Mode was originally Shared Autocommit, but i also have tested Not Shared.
Some other params:
COMMIT RETAIN=FALSE
LANGDRIVER= (blank)
WAIT ON LOCKS=FALSE
ENABLE SCHEMA CACHE=FALSE
It seems like the query actually executes and finishes, before the BDE does a test afterwards that results in an error.
I've tried the query through database desktop and paradox 9, and both places the SQL hourglass is visible for a while, and then an error appears (The message is somewhat different than the one delphi gives).
If i remove upper(), everything goes fine.
if there are any NULL-Values in the field enabled?
if so, then try
select *
from customer
where (ID = 1)
and enabled is not null
and (upper(enabled) = upper ('T'))
if this all not works then use
select *
from customer
where (ID = 1)
and enabled is not null
and (enabled = 'T' or enabled = 't')
meikl ;-)
if so, then try
select *
from customer
where (ID = 1)
and enabled is not null
and (upper(enabled) = upper ('T'))
if this all not works then use
select *
from customer
where (ID = 1)
and enabled is not null
and (enabled = 'T' or enabled = 't')
meikl ;-)
ASKER
"is not null" gives a result in database desktop, but the error message is still displayed.
The second solution will not be overall useful because in many cases, the field tested on will contain any number of characters.
(Between the lines: I am migrating a project from paradox to interbase...)
Seems like i need to create a completely unnessecary workaround (as usual :-( )
thanks
classmate
The second solution will not be overall useful because in many cases, the field tested on will contain any number of characters.
(Between the lines: I am migrating a project from paradox to interbase...)
Seems like i need to create a completely unnessecary workaround (as usual :-( )
thanks
classmate
well,
don't know where the problem is,
because it works fine for me (see first comment)
maybe we're working on different versions
i use
interbase 5.5
bde 5.01
delphi 5
meikl ;-)
don't know where the problem is,
because it works fine for me (see first comment)
maybe we're working on different versions
i use
interbase 5.5
bde 5.01
delphi 5
meikl ;-)
Whenever an simple select statement doesn't work, and the BDE is involved think:
"Request Live"
( make sure that property is set to false on the query component. )
In SQL Exploder ( make that Explorer )
uncheck "OPTIONS | QUERIES.... | Request Live Queries"
Generally, this comes when IB is using some "exotic" sql ( like your example ) that prevents it from returning an editable result set. JOINS and STORED PROCEDURE selects are typical culprits.
If you REALLY need to edit the records that this query returns, one thought that comes to mind is a master-detail setup using the master to do the "UPPER" filtering, and the detail on a primary key to allow for editting.
ASKER
Meikl:
You are right, we are working on different versions
vogonPoet:
Actually my TQuery is generated by code and not in the visual design environment. It always corresponds with a TTable which is used for updates, so the requestlive property is not even an issue. However i will check it out in case the default value is not False as i believe it is.
The database desktop as far as i know, never gives live results, but it still gives the error.
In general it seems useful to compare theese issues, but i havent found a solution to my problem.
thanks
classmate
You are right, we are working on different versions
vogonPoet:
Actually my TQuery is generated by code and not in the visual design environment. It always corresponds with a TTable which is used for updates, so the requestlive property is not even an issue. However i will check it out in case the default value is not False as i believe it is.
The database desktop as far as i know, never gives live results, but it still gives the error.
In general it seems useful to compare theese issues, but i havent found a solution to my problem.
thanks
classmate
classmate:
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.
Hi!
No comment has been added lately and this question is therefore classified abandoned.
If asker wishes to close the question, then refer to
https://www.experts-exchange.com/help/closing.jsp
Otherwise, I will leave a recommendation in the Cleanup topic area that this question is:
PAQ'd and pts refunded
Please leave any comments here within the next seven days. It is assumed that any participant not responding to this request is no longer interested in its final disposition.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
...Snehanshu
EE Cleanup Volunteer
No comment has been added lately and this question is therefore classified abandoned.
If asker wishes to close the question, then refer to
https://www.experts-exchange.com/help/closing.jsp
Otherwise, I will leave a recommendation in the Cleanup topic area that this question is:
PAQ'd and pts refunded
Please leave any comments here within the next seven days. It is assumed that any participant not responding to this request is no longer interested in its final disposition.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
...Snehanshu
EE Cleanup Volunteer
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
works for me
how is your alias configured?
(specially the sqlquerymode-property)
meikl ;-)