Connection to SQL database

users
#1

I have a project for which I need to extract data that is in a .sql file (presumably a SQL database). I've done very little with SQL so I need some guidance on doing this. One route would be to open it with Base or Access (I now finally have access to MSAccess on a Windows machine but would prefer Base), but I don't understand the basics of doing this.

So...can someone point me to a tutorial on how to do this, step-by-step and from the very beginning? For instance, do I need to set up a SQL server and if so, how? (I've tried installing XAMP with MySQL on the Windows machine but haven't gotten it to connect to the file yet.) Can I access a .sql file directly or via other methods? I'd like to eventually learn how to use SQL but I'm also on a pretty limited timeframe on accessing this data.

Or does someone want to outline the steps here for me to research on my own?

Thanks very much,
Carl

#2

SQL files are normally SQL scripts which are backups or scripts that create a database on a SQL server. If you need something in that script, you would need to setup a MySQL server, execute the SQL file on that server, and then get the data out that you need. I know that is a mouthful, and the process varies based on your operating system (Linux, Windows, Mac, etc).

After you do the above, you can connect with base, but you could also get your data out using the MySQL Workbench tool or even a command-line if you are using Linux. I can help you if somebody else doesn't help first, but I need to know what OS you are running to be able to help you further.

#3

Carl

First please advise your OS and LO version.

The basic steps to connect to any outside database backend are:

1. Set up the database backend - Note any usernames and passwords used to connect to the database. Often the install wizard will prompt you for both.

2. Make sure it is running - depending on your boot settings you might need to manually start the backend. This may require you to use your command line aka terminal on Linux and Macs, Command on Windows.

3. Make sure the correct connector for your backend is installed for Base. In Linux check your package manager to make sure it is installed. I am not sure what the Windows or Mac installers do.

4. Run the connection wizard in Base. A detail I am not sure about here is whether you need to have any schema/database/tables set up in the backend. I usually use Base for pretty reports for a MySQL/MariaDB backend that has numerous schemas/tables already setup. If you used the default settings when setting up the database backend you mainly have to enter the log in credentials.

Most backends have a default port they listen on (MySQL/MariaDB is 3306) and normally you do not need to change this. Also, you will need to know the path to the database or URL to reach it. If the database backend is on your local computer the path is localhost (127.0.0.1).

#4

Carl ,

Use a mySQL database and connect over the Native Connector

then google for the book "DataBase Programming" writen by Roberto Benitez for Openoffice but also for LibreOffice
a lot of code can been found on the Book-website

Greetz

Fernand

#5

Hi Carl,

An SQL file does not necessarily need to contain "data", it could
contain just a set of instructions for the database engine to execute.

The SQL file is not a database as such, it is a generally text file
containing instructions that a SQL db engine will understand and
optionally data enclosed within INSERT statements.

If your SQL file contains both data and instructions, it would be a good
idea to know how it was produced, i.e. from which db engine the output
came, as SQL dialects between different vendors, db engines etc, can
differ according to their specific implementations of the SQL standards.

The file itself should be loadable into any text editor, providing that
it can handle the size of the file ! That can give you a better idea of
what's inside. For example, it is all very well having just insert
statements with associated data in a SQL file, but if the tables and db
haven't been set up in advance, you won't be able to get very far with it.

Some of those SQL statements might be directly executable from an empty
LO Base file in the Tools > SQL window, e.g. table creation statements,
key definition statements, constraints, etc, but again that would depend
on whether these statements were supported by the version of hsqldb that
comes with a default embedded ODB database file.

Alex

#6

Thanks, Jay, that is VERY helpful and clarifies a lot. I wondered if I needed the server setup but lots of things I read yesterday suggested I did need that. I do understand that things like Base and Access are just GUIs, but I guess I really just don't understand what a database is and, in particular, what SQL is. I know there needs to be raw data and a file/table structure and I assume a way to interact with it, but I'm still missing a lot.

Is there a basic online reference to help me understand what a SQL database is and what MySQL and the rest are relative to that?

Thx,
Carl

#7

Carl

SQL is the query language used by relational databases and there is ANSI/ISO standard specification for the SQL. So, in principle, any relational database should understand vanilla SQL. Generally, this is true. HSQL (Base backend), Jet (Access), Oracle, MySQL/MariaDB, PostgreSQL, and SQL Server are all relational databases and they all use SQL as their query language. The only warning is all the database providers have added extensions to the SQL language. The GUIs used by Base and Access tend to hide the SQL query from the user. Some of the other tools such as PgAdmin (PostgreSQL) or MySQL Workbench generally require you to write the SQL query in an editor. But these tools allow to use a GUI to access most of the database functions.

The database is program for storing and accessing data that has some sort of structure. In a relational database, the data is organized in tables that consist of rows (specific data) and columns (data types). To keep the data manageable, the data is usually broken up across a series of related tables with defined relations between the tables defined (the data in a specific row in Table A is related to the data in specific row in Table B). In relational database each column has specific data type (number, string, date, etc.).

Your raw data, say for a donor, would consist of name, address, contact information, pledge amount, payments, etc. You would probably split the data into two or more tables. One table would have the donor name, address, and contact information. Another table might have a record of contacts including type, date, result. A third table might have a detail record of payments/donations with date and amounts. To relate each table, each table would likely have a primary key assigned for each entry with appropriate foreign keys (keys that refer to an entry in another table) included as well as the data specific to the table. A query would use these keys to get the data and combine it into a result set. The data design is based on the principle of entry data only once into the database (called "normalization"). So you would only have one table with the donor's name entered while the other tables that would refer to the entry would was the entry's primary key to refer to it. Often integers are used as primary keys because they are easy to deal with and increment nicely. The only requirement for a primary key is that is unique to the table.

If you are using MySQL or MariaDB (MariaDB is a MySQL fork) a pretty good book specific to that family is MariaDB: A Crash Course by Ben Forta. It covers basic SQL, basic database design, etc.

Jay

#8

Thanks again, Jay. Because of my long-time familiarity with Filemaker, Access, Salesforce (and Base soon I hope), I'm well aware of the structure of a relational database. And I'm pretty sophisticated user and even developer so long as the backend is hidden from me (LOL).

Now I have this file I need to "open" and examine, and then move into separate tables/csv files to eventually move into Salesforce. The file came from a proprietary online database system (I'm not sure what) as a backup. And you are correct, the structure is almost certainly similar to what you outline (e.g. name/contact table, donations table, perhaps a volunteer actions table, etc. all with various keys to connect).

But...I don't know just what tables are there, what field/columns are there, whether there's any consistency in data entry, and the like. All of this needs to be mapped into appropriate fields in the Salesforce system. I need to "see" the data and edit it (adding fields, splitting data up, etc.) in order to plan and prepare for the move, and then extract it into a csv file (the only file type that Salesforce will accept as a source).

Thanks to Alex's suggestion, I've learned that the file contains table structure info (table name, column names and types, Key field assignments, etc.) with the actual data for each table included (comma separated I think). I suspect the file is sufficient to construct a SQL database that will contain all of the organization's data, either in MySQL or in Base or Access. But I need to figure out how to do that in such a way that I can assess what data is there and export into a csv file.

Thanks again. Any more help is always appreciated.
Carl

#9

Sent to just Alex T by mistake:

#10

Below is an excerpt from the file referring to the donations_money table. Obviously there are some ID fields, donation info fields (Amount, Date, etc.), a primary key field, etc. Immediately after the table, there's the "dump" of all the data in that table. I've only included one or two "records" from the dump, but there are many hundreds or even thousands of records.

There are quite a few tables in the file, many of which we don't need (past logins under the old system, for example).

Any wisdom on how to convert this data into CSV files? I thought I'd need to either "open" it with MySQL or connect to it via Base. If I could convert it to CSV, I can do any cleanup and prep work in Calc or some other tool I'm more familiar with.

Thanks again, everyone!

Excerpt:

-- Table structure for table `donation_money`

#11

Carl,

Depending on the program (this looks like the output from MySQL Workbench) and the tables are MySQL/MariaDB - the MyISAM is specific MySQL/MariaDB database engine. If you have MySQL or MariaDB and MySQL Workbench installed you can import/restore the data.

The second dump has the data after value with each row in parentheses. You could open the file in any text editor. The remove the first part and the use find and replace to remove the parentheses and each '.

The program that generated the data probably allows you to get all the rows and export/save them as a csv file.

Jay

#12

Hi Carl,

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

This is enough to tell us that the data came from a mysql database
originally. MyISAM is the default engine for non-transactional MySQL
databases :

http://en.wikipedia.org/wiki/MyISAM

In the sample table you give, the table/field definitions are particular
to mysql, so if you try to run that sql with another db engine, e.g. in
hsqldb via LO Tools > SQL, it will fail because it will not recognise
the field types you're trying to create (e.g. enum, mediumint.

So, your best bet would be to import that into a mysql server, assuming
you have one to hand and you have some kind of console/terminal access
(localhost / same machine):

mysql < '/path/to/myfiletoimport.sql'

optionally with -p if you require authentication for the user that is
connecting to the mysql server :

mysql -p < '/path/to/myfileimport.sql'

There are many web sites on the internet that are full of information on
how to set up and import data into a mysql server.

Alex

#13

Hi :slight_smile:
Is it likely to be possible to connect Base directly to the original
data? So that instead of getting an export or a dump of the data it
can be read dynamically?

I know it is not what the o.p. is asking for but i often wonder.
Regards from
Tom :slight_smile:

#14

Tom,

The initial format and some of the data types would need to be converted so the syntax matched to the new . This is a well known problem when migrating from one SQL database to another where there are added, non-standard data types.

Jay

#15

Hi :slight_smile:
Thanks :slight_smile: I guess that is part of my question. The original back-end
of the database in this case is MySql/MariaDb. Base can normally use
MySql/MariaDb as it's own back-end so there would seem to be 2
different routes that might be worth considering;

1. Attach Base directly to the existing MySql/MariaDb that is hosted
on some web-site (or at least on an internet-facing server such as a
Cloud). I know the back-end can either be on a local machine or on a
local-area-network but could it work over the internet too?

2. Since the exported data is already laid out for MySql/MariaDb then
just install MySql or MariaDb locally (onto the same desktop machine
that is using Base) or onto a LAN file-share so that all machines can
use Base (or other front-ends) to access the data. This seems to be
the route Alex is suggesting except he goes further and suggests using
a fairly local machine that already has MySql installed and just
adding the exports as a new file on that machine.

Carl, the o.p., seems to be thinking about the 3rd route and walking
headlong into the type of troubles Jay just outlined. As i see it the
problem with the 2nd or 3rd routes is that exporting data gives static
data. As time goes on the original database gets updated with new
data. So maybe at some point a new export might need to be grabbed
and then somehow figure out a way to merge the updated data at this
end with the updated data from over there. New records/rows are
tricky enough but tracking changes in fields/columns in individual
ancient records would be a complete nightmare. If it's a case of a
single snapshot to rescue data from a sinking Cloud then none of that
is a worry and the single export routes are perfect

So it's really route 1 that i'm curious about and really in a yes/no
way rather than in any detail. Carl doesn't seem to be thinking along
those lines so this is a bit of a tangent that will probably crop up
again in a future thread and be more relevant then.

Regards from
Tom :slight_smile:

#16

Hi :slight_smile:
Hmmm, actually i've only just realised that a 2nd grab from the
'proprietary' back-end data-tables might not be such a nightmare.
Just keep a copy of the current export as it is so that the new grab
can be compared against it. Any differences could then be added to
the data-tables held fairly locally. Hopefully it's unlikely that
different updates would happen to a single old field both here and
there!

Anyway, i guess my main question is could Base be used as the
front-end for data tables (the back-end) that is online?
Regards from
Tom :slight_smile:

#17

Tom,

Let me clarify what I need to do. I need the raw data to move to a new relational database. I'm not planning on having them work directly on spreadsheets. The data will go into Salesforce, but I don't know how to get the raw SQL data into Salesforce with the changes I need to make to it before the move without exporting it.

I may have confused the discussion but not explaining my exact needs, but that's because I (wrongly) tend to think I can do almost everything, and only ask for help for specific issues I think I'm facing. There may well be other ways to do this, but I was focused on what I thought was the only way.

So...what I need is to remove the data from the current database (apparently what I have is the best we can get), manipulate it so that certain fields meet the requirements of the new database, then move it into the new database. The only way I am aware of to get the data into the new (Salesforce) database is in CSV files. Field mapping is done via the first "row" of data (I need the field names), and I also need to figure out what fields contain what data for the mapping process.

My goal was to use Base as a tool to extract the data, or perhaps even accomplish the manipulations I need to do in prep for the move to 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 understood how to use SQL. I then realized I'd probably first need to open or host the SQL data through MySQL or another variant of that. Trouble is, I don't know how to do that. Alex gave some specific suggestions which I will try today.

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 data with Base? 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 this point, I'm not yet sure I have MySQL running properly, because it ain't like any packaged software I've used in the past (and it's been a LONG time since I've done and command-line work - like since the mid to late 1980s). Or maybe there's an option I haven't thought of to manipulate and move the data into Salesforce.

At this point I'm all ears. If I need MySQL for this, can someone either point out a forum where I could get help with it or offer to coach me (off list if need be) through the process of opening the data and extracting it or connecting to Base?

OK, thanks, and sorry for the tome.

Carl

#18

Carl,

You can export csv directly from MySql. For example:
http://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format

If you need modified data, it seems to me that the best way to do that is to
1) work out the changes you need to the existing data
2) create a new table or tables in MySql that reflect the changes, selecting data from the originals
  CREATE TABLE....SELECT.....;
3) use UPDATE to perfom any necessary modifications to the original data
4) export the table(s) as csv files.

There is plenty of help out there for MySql users.

Peter West

So his fame spread throughout all Syria...

#19

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

#20

Thanks yet again, Alex. A couple of things. First I don't have access to the server from which the file originated. So I've set up both MySQL/Server and XAMP on my Windows machine hoping to get one to work for me. I've used PHPMyAdmin before so I thought that would be best, but I can't get it to start. Unfortunately, I can't get Workbench to work either, in part b/c I don't understand it. I'm trying to load the database there (actually, working on the "World" sample database first), but can't seem to figure that out. Do I simply "load" the database, import it, reverse engineer it to create a schema, create a new schema or database and import into it, etc. etc.? Create a server connection? But don't I have to create the connection first? When I try that I get a "failed to connect" 10061 error. As you can see, it's a bit opaque to me. BTW, I tried your command at the command prompt but get a response about the command not being interpretable or something like that. But I just read that I maybe need to start the server and get to a mysql prompt???

Basically, I'm at a huge disadvantage b/c I don't understand ANY of the terms or tools. I'm watching a ton of videos and reading a ton of material. But being someone who has done a lot of teaching/tutoring, I have to say no one out there seems to know how to assume a user knows nothing but is still capable of learning. Too much is assumed in all the materials I find. Thankfully, I'm bright enough to plow my way through this and figure it out, but it will take a LONG time. Plus, my "Workbench" looks nothing like what is shown in the materials I've seen.

In short, I'm trying b. because I can't do the programming without learning yet another tool and don't want to continue chasing after possible tools that end up not working. But at this point, I haven't been able to get the SQL server to load/clone the database (see my confusion above).

I appreciate the link you sent re: importing. If I can get PHPMyAdmin to start I think I can do this. In XAMP all I get is a control panel that lets me start Apache and MySQL but there's no PHPMyAdmin option available and no apparent way to launch it from the start button. I'm reinstalling now in case I screwed up the first installation. I hope that works.

Cheers,
Carl