How to save my data from resltset into file
Hi Community,
We need to export last 5 days Ens.MessageHeader data for a specific messages into file. I have written custom code to execute SQL query in object script.
Could somebody help me to iterate resultSet and write the data to text file.
Comments
Check this answer if you need a CSV file.
If you need some custom format:
// prepare statement
set st = ##class(%SQL.Statement).%New()
set st.%SelectMode = 1 // ODBC
set sc = st.%Prepare(query)
quit:$$$ISERR(sc) sc
// execute statement
#dim result As %SQL.StatementResult
set result = st.%Execute()
quit:result.%SQLCODE'=0 $$$ERROR($$$SQLError, result.%SQLCODE, result.%Message)
// iterate metadata (for example if you need a header)
#dim metadata As SQL.StatementMetadata
set metadata = result.%GetMetadata()
set columnCount = metadata.columns.Count()
for i=1:1:columnCount {
#dim column As %SQL.StatementColumn
set column = metadata.columns.GetAt(i)
}
// iterate results
while result.%Next() {
for i=1:1:columnCount {
set value = result.%GetData(i)
}
}Thanks Eduard ,
I can see extraction logic in above code but final step is how to write the data into file.
Yes csv file also fine , I want to store this file in file system like d://sample/history.csv
I did not see file path in the attached code.
Thanks a lot
File is a first parameter in the ToCSV example.
Thanks a lot Eduard , it works for me.
If you need to export the result of SQL to CSV you can use csvgen lib.
install csvgen:
USER>zpm "install csvgen"Perform export:
USER>set query="select * from your.classname"
USER>w ##class(community.csvgen).SQLToCSV(";",1,"/folder/file.csv",query)
1