Summary of general database software completion

After about a month of software development, I thought it would be made a J2EE-related component, which was taken the wrong path by the graduation design mentor of make (the name of this person is hidden here, it can even be developed into a complete software.

In the last few days, the basic time was wasted on reconcile database connections, and the software used five databases.
SQL Server, MySql, Oracle, DB2 UDB, Sybase

On my machine: their respective libraries are saved to the jbuilder/lib/ext folder and partially saved to the software driver for future use.

I. The following are the related problems:

1. SQL Server
It must be installed with sp3 before it can be successfully completed. Sometimes the machine can only be accessed by its own machine and cannot be connected by other machines. It will be very effective to restart it.
The stored procedure queries all the table names in database xx.
Exec sp_tables
Obtain all databases
Exec sp_databases

2. MySql
The user name entered by winmysqladmin.exe sometimes fails to be logged on to odbc @ localhost ......, I don't know what's going on.
Use mysql
Insert into user (host, user, password) values ("","","")
You can use this user to log on to a new user.
You can create a database at the mysql> prompt.
3. Oracle
This is a big guy. The first thing to note is that it will be difficult to install a machine when it is installed again in oracle. Therefore, we have to carefully uninstall the machine or reinstall it,

Therefore, installing it on a virtual machine is a good choice.
 
During the second installation, such problems may occur even if the listener is installed. For example, if a user is clearly present, the listener is open, but the listener cannot be connected. The reinstallation is not another reason, don't hesitate.

For example, Oracle provides two JDBC drivers types:
-Jdbc thin for Java applets and Applications
-Jdbc OCI for Java applications
The URL varies according to the JDBC used.
// Oracle JDBC thin driver
Class. forname ("oracle. JDBC. Driver. oracledriver ");
Connection dbcon = drivermanager. getconnection ("JDBC: oracle: oci7: @ mydatabase", "Scott", "Tiger ");

// Oracle jdbc oci driver
Class. forname ("oracle. JDBC. Driver. oracledriver ");
Connection dbcon = drivermanager. getconnection ("JDBC: oracle: thin :@ myhost: 1521: orcl", "Scott", "Tiger ");

Create a user in Oracle
We recommend that you create user luck identified by 123; (no "")
Grant connect, resource to luck;

The default user has the system password manager.

Each user has a different user table.

4. DB 2
I installed it as 8.2. The cause is that when the database is in use, you can delete it. A serious problem may cause that you cannot even start the service.
The default username and password are db2admin db2admin.
You can use db2admin to log on to other software and query tables in the db2admin mode. Therefore, you must build your tables in the db2admin mode.

1) com must be capitalized.
COM.ibm.db2.jdbc.net. DB2Driver or
COM. ibm. db2.jdbc. app. DB2Driver
2) the url and class do not match
Jdbc: db2: // localhost: 5000/sample to use the net type.

3) ensure that db2java.zip is in the class path
This file is under sqllib/java /.
As long as the jdbc driver version used by your program is the same as that used by the remote database.

Driver used to remotely connect to the DB2 database: Use COM.ibm.db2.jdbc.net. DB2Driver
5. Sybase
This is a very similar program to SQL Server. The first problem is that the "can't open scsslgen" plug-in will appear when you open central. You can go to the installed root directory (this root directory

This is not a common root directory. For example, if you install c:/sybase, the root directory is c:/.) Search for scsslgen in the sybtool and change it to scsslgch ...... En. cnt

File must also be renamed ...... Cn. cnt. This is to solve the Chinese problem. The sybase version I used is 11.5. By the way, he took the powerdesigner I always wanted.
In addition, it does not support Metadata Query. For example, conn. getMetaData does not support metadata query.
The default user name is Sa password is blank

Remember the username and password for all databases.
2. In JBuilder, you only need to add the library in project properties.

3. Most of the software uses metadata to obtain the user table name. Here there are several query statements for selecting the user table from the system table:
Oracle

Select table_name from user_tables order by table_name

MySQL

Show tables

Ms SQL Server

Select name from sysobjects where type = n' U' order by name

DB2 UDB

Select name from sysibm. Variables Ables where type = 'T' and creator! = 'Sysibm 'order by name

4. Multi-Database Support Methods
Application software vendors need to support different database products when facing different customer needs, but the SQL difference between different database products is huge.

For example, the following SQL statements are written differently in different databases.

Select top 1 * from T1 left join T2 on t1.id = t2.parentid order by t1.id
ORACLE:

Select * from (
Select * from T1, T2 where t1.id = t2.parentid (+) order by t1.id ASC
)
Where rownum <= 1

SYBASE:

Set rowcount 1
Select * from T1 left Outer Join T2 on t1.id = t2.parentid order by t1.id ASC
Set rowcount 0

MySQL:

Select * from T1 left Outer Join T2 on t1.id = t2.parentid
Order by t1.id ASC limit 0, 1

Ms SQL Server:

Select top 1 * from T1 left Outer Join T2 on t1.id = t2.parentid order by t1.id ASC

DB2 UDB:

Select * from T1 left Outer Join T2 on t1.id = t2.parentid
Order by t1.id ASC fetch first 1 row only

5. It is difficult to support multiple databases. There are two methods to support most databases.
The first method is to write a database abstraction layer for different implementations of different databases.
The second method is to use a common SQL to translate common SQL into dialect SQL using a Translation Engine.
Currently, many open source code projects support multiple databases. This is usually the first method. The first way is to ask programmers to be familiar with different databases.

Yes, it is too high for most programmers. Hibernate, including the well-known open-source project, only uses a small part of the database functions, and does not have the advantages for different databases.

Options. Different databases have different support for query hints and locking hints, while hints is an important way to optimize database access. In the implementation process

It takes up a considerable part of the open implementation and affects the maintainability of the program.

In the second way, you need to define a set of common SQL, close to the SQL-92 standard, similar to a dialect SQL, for example, can be similar to the ms SQL Server's transact SQL. Yes

A Translation Engine is required to translate common SQL into different dialect SQL statements, such as DB2 SQL, Oracle P/l SQL, and Microsoft transact SQL. In this way, most programs

You only need to understand one type of dialect SQL. If you define common SQL based on Microsoft's transact SQL, most programmers know the transact SQL of SQL Server.

You can support multiple databases without training costs.

At present, a foreign company provides an SQL translation product, http://www.swissql.com/, but the translation engine of the company is still immature, with many buckets. There are also PHASES IN CHINA

The corresponding products provided by software vendors are more mature than swissql, and the translation speed is also quite fast.

6. Obtain metadata
Gettables of databasemetadata
Obtain information in the table
You can use the gettables () method on the databasemetadata object to obtain information about tables in the database. This
Methods include the following four string parameters:

Results = DMA. gettables (catalog, schema, tablemask, types []);

The parameter indicates:

Catalog
The directory name used to find the table name. For JDBC-ODBC databases and many other databases, it can be set to null. The directory entries of these databases are actually in the file system

Absolute path in.

Schema
The schema of the database to be included. Many databases do not support schema, but for other databases, it is the user name of the database owner.

Tablemask
A mask that describes the name of the table you want to obtain. If you want to obtain all the table names, set them to wildcard %. Note that SQL uses % as the wildcard rather than * on PC *.

Types []
A string array that describes the types of tables you want to obtain. Databases usually contain tables for internal management, which are useless to users. If it is set to null,

You will get all the tables. If the array contains only one element and the element is set as a string "table", you will get the tables that you are interested in.

Obtain the name of the table in the database, and obtain the name of the table from the database before obtaining the databasemetadata object.