Topic
  • 2 replies
  • Latest Post - ‏2012-10-03T12:03:17Z by pete_s
eteP
eteP
1 Post

Pinned topic Export SQL query to CSV File

‏2012-09-24T14:12:48Z |
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.
Thanks.
Updated on 2012-10-03T12:03:17Z at 2012-10-03T12:03:17Z by pete_s
  • rmantyla
    rmantyla
    4 Posts

    Re: Export SQL query to CSV File

    ‏2012-10-03T08:04:05Z  
    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.

    Regards,
    Risto
  • pete_s
    pete_s
    2 Posts

    Re: Export SQL query to CSV File

    ‏2012-10-03T12:03:17Z  
    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>

    or

    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;
    COMMIT WORK;

    Then you can export view foo contents as if it were a regular table:

    solexp "tcp server 1964" <username> <password> foo