I want to fetch both column name and value from resultset of sql query
I want to fetch both column name and value from resultset of sql query . I am using ExecDirect() method for SQL execution.
please help me out.
Comments
After using your %ExecDirect() method, you should have a %SQL.StatementResult Object. Let's assume you called this object rs (for result set). So you did something like set rs = ##class(%SQL.Statement).%ExecDirect(blahblahblah). From there:
//Get the list of columns
set cols = rs.%GetMetaData().columns
//Loop through the result object
while rs.%Next(){
//Loop through the columns
for x=1:1:rs.%ResultColumnCount{
//Get the value of each column
set colValue = rs.%GetData(x)
//Get the name of each column
set colName = cols.GetAt(x).colName
//TODO: Add whatever you're doing with the name and value here
}
}
Adding to above question along with column name, I want to get type of data value it is storing in DB. Example - column Name - String, Age - Integer etc.
How can I get this as well.
You can use the method GetColumnType, here is the url to the official documentation: https://docs.intersystems.com/iris20231/csp/documatic/%25CSP.Documatic.cls?LIBRARY=%25SYS&CLASSNAME=%25Library.ResultSet#GetColumnType
how it is used?
resultSet.GetCoumnType(i)
Yes, the i is the number of the column.
i am getting error on this <method does not exist> on GetColumnType
forx=1:1:rs.%ResultColumnCount{
//Get the value of each columnset colValue = rs.%GetData(x)
//Get the name of each columnset colName = cols.GetAt(x).colName
set colType = rs.GetColumnType(x)Have you tried this code?
@Luis Angel Pérez Ramos, you're looking at the deprecated %Library.ResultSet class. If this is using IRIS 2023.1, it's probably starting from a %SQL.Statement, and the ExecDirect method is returning a %SQL.StatementResult, which doesn't have that method.
yes i am using iris 2023.1
It's true! Thank you @David Hockenbroch , I didn't realize that it was deprecated.
The columns have a clientType property. Is that what you're looking for? That would be:
set colType = cols.GetAt(x).clientType
That will get you an integer that corresponds to a data type, as documented here. So if that clientType is 1 it's Binary, 2 is a date, 3 is a double, and so on.
What does
| 4 | HANDLE |
this mean?
I believe that's an object handle. You could have a table with a column that contains ObjectScript objects, and in that case the query would return a reference to that object.
So In this case, If i wanna know the value return from this object is of which data type. How can I achieve that?
💡 This question is considered a Key Question. More details here.