Import CVS file to PostgresDB

Certified Lead Developer

Hi,

I'm trying to import a CSV file into a PostgreSQL DB.

Customer requires all data from appian must rely on that DB which is connected as datasource.

I'm using the plug in Excel tool and the Smart Service Import CSV to Database. 

I got the following error from stdOut log:

 

ERROR ImportCsvToDatabase8 Error executing SQL
 org.postgresql.util.PSQLException: ERROR: syntax error at or near ","
  Position: 17

From database, i turned statements log on and i found out the following statement: 

2024-03-29 17:32:08 UTC:10.245.48.249(51148):appian@postgres:[3319]:ERROR: syntax error at or near "," at character 17
2024-03-29 17:32:08 UTC:10.245.48.249(51148):appian@postgres:[3319]:STATEMENT: select `field1`, `field2` from `table` where 1=0
2024-03-29 17:32:08 UTC:10.245.48.249(51148):appian@postgres:[3319]:LOG: execute S_1: ROLLBACK

I'm running the same statement on my client in Postgres and i got the same error of course.

What I found out is that backtick is causing problems as it as a different use on o Postgres than MariaDb.

Do you know any workaround to bypass this problem?

Something to skip/bypass the backtick and use something different?

Thanks a lot

Alessandro

  Discussion posts and replies are publicly visible

Parents Reply Children
  • 0
    Certified Lead Developer
    in reply to Abhishek Karumuru

    Yeah, that's the issue with plugins. They only gain new features if enough people ask for it. Or you pick up the code and add that feature yourself.

  • 0
    Certified Associate Developer
    in reply to Stefan Helzle

    Just to add my vote: we have tried to use this smart service to import CSV data to an AuroraDB with Postgres engine, and we receive almost the same error; e.g.:

    ERROR ImportCsvToDatabase8 Error executing SQL org.postgresql.util.PSQLException: ERROR: syntax error at or near "`"  Position: 15  at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
      at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)  at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
      at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)  at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
      at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)  at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134)
      at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
      at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
      at com.appiancorp.ps.exceltools.util.DatabaseUtils.getColumnNames(DatabaseUtils.java:115)

    We can't see the SQL that the service is sending to the DB, but the "getColumnNames" method name suggests that the service is trying to get normalized column names by issuing the same SELECT reported in this post, and adding backticks around the column names.

    If we don't find some workaround we won't be able to use the Smart Service, and we will have to manually implement reading the file... it is a pity; for if the Service would just have the option to not quote the columns, it would work with more databases.

    Thanks and best regards

  • 0
    Certified Associate Developer
    in reply to javicamara

    A last note about this: it has turned out that in plugin version 2.6.3, released on April 27 2024, support for Postgres was added. And it works, or at least it does in our case.