How to save my data from resltset into file

Question

Question

prashanth ponugoti · Feb 7, 2022

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.

Product version: Caché 2017.1

Discussion (5)1

Log in or sign up to continue

Eduard Lebedyuk Feb 7, 2022 to prashanth ponugoti

File is a first parameter in the ToCSV example.

0 0

score 0 · Answer 1 · 2022-02-07T04:20:00-05:00

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

score 0 · Answer 2 · 2022-02-07T05:37:32-05:00

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

score 0 · Answer 3 · 2022-02-09T08:40:00-05:00

prashanth ponugoti Feb 9, 2022 to Eduard Lebedyuk

Thanks a lot Eduard , it works for me.

0 0

score 1 · Answer 4 · 2022-02-14T03:26:00-05:00

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