Switching to Postgresql

I want to switch a DA custom server (not relativity) from MS SQL to Postgresql. I’ve transferred the DB to a new server OK, and now need to see what works and what doesn’t. I can connect to it fine in SM using the FireDAC driver (this is Delphi)

The first stumbling block is that it seems to need the column names and table names to be surrounded by " for it to work, as the SM auto-generate does (and presumably automatically if it is autoSQL) but only for this connection. Is there an easy way to ask DA to do this, or do I really have to add extra statements for every table and command in every service (which I hope not as there are a lot…).

As we also have to work with DBISAM I already have multiple statements where needed (mostly where there is a hardcoded lookup for a boolean field which are different between MSSQL/DBISAM) but I really don’t want to have to go through all the statements in the project if I can avoid it.

PostgreSQL driver of FireDAC use “field” but MSSQL uses [field] naming.
as a workaround, you can save your schema as .daSchema, perform replacement in any text/xml editor and load updated schema back.

Not sure I can see how that would work? I need to keep the existing statements as they are for use with DBISAM, I just was hoping DA would have a way for me to add the " where needed.

can you provide an example of your schema?
you can attach it here or send to support@.

for stAutoSQL statements, no changes are required - they will be built automatically.
for stSQL statements: they should be updated manually, if they contain names in MSSQL style, i.e. like [field]

Eugene: Jeremy is talking about custom SQL statements. He wants to share them between connections, even though they differ in syntax.

That said, Jeremy, IIRC MSSQL supports both [] and “” for quoting identifiers. so “” should work on your statements for both that and PostgreSQL?

—marc

Yes, it’s as Marc says. My statements currently don’t have any quoting identifiers as that is quicker when I typed them and they work on both MSSQL and DBISAM. I’ve just tried “” on all three and they work. It’s just that I have hundreds of them (it’s a big project) so it could take some time. The biggest daSchema file is over 4MB and has 118,000 lines in it!

I have found now that I can avoid the quote identifier altogether, as long as the PG database has all tables and columns in lower case, so I think this is probably the way to go, even though I prefer mixed case for readability.