Hi Nidhikhetrapal,
SAS/ACCESS to Vertica provides a great deal of value - it is not free.
Keep in mind, you must have a PRIMARY KEY defined on a vertica table in order to UPDATE and DELETE using SAS/ACCESS to Vertica. You may want to review the documentation.
SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Fifth Edition
Here is some code...
libname myvert vertica server=myvertica database=test user=myuser password=mypasswd;
libname odbcvert odbc prompt;
data myvert.test (dbtype=(x="integer not null, primary key(x)"));
do x = 1 to 1000;
y=x; z=x; output;
end;
run;
INSERT on a non-empty table test...
63 proc sql;
63 ! /* insert using ACCESS to Vertica */
64 insert into myvert.test values (1112,1112,1112);
NOTE: 1 row was inserted into MYVERT.test.
65 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.09 seconds
cpu time 0.00 seconds
66 proc sql;
66 ! /* insert using ACCESS to ODBC */
67 insert into odbcvert.test values (1113,1113,1113);
ERROR: CLI prepare error: [Vertica][ODBC] (10280) Invalid attribute value.
SQL statement: SELECT * FROM test.
68 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
INSERT on a non-empty table works with ACCESS to Vertica and fails with ACCESS to ODBC.
UPDATE Test...
99 proc sql;
99 ! /* update using ACCESS to Vertica */
100 update myvert.test set y=2111 where x=1112;
NOTE: 1 row was updated in MYVERT.test.
101 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.11 seconds
cpu time 0.04 seconds
102 proc sql;
102! /* update using ACCESS to ODBC */
103 update odbcvert.test set y=9999 where x=1;
ERROR: CLI prepare error: [Vertica][ODBC] (10280) Invalid attribute value.
SQL statement: SELECT * FROM test.
104 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds
UPDATE works with ACCESS to Vertica and fails with ACCESS to ODBC.
DELETE Test...
105 proc sql;
105! /* update using ACCESS to Vertica */
106 delete from myvert.test where x=1112;
NOTE: 1 row was deleted from MYVERT.test.
107 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.11 seconds
cpu time 0.01 seconds
108 proc sql;
108! /* update using ACCESS to ODBC */
109 delete from odbcvert.test where x=1;
ERROR: CLI prepare error: [Vertica][ODBC] (10280) Invalid attribute value.
SQL statement: SELECT * FROM test.
110 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
DELETE works with ACCESS to Vertica and fails with ACCESS to ODBC.
Best wishes,
Jeff
