Written by

Question Gautam Rishi · Sep 8, 2023

How can i get Data type of column from resultset

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

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)0

Add reply

Comments

Luis Angel Pérez Ramos · Sep 8, 2023

Hi @Gautam Rishi ! You can see a similar question here. You can try getting the metadata from the ResultSet or directly getting the ODBCTypes from a query.

0 0

Ashok Kumar T · 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

Gautam Rishi Sep 11, 2023 to Ashok Kumar T

but It gives me same value 10 in almost all cases wether it is DATE or Integer type. How Can I get actual datatype of that value.

0 0

Luis Angel Pérez Ramos Sep 11, 2023 to Gautam Rishi

Adapt and run this query to check what are the ODBC types for your table from the SQL explorer:

SELECT colname,odbctype,isnullable,isreadonly FROM utility.statement_columns('SELECT Name,DOB,Home_City,Home_State from Sample.Person')

0 0

Ashok Kumar T Sep 11, 2023 to Gautam Rishi

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 integerClassMethod 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