|
1
|
1 NAME
|
|
|
2 DBSQLToTextFiles.pl - Export data from MySQL, Oracle or PostgreSQL
|
|
|
3 database into CSV/TSV text files
|
|
|
4
|
|
|
5 SYNOPSIS
|
|
|
6 DBSQLToTextFiles.pl SQLFileName(s) | SQLSelectStatement(s)...
|
|
|
7
|
|
|
8 DBSQLToTextFiles.pl [-d, --dbdriver mysql | Oracle | Postgres or Pg]
|
|
|
9 [--dbhost hostname] [--dbname databasename] [--dbpassword password]
|
|
|
10 [--dbusername username] [--exportdatalabels yes | no] [--exportlobs yes
|
|
|
11 | no] [-h, --help] [-m, --mode SQLStatement | SQLFile] [-o, --overwrite]
|
|
|
12 [--outdelim comma | tab | semicolon] [-q, --quote yes | no] [-r, --root
|
|
|
13 rootname] [--replacenullstr string] [-w --workingdir dirname]
|
|
|
14 SQLFileName(s) | SQLSelectStatement(s)...
|
|
|
15
|
|
|
16 DESCRIPTION
|
|
|
17 Export data from MySQL, Oracle or PostgreSQL database into CSV/TSV text
|
|
|
18 files. Based on -m --mode option value, two methods of data selection
|
|
|
19 are availble: in line SQL select statement(s), or SQL file name(s)
|
|
|
20 containing SQL select statement(s). All command line parameters must
|
|
|
21 correspond to similar mode; mixing of parameters for different modes is
|
|
|
22 not supported.
|
|
|
23
|
|
|
24 OPTIONS
|
|
|
25 -d, --dbdriver *mysql | Oracle | Postgres or Pg*
|
|
|
26 Database driver name. Possible values: *mysql, Oracle, Postgres or
|
|
|
27 Pg*. Default: *MySQL* or value of environment variable DBI_DRIVER.
|
|
|
28 This script has only been tested with MySQL, Oracle and PostgreSQL
|
|
|
29 drivers.
|
|
|
30
|
|
|
31 --dbhost *hostname*
|
|
|
32 Database host name. Default: *127.0.0.1* for both MySQL, Oracle and
|
|
|
33 PostgreSQL. For remote databases, specify complete remote host
|
|
|
34 domain: *dbhostname.org* or something like it.
|
|
|
35
|
|
|
36 --dbname *databasename*
|
|
|
37 Database name. Default: mysql for MySQL, postgres for PostgreSQL and
|
|
|
38 none for Oracle. For connecting to local/remote Oracle databases,
|
|
|
39 this value can be left undefined assuming --dbhost is correctly
|
|
|
40 specified.
|
|
|
41
|
|
|
42 --dbpassword *password*
|
|
|
43 Database user password. Default: *none* and value of environment
|
|
|
44 variable DBI_PASS is used for connecting to database.
|
|
|
45
|
|
|
46 --dbusername *username*
|
|
|
47 Database user name. Default: *none* and value of environment
|
|
|
48 variable DBI_USER is used for connecting to database.
|
|
|
49
|
|
|
50 --exportdatalabels *yes | no*
|
|
|
51 This option is mode specific and controls exporting of column data
|
|
|
52 labels during exportdata mode. Possible values: *yes or no*.
|
|
|
53 Default: *yes*.
|
|
|
54
|
|
|
55 --exportlobs *yes | no*
|
|
|
56 This option is mode specific and controls exporting of CLOB/BLOB
|
|
|
57 data columns during exportdata mode. Possible values: *yes or no*.
|
|
|
58 Default: *no*.
|
|
|
59
|
|
|
60 -h, --help
|
|
|
61 Print this help message.
|
|
|
62
|
|
|
63 -m, --mode *SQLStatement | SQLFile*
|
|
|
64 Data selection criterion from database. Two different command line
|
|
|
65 parameter methods are available: in line SQL statement(s)
|
|
|
66 specification or file name(s) containing SQL select statement(s).
|
|
|
67 This value determines how command line parameters are processed.
|
|
|
68
|
|
|
69 Possible values: *SQLStatement or SQLFile*. Default value:
|
|
|
70 *SQLStatement*
|
|
|
71
|
|
|
72 In SQLFile mode, SQL file contains select statements delimited by
|
|
|
73 *;*. And the lines starting with *#* or *-* are ignored.
|
|
|
74
|
|
|
75 -o, --overwrite
|
|
|
76 Overwrite existing files.
|
|
|
77
|
|
|
78 --outdelim *comma | tab | semicolon*
|
|
|
79 Output text file delimiter. Possible values: *comma, tab, or
|
|
|
80 semicolon* Default value: *comma*.
|
|
|
81
|
|
|
82 -q, --quote *yes | no*
|
|
|
83 Put quotes around column values in output text file. Possible
|
|
|
84 values: *yes or no*. Default value: *yes*.
|
|
|
85
|
|
|
86 -r, --root *rootname*
|
|
|
87 New file name is generated using the root:<Root><No>.<Ext>. Default
|
|
|
88 new file file names: SQLStatement<No>.<Ext>, or
|
|
|
89 <SQLFileName><StatementNo>.<Ext>. The csv and tsv <Ext> values are
|
|
|
90 used for comma/semicolon, and tab delimited text files
|
|
|
91 respectively.This option is ignored for multiple input parameters.
|
|
|
92
|
|
|
93 --replacenullstr *string*
|
|
|
94 Replace NULL or undefined row values with specified value. Default:
|
|
|
95 *none*
|
|
|
96
|
|
|
97 For importing output text files into MySQL database using "load data
|
|
|
98 local infile '<tablename>.tsv' into table <tablename>" command, use
|
|
|
99 *--raplacenullstr "NULL"* in conjunction with *--exportdatalabels
|
|
|
100 no*, *--quote no*, and *--outdelim tab* options: it'll generate
|
|
|
101 files for direct import into MySQL assuming tables already exists.
|
|
|
102
|
|
|
103 -w --workingdir *dirname*
|
|
|
104 Location of working directory. Default: current directory.
|
|
|
105
|
|
|
106 EXAMPLES
|
|
|
107 To export all data in user_info table from a MySQL server running on a
|
|
|
108 local machine using username/password from DBI_USER and DBI_PASS
|
|
|
109 environmental variables, type:
|
|
|
110
|
|
|
111 % DBSQLToTextFiles.pl -o "select * from user_info"
|
|
|
112
|
|
|
113 To describe user table in a MySQL server running on a remote machine
|
|
|
114 using explicit username/password and capturing the output into a
|
|
|
115 UserTable.csv file, type:
|
|
|
116
|
|
|
117 % DBSQLToTextFiles.pl --dbdriver mysql --dbuser <name> --dbpassword
|
|
|
118 <pasword> --dbname mysql --dbhost <mysqlhostname.org> -r UserTable
|
|
|
119 -m SQLStatement -o "select * from user_info"
|
|
|
120
|
|
|
121 To describe table all_tables in Oracle running on a remote machine using
|
|
|
122 explicit username/password and capturing the output into a AllTable.tsv
|
|
|
123 file, type:
|
|
|
124
|
|
|
125 % DBSQLToTextFiles.pl --dbdriver Oracle --dbuser <name> --dbpassword
|
|
|
126 <pasword> --dbhost <oraclehostname.com> -r AllTable -m SQLStatement
|
|
|
127 --outdelim tab --quote no -o "select * from all_tables"
|
|
|
128
|
|
|
129 To run all SQL statement in a file sample.sql on a local Oracle host and
|
|
|
130 capturing output in a SampleSQL.csv file, type:
|
|
|
131
|
|
|
132 % DBSQLToTextFiles.pl --dbdriver Oracle --dbuser <name> --dbpassword
|
|
|
133 <pasword> -r SampleSQL -m SQLFile -o sample.sql
|
|
|
134
|
|
|
135 AUTHOR
|
|
|
136 Manish Sud <msud@san.rr.com>
|
|
|
137
|
|
|
138 SEE ALSO
|
|
|
139 DBSchemaTablesToTextFiles.pl, DBTablesToTextFiles.pl
|
|
|
140
|
|
|
141 COPYRIGHT
|
|
|
142 Copyright (C) 2015 Manish Sud. All rights reserved.
|
|
|
143
|
|
|
144 This file is part of MayaChemTools.
|
|
|
145
|
|
|
146 MayaChemTools is free software; you can redistribute it and/or modify it
|
|
|
147 under the terms of the GNU Lesser General Public License as published by
|
|
|
148 the Free Software Foundation; either version 3 of the License, or (at
|
|
|
149 your option) any later version.
|
|
|
150
|
