PostgreSQL and serial part 2

[pakis]

Hello,

I have folloving problem, when Zeos try to insert serial column into table
it use SELECT nextval(id_seq) FROM TABLE but in postgres it should
be just SELECT nextval(id_seq). First statement doesn't return any value if
Table is empty. If TABLE have n records then it n times increase id_seq value
so nextval is n + n + 1. I made some ugly hack to Zeos source to temporary
solve the problem. Is there any solution to this problem. How shoud I use
serial with PostgreSQL (postgres 8.1.4 + Delphi 7).

Thanks

[mdaems]

Hi Pakis,

Can you describe the ugly(?) hack you did? It might be a good one. It declares the problem you wrote about before.
Even better : attach a SVN patch file to this thread or post a comment with the changed code (format using Code tags).

Mark

[pakis]

Hello,

here is the ugly hack I made in Zeos 6.5.1 -alpha source in
ZDbcGenericResolver.pas file.

Pavel



function TZGenericCachedResolver.FormCalculateStatement(
Columns: TObjectList): string;
var
I: Integer;
Current: TZResolverParameter;
begin
Result := '';
if Columns.Count = 0 then Exit;

for I := 0 to Columns.Count - 1 do
begin
Current := TZResolverParameter(Columns);
if Result <> '' then
Result := Result + ',';
if Current.DefaultValue <> '' then
Result := Result + Current.DefaultValue
else Result := Result + 'NULL';
end;

// ugly hack for PostgreSQL serials
if (DatabaseMetaData.GetDatabaseProductName = 'PostgreSQL') then
if ( Copy(Result,1,8 ) = 'nextval(') then
Result := 'SELECT ' + Result + ' '
else
Result := 'SELECT ' + Result + ' FROM ' + DefineTableName;
// end of ugly hack

end;

[mdaems]

Hi Pakis,

That hack is very Ugly indeed.

I have two proposals for you:

- Just override FormCalculateStatement in TZPostgreSQLCachedResolver (ZPostGreSqlStatement.pas) with the version for PostGreSql and restore the old ZDbcGenericResolver.pas file. It's still a hack, but at least it only affects PostgreSql

- Look in ZDbcMysqlResultset.pas and extend the TZPostgreSQLCachedResolver (ZPostGreSqlStatement.pas) the way it's done there for Mysql. This is more work and needs some more Zeos internals study but I think it's the best way.

Unfortunately I'm a mysql only user, so can you please choose one of the options and implement/test it? I'll commit your patch to SVN ASAP.

Mark

[everdaniel]

hi everyone:

is there a official patch for this? I have the same issue

[pakis]

Hi,

there is no patch, I can't access cvs at sourceforge, even I
try everything as described in http://zeos.firmos.at/viewtopic.php?t=27
but something is not vorking. Plus hack I make was in opfficial 6.5.1 alpha
source. So for now you have to apply patch to your own versionjavascript:emoticon(':(')
Sad

[barko]

You need SVN client for this kind of actions... look here for more infos: Zeos tutorial.

[mdaems]

Pakis,

If you get the SVN stuff installed : please check out the testing branch version!!! Current trunk is really outdated.

But you don't have to do all that work if you use one of the downloads I provided recently in the Downloads Forum. From there on and with the files you modified I can do the integration as well. So send the full changed files then.
(Don't use the 'Downloads' top link, somebody should remove it or give me a way to publish there. Just as somebody should shut down the sourceforge cvs directory and create the right links there. )

I don't want to integrate the hack you mentioned above. Please do and try one of the things I proposed above. I would do it myself if I had the infrastructure to test it, honestly...

Mark

[pakis]

Hello,
got SVN, but very busy next two weeks, so I will try to apply patch later.