I created a graph which reads input from a tab in an .xlsx file, performs a few transformation actions and then tries to use an ODBC connection to
1) create a table in an Oracle 21c database from the columns in the preceding TA and
2) load into that table the data from the peceding TA. I use the RunToFinishLine TA to make sure the table is created before the data is loaded ;-)
First issue I encountered is that the SQL generated by the CreateTable TA is invalid for Oracle 21c. The generated SQL was:
CREATE TABLE impEventVolunteer (
EventVolunteerName varchar(50),
TempEventKey varchar(400),
EvtVolRecid NOT NULL AUTO_INCREMENT
)
but the AUTO_INCREMENT option seems never to have been supported in Oracle and the datatype VARCHAR is also not supported so the operation fails at runtime. (Unhelpfully the error message reports a 'missing right parenthesis' error which is so far from the real problem, but I fully understand that Anatella is only reporting what it receives back from ODBC/<target database>). Not a problem - the comprehensive User Guide clearly says the generated SQL may not work for all DBs and so I was able to manually enter the following statement for Oracle 21c SQL to override the auto-generated SQL statement:
CREATE TABLE impEventVolunteer (
EventVolunteerName varchar2(50),
TempEventKey varchar2(400),
EvtVolRecid number(10) GENERATED BY DEFAULT AS IDENTITY,
PRIMARY KEY(EvtVolRecID)
)
and this successfully created the table. But now when I try to run an ODBCupsert action to load the table it fails with the error message:
ERROR upsertODBC: cannot find column '' to test
(I wanted to attach/embed in this message screenshots of the standard and advanced parameters in my ODBCupsert step but I can't find a way to do this).
I wondered if there might be some hidden control characters in the data causing some kind of parsing error so I tested the data was "good" by exporting it from the graph to a CSV file and using a different tool (DBeaver) to load it into the target table. All records were successfully loaded. So I know that I at least have a workaround ending the graph by creating a CSV file to import with DBeaver but obviously it would be much nicer if the ODBCupsert action in Anatella would work with Oracle 21c.
Is it me screwing up or have I run into a compatibility issue?
1) create a table in an Oracle 21c database from the columns in the preceding TA and
2) load into that table the data from the peceding TA. I use the RunToFinishLine TA to make sure the table is created before the data is loaded ;-)
First issue I encountered is that the SQL generated by the CreateTable TA is invalid for Oracle 21c. The generated SQL was:
CREATE TABLE impEventVolunteer (
EventVolunteerName varchar(50),
TempEventKey varchar(400),
EvtVolRecid NOT NULL AUTO_INCREMENT
)
but the AUTO_INCREMENT option seems never to have been supported in Oracle and the datatype VARCHAR is also not supported so the operation fails at runtime. (Unhelpfully the error message reports a 'missing right parenthesis' error which is so far from the real problem, but I fully understand that Anatella is only reporting what it receives back from ODBC/<target database>). Not a problem - the comprehensive User Guide clearly says the generated SQL may not work for all DBs and so I was able to manually enter the following statement for Oracle 21c SQL to override the auto-generated SQL statement:
CREATE TABLE impEventVolunteer (
EventVolunteerName varchar2(50),
TempEventKey varchar2(400),
EvtVolRecid number(10) GENERATED BY DEFAULT AS IDENTITY,
PRIMARY KEY(EvtVolRecID)
)
and this successfully created the table. But now when I try to run an ODBCupsert action to load the table it fails with the error message:
ERROR upsertODBC: cannot find column '' to test
(I wanted to attach/embed in this message screenshots of the standard and advanced parameters in my ODBCupsert step but I can't find a way to do this).
I wondered if there might be some hidden control characters in the data causing some kind of parsing error so I tested the data was "good" by exporting it from the graph to a CSV file and using a different tool (DBeaver) to load it into the target table. All records were successfully loaded. So I know that I at least have a workaround ending the graph by creating a CSV file to import with DBeaver but obviously it would be much nicer if the ODBCupsert action in Anatella would work with Oracle 21c.
Is it me screwing up or have I run into a compatibility issue?
