<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
I only use ODBC escape sequences when necessary. Obviously I want to
use standard sql syntax as much as possible. {fn user() } is handy
because it works in all the databases that I work with and there is no
substitute standard sql function for getting current userid that is
cross-dbms.<br>
<br>
I also use {fn now()} which works across most ODBC drivers, but I can't
in this case because I need to use a constant timestamp value so as not
to change distinctness of the subquery that is the source for the
insert.<br>
<br>
The datetime ISO-standard string format I am using works in all the
databases I use 14+ (including PG), except in this case where
'distinct' is used with subquery in PG.<br>
<br>
Also not all PG clients use ODBC, so other PG clients will encounter
this issue using standard ISO datetime string format when not using
ODBC. I don't want to limit users to using ODBC for loading/updating
the database by running sql scripts (which is what the sql for this
issue is used for) so almost all of the database update/load scripts
use generic sql where timestamp/datetime values are are written as ISO
datetime format strings same format as '2010-04-30 00:00:00'<br>
<br>
BTW, I have also tested the sql in question with SQLite, MS Access, MS
Excel and Sybase Adaptive Server 15 and it works with no error, so now
that's 14 different DBMSs that have no issue with the ISO standard
string format and distinct keyword.<br>
<br>
I guess I can find some workaround for this to work with ODBC just for
the specific sql statements causing errors with PG, but that does not
resolve the issue for PG clients not using ODBC.<br>
<br>
Like I said, I am reporting this issue so it can be identified and
hopefully addressed at some point in the future, it is not critical for
me for it to work right now, but that would be nice otherwise user will
see a bunch of one-time errors and lose some ease of use but otherwise
will not be too badly affected.<br>
<br>
Farid<br>
<br>
On 6/4/2010 9:42 PM, Kris Jurka wrote:
<blockquote
cite="mid:alpine(dot)BSO(dot)2(dot)00(dot)1006042140040(dot)6416(at)leary(dot)csoft(dot)net"
type="cite"><br>
<br>
On Fri, 4 Jun 2010, Farid Zidan wrote:
<br>
<br>
<blockquote type="cite">Here is actual statements I am running and
like I said they work for all 9+
<br>
DBMSs (I use ODBC and{fn user()} is the ODBC cross-dbms syntax for the
<br>
current user ID):
<br>
<br>
'2010-04-30 00:00:00',
<br>
'2010-04-30 00:00:00',
<br>
{fn user() }
<br>
<br>
</blockquote>
<br>
If you're into using standard ODBC escapes for portability, shouldn't
you be using {ts '2010-04-30 00:00:00'}?
<br>
<br>
<a class="moz-txt-link-freetext" href="http://msdn.microsoft.com/en-us/library/ms712360%28VS.85%29.aspx">http://msdn.microsoft.com/en-us/library/ms712360%28VS.85%29.aspx</a>
<br>
<br>
Kris Jurka
<br>
<br>
<br>
</blockquote>
<br>
<div class="moz-signature">-- <br>
<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
<title>Signature</title>
<meta name="author" content="Farid Z">
<font size="-1"><a href="http://www.zidsoft.com/">www.zidsoft.com</a>
CompareData: </font><font size="-1">compare
and synchronize SQL DBMS data </font><font size="-1">visually </font><font
size="-1">between two databases
using ODBC drivers</font>
</div>
</body>
</html>