topic SAS 9.4 M2, PostgreSQL 12, cannot delete rows in SAS Programming

SAS 9.4 M2, PostgreSQL 12, cannot delete rows

yellowcat — Tue, 17 Aug 2021 00:43:02 GMT

Hello,

I have SAS 9.4 M2 and a database on a server with PostgreSQL 12.

We are using a libname with odbc to connect to the server. I can SELECT but I cannot DELETE datat from a table.

options nofmterr;

libname db_schema odbc user=**** password=**** datasrc=[database alias in odbc.ini] schema=[schema name] direct_exe=delete;

proc sql;
    /* SELECT WORKS */
    select * from db_schema.tbl_blah_error
    where blah_error_comment LIKE "TEST-1234%";

    /* ERROR - CANNOT DELETE */
    delete from db_schema.tbl_blah_error
    where blah_error_id=104680;
quit;

I can select from the table just fine
I cannot delete from the table, I get the following error
I am told that this problem is expected, because
1. we have postgresql 12
2. we need to upgrade sas because "they [SAS] wants us to use the psqlODBC driver that they supply with SAS instead of the one you install on the server [the database server?]"
3. I see the driver file name (from our odbc.ini file) is psqlodbcw.so
I cannot find any documentation that clearly supports #3 above
1. for instance, I can't find a bug or hot fix or whatever that I would normally see for similar problems in the community forums (yay other community users!)
2. we did find this note https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/whatsdiff/p1qjpyniex9epun17ie3st4n3ror.htm where it says "SAS 9.4M7 (August 2020) supports PostgreSQL 12." That implies but does not directly indicate that SAS 9.4 less than M7 does not support PostgreSQL 12.
3. Can anyone point me to documentation listing which version of PostgreSQL SAS 9.4 M2 supports?
If anyone has seen this problem, is there any known workaround?

Re: SAS 9.4 M2, PostgreSQL 12, cannot delete rows

yellowcat — Tue, 17 Aug 2021 00:50:38 GMT

the error I get is
ERROR: Error updating table entry: Only SQL_POSITION/REFRESH is supported for PGAPI_SetPos
ERROR: ROLLBACK issued due to errors for data set HPTN071D.TBL_ASSAY_FILE_UPLOAD_ERROR.DATA.
NOTE: Deletes were rolled back by the DBMS software.

Re: SAS 9.4 M2, PostgreSQL 12, cannot delete rows

ChrisNZ — Tue, 17 Aug 2021 03:21:50 GMT

SAS does not mandate any ODBC driver.

Ask the PostgreSQL admins which driver they recommend for your needs and install it on the SAS server.

Re: SAS 9.4 M2, PostgreSQL 12, cannot delete rows

Ksharp — Tue, 17 Aug 2021 12:56:43 GMT

Also try Pass-Through SQL.

proc sql;
connect to odbc(dsn=xx ........);
execute ( delete from ...........)
by odbc;
quit;

Re: SAS 9.4 M2, PostgreSQL 12, cannot delete rows

yellowcat — Mon, 23 Aug 2021 17:58:52 GMT

Thanks Ksharp! I used a connection and executed the query on the remote server.

However, I had to rewrite some of the queries, because they have correlations to local sas datasets.