I want to Fetch columnType for sql query

Question

Question

Prashant Singhal · Jul 26, 2023

Hii all,
I'm trying to fetch columnType from resultSet of type %Library.ProcedureContext.
Executed the class query by %Execute after %PrepareClassQuery.

Product version: IRIS 2023.1

Discussion (4)2

Log in or sign up to continue

score 0 · Answer 1 · 2023-07-26T07:55:30-04:00

Bob Kuszewski · Jul 26, 2023

1. Get the NextResult() resultset

2. Get the Metadata from the resultset

0 0

score 0 · Answer 2 · 2023-09-08T03:19:43-04:00

Prashant Singhal · Sep 8, 2023

How to get the column type from Metadata ?

0 0

score 0 · Answer 3 · 2023-09-08T04:35:41-04:00

Hi @Prashant Singhal ! Please, read this interesting article to get the data type from a query:

https://community.intersystems.com/post/sql-statement-metadata

score 0 · Answer 4 · 2023-09-08T06:50:00-04:00

Hi Prashant,

You can get the values from %Metadata.columns.GetAt(i).ODBCType the ODBC type is referred the datatype of the column. 12 for VARCHAR refer the ODBC type integer and datatype here

ClassMethod GetColumnType() As %String
{
    set statement = ##class(%SQL.Statement).%New()
    set tSC = statement.%PrepareClassQuery("Sample.Person","TestQuery")
    set result = statement.%Execute()
    #dim meta As %SQL.StatementMetadata
    set meta = result.%GetMetadata()
    write meta.columns.GetAt(1).ODBCType ;fetch first field
}ObjectScript
ObjectScript