Neo4j - Exporting data to SQL/MS Access via apoc.load.jdbcUpdate

[Jason_L]

First time poster after searching through the community.

I am struggling to dynamically build a CREATE or ALTER statement from a list of keys(n) results to be employed using the apoc.load.jdbcUpdate apoc function. I have this function working very well for CREATE and INSERT statements for simple CREATE and INSERT statements.

The challenge I have is the need to export numerous lists of keys(n) in order to build a table in SQL/MS Access. Is there a way to "loop" through this list or collection to dynamically build this statement? This would be followed by a similar dynamically built INSERT statement to insert the records themselves which I believe I can get working once I iron out logic for this CREATE or ALTER statement.

My actual data consists of several neo4j queries to get keys(n) of various nodes and combines them into a long list. I'll likely have 50-200 columns for each export and every column will be unique or different with only about 10 columns common to every export. At present, I can make every column TEXT 12 but next steps will be getting the associated Type(n) for each Keys(n).

Another option I tried was trying to ALTER an existing "template" table however the same issue with "looping through columns persists.

Many thanks in advance!

//Sample list  - actual data includes ~50-254 columns of data to be exported to one table in an access or sql db
:param exportUrl => 'jdbc:ucanaccess:///artifacts/export/sample.accdb'
WITH ['colA', 'colB', 'colC', 'colD', 'colE', 'colF', 'colG', 'colH', 'colI', 'colJ'] AS listCOLS
UNWIND listCOLS as columnNAMES
WITH columnNAMES,
 'CREATE Table TBL_ATTR ([colA] TEXT(12), [colB] Double, [colC] TEXT(36), [colD] TEXT(38), [colE] Integer, [colF] TEXT(24), ' +
     '[colG] TEXT(20), [colH] TEXT(2), [colI] TEXT(4), [colJ] TEXT(50))' as statement
CALL apoc.load.jdbcUpdate($exportUrl, statement) YIELD row
return COUNT(row);        

BONUS - While the above apoc function appears to write the table to the sample.accdb succesfully, the UI throws the following exception. Similarly, I get the same when using an ALTER function. Any thoughts?

EDIT - note this error exists on the first instance of the CREATE (i.e. even when TBL_ATTR does not exist.

Neo.ClientError.Procedure.ProcedureCallFailed
Failed to invoke procedure `apoc.load.jdbcUpdate`: Caused by: org.hsqldb.HsqlException: object name already exists: TBL_ATTR

[Jason_L · Accepted solution]

Looks like I have something that works now using a CREATE function then adding an ALTER TABLE statement for each element in the rows (after UNWIND.)

Effectively I unwind the list into a table format then the apoc function runs table with some default column and then run ALTER table commands to edit for each column. It does seem redundant to run each line to the apoc.load.jdbcUpdate() function it allows me to have some control and doesn't really impact speed. (creates ~100 columns in 54ms for a larger data set)

WITH ['colA', 'colB', 'colC', 'colD', 'colE', 'colF', 'colG', 'colH', 'colI', 'colJ'] AS listCOLS
UNWIND listCOLS as columnNAMES
WITH columnNAMES,
 'CREATE Table TBL_ATTR ([DEFAULT] TEXT(12))' as statement
CALL apoc.load.jdbcUpdate($exportUrl, statement) YIELD row
WITH columnNAMES,
CASE columnNAMES  //modify data types for various data
     WHEN "colB" THEN 'ALTER TABLE TBL_ATTR ADD COLUMN ' + columnNAMES +  ' DOUBLE'
     WHEN "colC" THEN 'ALTER TABLE TBL_ATTR ADD COLUMN ' + columnNAMES +  ' INTEGER'
     WHEN "colH" THEN 'ALTER TABLE TBL_ATTR ADD COLUMN ' + columnNAMES +  ' DATE'
     WHEN "colJ" THEN 'ALTER TABLE TBL_ATTR ADD COLUMN ' + columnNAMES +  ' TEXT(12)'
ElSE 'ALTER TABLE TBL_ATTR ADD COLUMN ' + columnNAMES + ' TEXT(25)'
END as statement
WITH columnNAMES, statement
CALL apoc.load.jdbcUpdate($exportUrl, statement) YIELD row
return count(row);

While the function executes successfully and I can see the columns correctly reflected in the export file, I do receive an error so not sure what is throwing the exception.

Failed to invoke procedure `apoc.load.jdbcUpdate`: Caused by: org.hsqldb.HsqlException