SAS 9.4 M2, PostgreSQL 12, cannot delete rows

[yellowcat]

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;

1. I can select from the table just fine
2. I cannot delete from the table, I get the following error
3. 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
4. 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?
5. If anyone has seen this problem, is there any known workaround?

[yellowcat]

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.

[Ksharp · Accepted solution]

Also try Pass-Through SQL.

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

[yellowcat]

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.

[ChrisNZ]

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.