Access vs PostGreSQL

"pu" <leejen666 DeleteThis @hotmail.com> wrote in message news:<8nj5c.295$th6.8652@nnrp1.ozemail.com.au>...
> Hi all,
>
> Here is another one.
>
> In MS-Access I have:
> "select * from tbl_Project where datestart>=date() order by datestart"
> Runs ok
> In PostGreSQL, I get this:
> Runtime Error '3146'
> ODBC--call failed.
>
> If I get rid of where clause, all works ok. I had a look at the date_ge
> function, and although works in PostGreSQL, does not work in Access code.
>
> I looked in google, and although found error, none solved the above.
>
> Any pointers please?
>
> Regards
> Leigh
>
> cc to leejen666 DeleteThis @hotmail.com

Judging from this and your other post re: CHR and REPLACE you suffer
from a misplaced notion of what constitutes "standard" SQL. All 3 of
these are _not_.

Especially re: date and string handling by means of SQL _extensions_,
every dbms engine will provide a different set of names for similar
functionality as well as a different set of functionality (or lack
thereof). This especially true of Access.

Last time I looked, PostgreSQL included a pretty complete doc set for
its built-in extensions. You just need to find the appropriate
substitute. If you're using someone else's SQL statements that you're
trying to migrate to PostgreSQL, then you'll need some Access docs as
well if you don't understand the Access functions.

IIRC, there are several Perl scripts available for converting Access
statements to PostgreSQL, but I've never looked at them and may be
wrong.

hth,
prg
email above disabled

<cj.DeleteThis@bird.hoax.qwest.net> wrote in message
news:slrnc5c29u.a8a.cj@bird.hoax.qwest.net...
[deleted]
> >"select * from tbl_Project where datestart>=date() order by datestart"

[deleted]
> but in your ODBC setup on your *nix box. make sure that is configured
properly

[deleted]

Apparently "select * from tbl_Project where date_ge(datestart,now()) order
by datestart;" works.

I would say the PostGreSQL I have doesn't understand the operator >= for
dates.

"P Gentry" <rdgentry1.DeleteThis@cablelynx.com> wrote in message
news:facb01db.0403151246.464eca6f@posting.google.com...
> "pu" <leejen666.DeleteThis@hotmail.com> wrote in message
news:<8nj5c.295$th6.8652@nnrp1.ozemail.com.au>...
[deleted]
> > "select * from tbl_Project where datestart>=date() order by datestart"
[deleted]
> Judging from this and your other post re: CHR and REPLACE you suffer
> from a misplaced notion of what constitutes "standard" SQL. All 3 of
> these are _not_.
You are correct. Except for CHR. There must be someone that has used this
function. If not there must be someone that can point me in the right
direction when it comes to CREATE fUNCTION regarding LANGUAGE "C" where I
can create my own function. Something with an example would even be a
bonus.

[deleted]

Thanks to all anyway.

Leigh

In article <tbD5c.270$Rb7.7777@nnrp1.ozemail.com.au>, pu wrote:
> "P Gentry" <rdgentry1 DeleteThis @cablelynx.com> wrote in message
> news:facb01db.0403151246.464eca6f@posting.google.com...
>> "pu" <leejen666 DeleteThis @hotmail.com> wrote in message
> news:<8nj5c.295$th6.8652@nnrp1.ozemail.com.au>...
> [deleted]
>> > "select * from tbl_Project where datestart>=date() order by datestart"
> [deleted]
>> Judging from this and your other post re: CHR and REPLACE you suffer
>> from a misplaced notion of what constitutes "standard" SQL. All 3 of
>> these are _not_.
> You are correct. Except for CHR. There must be someone that has used this
> function. If not there must be someone that can point me in the right
> direction when it comes to CREATE fUNCTION regarding LANGUAGE "C" where I
> can create my own function. Something with an example would even be a
> bonus.
Excactly this is what I wanted you to post to one of the postgresql
mailinglists. Chances are miles higher they know it, than someone
here at a redhat usenet channel.

"pu" <leejen666 RemoveThis @hotmail.com> wrote in message news:<tbD5c.270$Rb7.7777@nnrp1.ozemail.com.au>...
> "P Gentry" <rdgentry1 RemoveThis @cablelynx.com> wrote in message
> news:facb01db.0403151246.464eca6f@posting.google.com...
> > "pu" <leejen666 RemoveThis @hotmail.com> wrote in message
> news:<8nj5c.295$th6.8652@nnrp1.ozemail.com.au>...
> [deleted]
> > > "select * from tbl_Project where datestart>=date() order by datestart"
> [deleted]
> > Judging from this and your other post re: CHR and REPLACE you suffer
> > from a misplaced notion of what constitutes "standard" SQL. All 3 of
> > these are _not_.
> You are correct. Except for CHR. There must be someone that has used this
> function. If not there must be someone that can point me in the right
> direction when it comes to CREATE fUNCTION regarding LANGUAGE "C" where I
> can create my own function. Something with an example would even be a
> bonus.
>
> [deleted]
>
> Thanks to all anyway.
>
> Leigh

A quick glance here:
http://www.postgresql.org/docs/7.4/interactive/functions-string.html
shows all you need to scan/replace a string/substring.

Re: CHR -- I've not kept up with Access for several years, but if
memory is correct, the Access version of CHR does have some gotchas in
some cases. May be fixed by now as people complained a great deal
that their Access sql would not run even in SQLServer.

If you have much string manipulation to do, I seem to remember some
add-on that provides pretty good regex support. As Nils S. says
though, your best prospect for _correct_ advice is a PostgreSQL
mailing list or one of the NGs in comp.databases.postgresql .

good luck,
prg
email above disabled