Topspeed ODBC and SQL query 3

[pilotone]

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

 

[JShankar]

The Topspeed ODBC has a problem with dates especially if you have set up the data source to identify date fields. It is better to query using the integer value of the dates and have a function to convert a Clarion Date to an Access date.
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'

 

[pilotone]

Thanks JShankar,
I had a feeling this was the case.
Pilotone

 

[lvdm]

Hi,

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'}

 

[JShankar]

Thanks a lot, lvdm. You have given me the solution to a problem which has limited me from using Topspeed ODBC effectively for a long time. You do deserve a Star.

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?

 

[JShankar]

Got my answer - Found a site which lists all the scalar functions.

http://www.houseoffusion.com/scalar/scalar.htm

 

[lvdm]

No problem.

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

 

[pilotone]

Thanks Guys,
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

 

[jim1234567890]

I have looked at the Scalar Functions for Access.

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.