How to enable Autocommit=true in Spotfire Custom Data Source JDBC template

[Gopinath Loganathan]

How to enable Autocommit=true in Spotfire Custom Data Source JDBC template. 3rd party Query tool like DBVisualizer has this as an Option in Connectionto Check & uncheck. Need help to enable the similar property in Spotfire 10.10.3. Need this option to connect Presto Data Source from Spotfire otherwise query fails with error "Session properties cannot be overridden once a transaction is active".

[Seema Krishnani]

Add the below tag to the jdbc template, save and restart the Spotfire server service and test=====

<autocommit>true</autocommit>=====e.g. <connection-url-pattern>jdbc:trino://<host>:port>/<catalog>/<schema></connection-url-pattern>

<supports-catalogs>true</supports-catalogs>

<supports-schemas>true</supports-schemas>

<date-format-expression>$$value$$</date-format-expression>

<date-literal-format-expression>date '$$value$$'</date-literal-format-expression>

<time-format-expression>$$value$$</time-format-expression>

<time-literal-format-expression>time '$$value$$'</time-literal-format-expression>

<date-time-format-expression>$$value$$</date-time-format-expression>

<date-time-literal-format-expression>timestamp '$$value$$'</date-time-literal-format-expression>

<autocommit>true</autocommit>

[Gopinath Loganathan]

Thissolution in jdbc template worked fine, error was resolved and able to create report. Thank you!!

[Rajesh Kumar 10]

Hi Gopinath,

I am also trying to connect Trino using JDBC but it throws error, can you please help me. Here is data source template and the error. It is always pointing to default schema.

Data Source Template:

<jdbc-type-settings>

 <type-name>Trino</type-name>

 <driver>io.trino.jdbc.TrinoDriver</driver>

 <connection-url-pattern>jdbc:trino://<host>:port>/<catalog>/<schema></connection-url-pattern>

 <connection-properties>

  <connection-property>

   <key>SSL</key>

   <value>True</value>

  </connection-property>

 </connection-properties>

 <supports-catalogs>true</supports-catalogs>

 <supports-schemas>true</supports-schemas>

 <autocommit>true</autocommit>

 <date-format-expression>$$value$$</date-format-expression>

 <date-literal-format-expression>date '$$value$$'</date-literal-format-expression>

 <time-format-expression>$$value$$</time-format-expression>

 <time-literal-format-expression>time '$$value$$'</time-literal-format-expression>

 <date-time-format-expression>$$value$$</date-time-format-expression>

 <date-time-literal-format-expression>timestamp '$$value$$'</date-time-literal-format-expression>

</jdbc-type-settings>

and the error is

Error message: Could not get contents of 'TrinoT' from the server.

The data source reported a failure.

InformationModelException at Spotfire.Dxp.Data:

Error retrieving metadata: Query failed (#20454519_064767_067228_c6511t): User: arn:aws:sts::623546374200:assumed-role/DMR_EEC2_Dev_CustomRole/i-049e343cdf455bce7 is not authorized to perform: glue:GetTables on resource: arn:aws:glue:us-east-1:6235466565200:database/default with an explicit deny in an identity-based policy (Service: AWSGlue; Status Code: 400; Error Code: AccessDeniedException; Request ID: axrecd342-c61459-54ff-45ff-a70trt5454; Proxy: null) (HRESULT: 80131500)

Stack Trace:

  at Spotfire.Dxp.Data.InformationModel.InternalInformationModelManager.ListDataSourceChildren(Guid dataSourceId, String dataSourceName)

  at Spotfire.Dxp.Forms.Data.InformationDesigner.Cache.CachedDataSource.<GetChildren>d__34.MoveNext()

  at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)

  at Spotfire.Dxp.Forms.Data.InformationDesigner.Cache.CachedItem.EnsureChildrenLoaded()

  at Spotfire.Dxp.Forms.Data.InformationDesigner.VirtualMultiSelectTreeView.TreeItem.Expand()

Stack Trace:

  at Spotfire.Dxp.Services.WcfSoapService`2.InvokeService[T](Func`1 serviceMethod, String customMethodNameForLogging)

  at Spotfire.Dxp.Data.InformationModel.InternalInformationModelManager.ListDataSourceChildren(Guid dataSourceId, String dataSourceName)

Thanks in advcance!