[libreoffice-users] Re: Connection to SQL database

Le 16/01/2014 14:45, Carl Paulsen a écrit :

Hi Carl,

Let's start at the source...

You have access to a file containing DDL (data definition language) and
DML (data manipulation language). This file has the extension .sql.

Essentially, this file is a kind of dump from the mysql/mariadb
database. The dump includes instructions about the database name or
schema, the tables in the database, and the field types, as well as the
corresponding statements that would allow you to insert that data into a
corresponding mysql database server hosted elsewhere.

First things first :

- can you gain query access to the mysql/mariadb server from which the
sql file originates ? If you can, then you should be able to export your
data directly in CSV format, by querying the database and using the
SELECT...INTO OUTFILE command, or an equivalent GUI function from an
appropriate program (e.g. phpmyadmin, MySQL Query Browser, MySQL
Navigator, Navicat, etc, etc)

- if you do not have query access to the source of the data, then you
are effectively reliant on the SQL file that you have been given. This
means that you have several other ways of dealing with the data
contained therein :

(a) as it is a text file, you could use a script of your own making or
if lucky, trawl for one on the net, to extract the data and output that
to a CSV, Excel or other text-based file type of your choice - various
languages are capable of this, Ruby, Python, Perl, PHP, etc, or you
could probably even use bash/sed/awk ;

(b) host the data on a locally accessible mysql server - to do that you
would need to install mysql server and client programs. If console
commands are not your thing, then you could use LO Base to connect, via
one of the connector methods (extension, jdbc or odbc), to the mysql
hosted database.

The advantage of (b) is that you can do most of the work via the LO Base
UI, once the connection to the mysql database has been set up and the
data imported.

Another advantage of solution (b) is that you can tailor your output via
the GUI tools of LO Base, so that it meets the requirements of your
Salesforce input.

The advantage of (a) is that it operates directly on the content of your
SQL file without having to go through the rigmarole of setting up a
mysql server, but at the expense of having to learn how to manipulate
text data. Note that solution (a) works well for data that is just text
or numbers, but not so well for binary encoded data (although I imagine
that solutions to handle this are also available). This means that you
need to know what kind of data you are going to have to manage in that
SQL file before you start trying to extract it.


> Salesforce.  I thought I might be able to open or link to the data file
> I have through Base and export it.  That's because I haven't really

No, that is not directly possible, at least not in the format in which
you have been given the data.


> Honestly, though, I don't understand what MySQL does.  Does it "open"
> the data file, interpret the commands in it, and then allow the user to
> manipulate and/or display the data correctly (with the correct
> relational links)?  And is that the only - or best - way to access the

MySQl contains both a server and client programs. To simplify, the
server hosts the data and serves it up against requests from the client
program. Interfaces have been developed by third parties to enable the
client program functions to be mapped to UIs, whether it be LO Base,
MySQL Browser, MySQL Workbench, or any other number of GUI tools.

The SQL file you have can be imported directly into a mysql server with
the command I indicated. The server will interpret the statements in the
SQL file and create a "clone" of the database schema, the tables, field
definitions, and the insert the data into that database. You can then
access the imported data and manipulate it as you would with a mysql
hosted database that you had created yourself ab initio.


> Can I just use MySQL to generate CSV files (or some
> other format that Calc can open) for each of the tables in the file?  At

Yes, mysql has a SELECT...INTO OUTFILE query command that lets you do
this (at least for CSV/TSV format) - it also allows you to specify
certain, limited export options. This is done directly from the mysql
command line console.

However, there are GUI tools that make life much easier for you in this
reqard :

MySQL Workbench - possible outputs are : CSV, HTML, XML

MySQL Browser (deprecated, but still functional) - CSV, HTML, XML, Excel
(xslx)

PHPMyAdmin - exports directly to CSV, other text formats, even Calc !!


Importing data into mysql from a sql file :
http://www.itworld.com/it-management/359857/3-ways-import-and-export-mysql-database


Alex




-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted