[ic] TO_DAYS(now())

[Russell Mann]

> Quoting Russell Mann (tech@khouse.org):
> > Hello List,
> >
> > Is the sql command TO_DAYS(now()) supported in Interchange?  I
> have a query
> > designed like this:
> >
> > [query
> > prefix=pre
> > list=1
> > type=list
> > st=db
> > sql=|
> > SELECT * FROM table_name
> > WHERE TO_DAYS(NOW()) - TO_DAYS(date) >= 0
> > ORDER BY date DESC
> > LIMIT 4
> > |
> > ]
> >
> > This is designed to get the most recent four entries from today
> or earlier,
> > based on the "date" field.  This works just fine by running the query
> > straight to mySQL, and it works fine in PHP, but in Interchange
> it produces
> > this error:
>
> Then your table is not a MySQL table. Interchange GDBM tables don't
> support SQL other than as a simple search mechanism.
>
> --
> Mike Heins
> Perusion -- Expert Interchange Consulting    http://www.perusion.com/
> phone +1.513.523.7621      <mike@perusion.com>

It is an mySQL table, defined in dbconf/mysql/table_name.mysql just like any
other mySQL table.  There are no GDBM or txt files in the products directory
with the same name.  I decided to troubleshoot by removing the offending
code and found out that it doesn't like LIMIT commands either.

Query rerouted from table products failed: Bad SQL statement: Parse error
near LIMIT 4 at /usr/lib/interchange/lib/Vend/Scan.pm line 586.
>
> Query was: SELECT * FROM table_name
> ORDER BY date DESC
> LIMIT 4 at /usr/lib/interchange/lib/Vend/Table/DBI.pm line 1601
>
> Query was: SELECT * FROM table_name
> ORDER BY date DESC
> LIMIT 4

I worked around the no LIMIT limitation with this:

[query
prefix=pre
list=1
type=list
st=db
sql=|
SELECT * FROM table_name
ORDER BY date DESC
|
ml=4
]

If I take the same command from above and strip out the offending SQL, it
works, and displays information only found in the mySQL table:

SELECT * FROM members_bible_study_detail
ORDER BY date DESC

Only problem is this isn't what I need :)  Does the st=db have anything to
do with it?  I copied this code from a previously developed IC page that is
working fine, and I don't remember why st=db is in there.  I can't find
anything on the [query] documentation page about it.  Any suggestions?

Thanks,

Russell