New post
Question
· Sep 8, 2023

How can i get Data type of column from resultset

#Embedded Python #Python #SQL #Caché #InterSystems IRIS

Hi all,
I am trying to execute a query like the below code.
set statement = ##class(%ResultSet).%New("some_class:query_method").   // here query method is empty and with rowspec some columname

statement.Execute(param1)

I want to fetch data type of column value returned from above. eg - Name - VARCHAR, amount - INTEGER etc.
How can I get it. Or if not possible directly. Is there any other way to validate or get datatype of values returned. Line we have type() in python3

Product version: IRIS 2023.1
Discussion (5)2
Log in or sign up to continue
Ashok Kumar · Sep 8, 2023

Hi @Gautam Rishi ,

You can use the  GetColumnType(columnnumber)  to get the datatype of the particular field in the result set object. This GetColumnType Method returns an integer values from 1 to 20.each number represent unique datatype( 10 represents VARCHAR ). If the type cannot be determined, 0 is returned.

For Dynamic SQL use %SQL.Statement It's preferable and suggested instead %Resultset. 

	set result = ##class(%ResultSet).%New("Class:Query")
	while result.Next() {
		for I=1:1:result.GetColumnCount() {
			write "Column Name: ",result.GetColumnName(I),!
			write "Datatype: ",result.GetColumnType(I),!
			
		}
	}
Column Name: FirstName
Datatype: 10
Column Name: LastName
Datatype: 10
0 0
Ashok Kumar · Sep 11, 2023

No. GetColumnType() method returns an integer of the datatype.You can manually get the datatype from the integer.

Class Sample.Person Extends %Persistent
{

Property FirstName As %String;

Property LastName As %String;

Property StartDate As %Library.TimeStamp ;

Property Age As %Integer;

Property TestCurrency As %Currency;

Property TestBoolean As %Boolean;

Property TestCharStream As %Stream.GlobalCharacter;

Query TestQuery() As %SQLQuery [ SqlProc ]
{
    
    select firstname,LastName,Age,TestCurrency,TestBoolean,TestCharStream from Sample.Person
}

ClassMethod GetDataTypeOfField() As %String
{
    set result = ##class(%ResultSet).%New("Sample.Person:TestQuery")
    ;	
    for I=1:1:result.GetColumnCount() {
        write "Column Name: ",result.GetColumnName(I),"  "
        write "Datatype number: ",result.GetColumnType(I),"  "
        write "DataType: ",..GetDataType(result.GetColumnType(I)),!!
    }
}
///Get datatype from the integer
ClassMethod GetDataType(type As %Integer=0) [ CodeMode = expression ]
{
$Case(type,
        1:"BINARY", 2:"DATE", 3:"DOUBLE", 4:"HANDLE",
        5:"INTEGER", 6:"LIST", 	7:"LONGVARCHAR",
        8:"TIME", 9:"TIMESTAMP", 10:"VARCHAR", 11:"STATUS",
        12:"BINARYSTREAM", 13:"CHARACTERSTREAM", 14:"NUMERIC",
        15:"CURRENCY", 16:"BOOLEAN", 17:"OID",
        18:"BIGINT", 19:"FDATE", 20:"FTIMESTAMP",
        :"")
}
}

The output when execute the method GetDataTypeOfField()

IRISMYDEV>d ##class(Sample.Person).GetDataTypeOfField()
Column Name: FirstName  Datatype number: 10  DataType: VARCHAR
 
Column Name: LastName  Datatype number: 10  DataType: VARCHAR
 
Column Name: Age  Datatype number: 5  DataType: INTEGER
 
Column Name: TestCurrency  Datatype number: 15  DataType: CURRENCY
 
Column Name: TestBoolean  Datatype number: 16  DataType: BOOLEAN
 
Column Name: TestCharStream  Datatype number: 13  DataType: CHARACTERSTREAM

refer the documentation here

0 0