Base with MySQL via ODBC

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER
Post Reply
decomplexity
Posts: 7
Joined: Sat Jul 24, 2010 6:46 pm

Base with MySQL via ODBC

Post by decomplexity »

When I use the internal HSQL DBMS or connect to an existing database file (e.g. a .xls or an Access .mdb), the database name is specified by implication in the database file name.

However, if I use ODBC to connect to MySQL, is the title of the dialogue box “Connect to existing database” misleading – or is it just misleading me!

Q: Since MySQL can contain more than one database, can Base create a new database in MySQL or only update the structure (e.g. add tables) of an existing database which was created, for example, using one of the usual MySQL utilities


When creating new tables in an existing MySQL database, Base asks for the schema name as well as the table name (I assume that in Base a MySQL schema name is equivalent to ‘database name’). No problem here if I have already created a suitable schema (database field definitions etc) in MySQL. But Base then prefixes all the table names with the schema name, i.e. when I select Tables from the Navigation bar, the schema name rather than the list of tables is displayed. The tables themselves are only displayed when the schema is opened by clicking it.
Q: Is there any way of selecting one database when connecting to MySQL such that Base does not display a schema name (or names) but behaves in the same way as it behaves when connecting to a database file? Note that, as would be expected, selecting a particular database on the MySQL ODBC Connector (Windows version) causes Base to start up using a database with that schema name, but why then does Base need to prefix the table names with the name of the database when it doesn't do so with e.g. a spreadsheet
Max
OpenOffice 3.2 on Windows XP SP3
User avatar
Robert Tucker
Volunteer
Posts: 1250
Joined: Mon Oct 08, 2007 1:34 am
Location: Manchester UK

Re: Base with MySQL via ODBC

Post by Robert Tucker »

decomplexity wrote:Q: Since MySQL can contain more than one database, can Base create a new database in MySQL or only update the structure (e.g. add tables) of an existing database which was created, for example, using one of the usual MySQL utilities
I use JDBC and can go to Tools>SQL... and run CREATE DATABASE new; to create the new database "new" in MySQL. Doubt if that's different for ODBC.

Sorry, don't have much experience with the rest – my current problem (which I think must have been discussed before) is that OpenOffice needs a primary key set before it will edit a MySQL table.
LibreOffice 7.x.x on Arch and Fedora.
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Base with MySQL via ODBC

Post by r4zoli »

Q: Since MySQL can contain more than one database, can Base create a new database in MySQL or only update the structure (e.g. add tables) of an existing database which was created, for example, using one of the usual MySQL utilities
It depending on user rights, how many table you see in table pane, restrict user rights to only one.
If you gives user DBA rights than you can create database/schema.
Q: Is there any way of selecting one database when connecting to MySQL such that Base does not display a schema name (or names) but behaves in the same way as it behaves when connecting to a database file? Note that, as would be expected, selecting a particular database on the MySQL ODBC Connector (Windows version) causes Base to start up using a database with that schema name, but why then does Base need to prefix the table names with the name of the database when it doesn't do so with e.g. a spreadsheet
No, present MySQL drivers shows database names, you can not remove them.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
Post Reply