Topspeed ODBC and SQL query
Topspeed ODBC and SQL query
(OP)
Hi,
I'm new to Topspeed ODBC and am wirting the query: "select date, register from cshthist where date = 6/19/01" using Access 97. I get the error "invalid operand". I have tried different variations trying to get the date but no go. Date is set up in the ODBC manager to convert it to Access style date. I can select specific other fields but always fails on Date. I've tried "date = "#6/19/01#". "date = convert(datetime,'06/19/2001') and others. Any ideas? Thanks much.
Pilotone
I'm new to Topspeed ODBC and am wirting the query: "select date, register from cshthist where date = 6/19/01" using Access 97. I get the error "invalid operand". I have tried different variations trying to get the date but no go. Date is set up in the ODBC manager to convert it to Access style date. I can select specific other fields but always fails on Date. I've tried "date = "#6/19/01#". "date = convert(datetime,'06/19/2001') and others. Any ideas? Thanks much.
Pilotone
RE: Topspeed ODBC and SQL query
Clarion stores the date as the no of days since Dec 28, 1800 e.g. today's date Jun 26, 2003 is 73959.
P.S.: Although it is not going to work, the proper syntax for the query is to enclose the date in single quotes in the yyyy-mm-dd format e.g. '2003-06-26'
RE: Topspeed ODBC and SQL query
I had a feeling this was the case.
Pilotone
RE: Topspeed ODBC and SQL query
This should do the trick...
Added a bit more just to show whats possible.
Select i.invdate, {fn dayofyear(i.Invdate)} AS B, {fn CurDate()} as Date , OrdH.OrdNo
FROM Invh i,{OJ OrdH LEFT OUTER JOIN Invh ON Invh.OrdNo = OrdH.OrdNo}
Where i.Invdate = {d'2003-06-05'}
RE: Topspeed ODBC and SQL query
I did experiment and found out the {fn dayofyear(date)} returns the no of days from the beginning of the year, {fn dayofmonth(date)} returns the Day part of the date, {fn month(date)} returns the Month part of the date, {fn year(date)} returns the Year part of the date and can be used effectively in queries like select no,date,{fn dayofyear(date)}as dy,{fn dayofmonth(date)}as d,{fn month(date)} as m,{fn year(date)} as y from tranhdr where {fn month(date)} = 2 and {fn year(date)} = 2002.
Are these functions standard ODBC functions? Is there any way to find out the full list of supported functions by the Topspeed ODBC driver?
RE: Topspeed ODBC and SQL query
http://www.houseoffusion.com/scalar/scalar.htm
RE: Topspeed ODBC and SQL query
I saw that this is a general problem with Topspeed/Clarion, they never provide any help or example when it comes to things like this. You pay a lot of money for the TPS ODBC drivers, but with NO HELP... go figure.
I used Clarion for the last 5 years successfully, but with LOTS OF LIMITATIONS like this.. so I have vowed to never use it again.
There are a lot better development tools and platforms available.
Here is another site with a bit more info ODBC.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odappepr.asp
RE: Topspeed ODBC and SQL query
I'm passing this info to my boss in hopes that he doesn't sink a lot of money into topspeed. There is a potential of over 500 sites that he wants to buy driver licenses for.
One more:
I offered to learn and write my own ODBC drivers for our use on our Clarion databases. Any ideas on the reality of such an undertaking?
Later,
Pilotone
RE: Topspeed ODBC and SQL query
Althought it claims the return types should always be SQL_FLOAT it does not seem the case.
I For example I do something like this:
Select 22/3
from mytable
The result is just 3
There the result should be something like 3.14285714
I am just getting the interger value back, not the entire value.
This becomes and issue when I am trying to manipulate some dates.