[Oledb-dev] timestamps

[Victor Snezhko]

Hello, Bart,

please keep the list <oledb-devel at pgfoundry.org> CC'ed, this will help
others with similar problems to find a solution easier, in the
archives.

> I can't get it to work.
>
> I do the following:
> 	TADOQuery* lopQuery = new TADOQuery(this);
>
> 	lopQuery->Connection = con1;
>
> 	AnsiString lovSQL = "UPDATE \"DBInfo\" set \"DBDateCreated\" = :DBDATECREATED";
>
> 	lopQuery->SQL->Clear();
> 	lopQuery->SQL->Add(lovSQL);
>
> 	lopQuery->Prepared = true;
>
> 	TParameter* lopParam = lopQuery->Parameters->ParamByName("DBDATECREATED");
> 	lopParam->Value = AnsiString("2007-08-09 23:59:59");
> //	lopParam->DataType = ftTimeStamp; 
>
> 	lopQuery->ExecuteOptions = TExecuteOptions() << eoExecuteNoRecords;
> 	lopQuery->ExecSQL();
>
>
> The error I get is:
> ERROR: column "DBDateCreated" is of type timestamp without time zone but expression is of type text LINE 1: UPDATE "DBInfo" set "DBDateCreated" = $1 ^ HINT: You will need to rewrite or cast the expression.
>
> If I add the line commented (set the DataType to ftTimeStamp) I get the following error:
> Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another
>
> Any idea what I can do ?

Hm, parameters... Should work, at least when TADOTable operations use
them implicitly, timestamps work for me.
I don't have good examples at hand, will try to look at it over the
weekend. In the meantime, you may do the following:
 1) as a temporary workarounds - 
   1a) try TADOTable (as a temporary workaround)
   1b) don't use parameters
 2) a blind suggestion to fix your example (I don't have a compiler
 that you use, so can't compile it):  
   try to set the type to ftDateTime instead of ftTimeStamp -
   ftTimeStamp might correspond to MSSQL's timestamp - that's a
   completely different type.
   You will need to supply a value of TDateTime type, or how it is
   called in C++ Builder. AnsiString("2007-08-09 23:59:59") won't
   work. Try the following: lopParam->Value = Now(); - this works in
   Delphi, don't know about Builder.

> I already tried to rewrite the functions to do the conversion in the pgoledb.dll myself, but I fail to link because of a lack to libpgdll.lib.

I'd recommend Microsoft's compiler for pgoledb.dll. As a bonus, you
will not need to recompile libpq.dll - it's included in binary
distribution of pgoledb.

> I know I have to recompile PostGreSQL for that, but I did not succeed to do that.

You don't. libpq is enough - it uses a tiny fraction of postgresql's
sources.

> Do you have an easy (detailed) description on how to compile that
> libpgdll.dll on a windows xp system?

PostgreSQL's docs have it:
http://www.postgresql.org/docs/8.2/interactive/install-win32.html

if you use free borland C++ compilers, you might need to make one or
more of the following modifications to the postgresql's sources:
1) modify SYSCONFDIR in pg_config_paths.h. empty string ("") worked for
   me.
2) if there are compilation errors in wchar.c - replace "static
inline" with "inline". (This was necessary for 8.1.4, should be fixed
in 8.2).

> Or can you mail me the libpgdll.lib file for a PostGreSQL version
> 8.2.4 ?

Unfortunately, I don't have BCC at hand anymore.

-- 
WBR, Victor V. Snezhko
E-mail: snezhko at indorsoft.ru