Hi, i am sorry but i am not very used to IBM SolidDB and solsql.exe
I did read some of the pages of the Administrators manual but i was unfortunately unable to find something that helps me with my Problem:
I want to have a way to get the output of an SQL-Select Query that is run against an IBM SolidDB directly written to a CSV. I know that solexp will do for one Table but not for more complex SQL queries over more than one table.
If it helps to know. I am granted writing access to the DB. I try to get along with just readonly actions to the DB.
Hopefully somebody has mercy and if only to point any directions where to look for a solution.
rmantyla 270005PEBG4 Posts
Re: Export SQL query to CSV File2012-10-03T08:04:05ZThis is the accepted answer. This is the accepted answer.Hi,
Using solsql you need to modify your complex SQL query to include the separating commas:
select column1, ',', column2, ',', column3 from table1, table2;
It you use a script file to store the modified query, you can execute it by specifying the script filename in solsql command line. The output of a query can be stored into a file using option -o:
solsql -o my_data_output.txt "tcp localhost 1313" admin iohe4y my_complex_query.sql
Note that output file contains extra information that needs to be removed:
- at the beginning of the file there are rows that show the executed sql query and the names of the result set columns,
- at the end of the file there is a row that shows the number of rows in the result set.
pete_s 270005QS362 Posts
Re: Export SQL query to CSV File2012-10-03T12:03:17ZThis is the accepted answer. This is the accepted answer.There are basically two alternatives:
1. Use solexp tool with given SQL query (either -e "SQL query" or -f sql_query_filename), eg:
solexp -e "select table_name, column_name from sys_tables, sys_columns where sys_tables.id = sys_columns.rel_id" "tcp server 1964" <username> <password> <tablename>
solexp -f query.sql "tcp server 1964" <username> <password> <tablename>
Note: the <tablename> needs to be a valid name for an existing table, even if the query itself would not read anything from that table!
2. Create a view and export its contents:
CREATE VIEW FOO AS select table_name, column_name from sys_tables, sys_columns where sys_tables.id = sys_columns.rel_id;
Then you can export view foo contents as if it were a regular table:
solexp "tcp server 1964" <username> <password> foo