JDBC-connector to MySQL database

users
#1

Hello,

I have been using a JDBC-connection to a remote MySQL-Database for years. It has
been working like a charm in general. There is one rather annoying problem, however:

I have a table with the name "inventar" (German for "inventory"). I can access this
table using a command-line client (mysql ... from Linux) as well as using the "native"
MySQL connector. However, when using JDBC <http://www.coderanch.com/forums/f-3/JDBC>, the table content is NOT displayed at all.
All the other (some 80 in number) are accessible through JDBC without problems. There
are 1316 tuples ("records") in the table. I don't see any error messages in any of the
logs I wonder what's the problem...

Below is the table definition:

DROP TABLE IF EXISTS inventar;
CREATE TABLE inventar
(nummer INTEGER NOT NULL,
bezeichnung VARCHAR (255) not null default ' ',
stueckZahl INTEGER not null default 1,
anschDatum DATE not null DEFAULT '1999-09-01',/* Anschaffungsdatum */
fvbNr INTEGER not null default 0, /* Firmennummer des Lieferanten */
typBesch CHAR(1) not null default 'k', /* Miete, Kauf, Leihe, Spende,... */
anschKosten FIXED (11,2) default 0, /* Anschaffungskosten in EUR */
repKosten FIXED (11,2) default 0, /* aufgelaufene Reparaturkosten in EUR */
abschreibung FIXED (11,2) default 0, /* aufgelaufene Abschreibung in EUR */
datEliminiert DATE NOT NULL DEFAULT '1999-09-01',/* aus Inventar ausgeschieden am... */
vorhanden SMALLINT NOT NULL default 1, /* Menge laut Inventur */
invDat DATE not null default '1999-09-01',/* Inventur-Datum */
bemerkung VARCHAR (255) not null default ' ',
PRIMARY KEY (nummer),
UNIQUE INDEX (nummer),
INDEX (fvbNr),
INDEX (typBesch),
FOREIGN KEY (fvbNr) REFERENCES fvbPartner(nummer),
FOREIGN KEY (typBesch) REFERENCES invBeschTyp(code)
)
CHARACTER SET latin1 COLLATE latin1_german1_ci
ENGINE=InnoDB
;

#2

When I run your Create Table script (copied from your Email) in MySQL Workbench I get the following error

Error Code: 1005. Can't create table 'sanmadb_test.inventar' (errno: 150) 0.187 sec

The 'sanmadb_test' bit is the name of the database I used to try and create the table.

I know that you said the table was "showing up" when using MySQl from the command line, but I wonder if it is actually being re-created when you use JDBC?
Is the table actually being created in your database?

Regards
Tony Bray

#3

Hi Heinrich,

CHARACTER SET latin1 COLLATE latin1_german1_ci
ENGINE=InnoDB
;

Try setting your collation to UTF-8, in both your table and the JDBC
driver connection parameters.

Alex

#4

Have a look at this thread :

https://forum.openoffice.org/en/forum/viewtopic.php?f=40&t=837#p4041

Alex

#5

And this :

http://lists.freedesktop.org/archives/libreoffice-bugs/2013-August/141543.html

Alex