Solved

'Unknown datatype: JSON.' in ADO.NET Provider for Presto

  • 22 November 2023
  • 1 reply
  • 30 views

Userlevel 3
Badge

I’m getting the following exception when trying to run the following code.  Does the driver support the Trino JSON type?  I don’t see json referenced in GetResultTypeData method source.

string sql = "SELECT _id, CAST(extra_columns AS JSON) as extra_columns FROM hive.default.measures";

PrestoCommand cmd = new PrestoCommand(sql, connection);

PrestoDataReader rdr = cmd.ExecuteReader();

 

  Message: 
Test method Juniper.Test.Trino.TrinoClientTests.PrestoAdoRead threw exception:
System.Data.CData.Presto.PrestoException: [500] Could not execute the specified command: Unknown datatype: JSON. ---> oro230j.TC: Unknown datatype: JSON.

Stack Trace: 
ZN.GetResultDataType(FormulaEvaluationContext )
RqC.K(SqlFormulaColumn )
RqC.mI(SqlTable , SqlColumn )
Rod.C(SqlCollection`1 )
Rod.Nd(LLB`2 )
Roh.Nd(LLB`2 )
KJ.U(SQLQuery , Int32 , RRq )
KJ.d(IDataStatement , SQLQuery , QueryParameterCollection , Int32 , String , LeW )
RUJ.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )
RUG.ExecuteQuery(IDataStatement , QueryParameterCollection , Int32 )
<9 more frames...>
PrestoCommand.ExecuteDataReader(CommandBehavior behavior)
PrestoCommand.ExecuteDbDataReader(CommandBehavior behavior)
DbCommand.ExecuteReader()
PrestoCommand.ExecuteReader()
<PrestoAdoRead>d__5.MoveNext() line 106
--- End of stack trace from previous location where exception was thrown ---
ExceptionDispatchInfo.Throw()
TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
TaskAwaiter.GetResult()
ThreadOperations.ExecuteWithAbortSafety(Action action)

 

icon

Best answer by Ethem Q 22 November 2023, 21:14

View original

1 reply

Userlevel 5
Badge

Our SQL engine does not support casting to a JSON type at this time. However, this looks like a syntax that Presto does support so to work around the issue here you might want to use NativeQuery.

SELECT * FROM NativeQuery(‘SELECT id, CAST(extra_columns AS JSON) FROM [TableName]’)

 

You can read more about it here: https://community.cdata.com/editions-90/using-nativequery-568?tid=568&fid=90 

 

In case you have any other questions or issues feel free to send those over at support@cdata.com or you can always submit a support case here.

Reply