Analysis of a common database operation error (2)

Looking for further information, we found similar problems when using Java to connect to SQL Server or access database. One of the netizens described the problem as follows:

I use JDBC-ODBC bridge, the code should be OK, ODBC data source is also configured successfully. But there is an exception:
[Microsoft] [ODBC driver manager] No data source name found and default driver not specified

I was puzzled. I went online to search for information and Google to find the solution. At this time, I have been working on it for two days, but I still cannot do it well. The third day, the Internet to see the use of SQL Server Driver to connect, do not use JDBC-ODBC, this is a success, but now still did not find out with me here to connect to the JDBC-ODBC can not.

Attached: Method for connecting JDBC to the SQL Server database.

First of all to download SQL Server 2000 driver for JDBC SP3, in Microsoft's view of the network (http://www.microsoft.com/downloads/details.aspx? Familyid = 07287b11-0502-461a-b138-2aa54bfdc03a & displaylang = EN ). Install the SP3 patch for the SQL Server 2000 database. Http://www.microsoft.com/china/ SQL /downloads/sp3.asp ).
After installing SQL Server 2000 driver for JDBC SP3, there will be three packages in its lib directory: msutil. jar, msbase. jar, and MSSQLServer. jar.

You can add these three packages in the environment variable classpath:

Create a jdbc_home = D:/Microsoft SQL Server 2000 driver for JDBC (this is the directory where you installed the driver)
Classpath =.; % jdbc_home %/lib/msbase. jar; % jdbc_home %/lib/MSSQLServer. jar; % jdbc_home %/lib/msutil. Jar

The preparation is over, in the program and the JDBC-ODBC bridge is similar, first register your driver,
Class. forname ("com. Microsoft. JDBC. sqlserver. sqlserverdriver"). netinstance ();

Then establish a connection
String url = "JDBC: Microsoft: SQL SERVER: // localhost: 1433; databasename = coffees ";

The localhost here is the IP address or host name of your database server. Databasename is the database you want to connect.

Summary: "It seems that the JDBC-ODBC bridge does not work. We need to use JDBC for SQL Server"

It seems necessary to study the knowledge of database connection on the Windows platform.

[Odbc api access database]

Before ODBC
Please allow me to turn that time into the second dark age. The first dark age is the era without databases.

The emergence of ODBC has ended the non-standard era of database development. The standards used for development of different databases before ODBC are not uniform. Generally, different database vendors have their own Database Development kits, which support two modes of database development: Pre-compiled embedded mode (such as Oracle proc, SQL Server esql) and API calls (such as Oracle's OCI ).

It is extremely painful for a developer to use the pre-Compilation Method for development. I have had this experience. All SQL statements should be written inside the program and follow certain rules, after being processed by the Pre-compilation tool of the database vendor, the C code is formed and compiled by the C compiler. The biggest problem with pre-compilation is the inability to dynamically generate SQL statements. I think it is hard to accept it as a programmer.

The next step is to use APIs for development, which is a huge step forward compared with pre-compilation. The database vendor provides sdks that allow you to connect to the database through various API functions, perform queries, modifications, and deletions, manipulate the cursor, and execute stored procedures. Programmers have more freedom and can create their own development kits. However, all the development can only target the same database.

Oracle's OCI is a very good C Language Development Kit. In ODBC, We have referenced the OCI design in many places.

ODBC Introduction
ODBC (Open Database Connectivity) is a unified interface standard proposed by Microsoft for database access. With the wide application of the Client/Server Architecture in various industries, interconnection and access between multiple databases has become a prominent problem, while ODBC has become a powerful solution. The reason why ODBC can operate a large number of databases is that most of the databases currently comply with the concept of relational databases in whole or in part, which is exactly what ODBC looks. Although it supports many databases, it does not mean that ODBC will become complex. ODBC is based on structured query language (SQL). Using SQL can greatly simplify the interface design (API) of its application ), ODBC is becoming more and more favored by many manufacturers and users because of its advanced thinking and no competition between similar standards or products. Currently, ODBC has become an important support technology in the client/server system.

In October 1994, ODBC had its first version. The technology named Open Data Base connection (Open Database interconnection) soon passed the standardization and was supported by various database vendors. ODBC solved two problems at the time. One was database development on the Windows platform, and the other was to establish a unified standard. As long as the development kit provided by the data vendor supports this standard, then, programs developed by developers through ODBC can be freely converted between different databases. This is indeed a celebration for developers.

ODBC adopts the X/opendata management: SQL call-level interface and ISO/ice1995 call-level interface standards. In ODBC version 3. X has fully met all the requirements of these two standards. Therefore, all content in this book is based on ODBC 3.0 and later versions.

At the beginning, only SQL Server, access, and FoxPro databases support ODBC. Microsoft products support ODBC, however, at that time, the GUI of Windows has become the most ideal carrier of the client software, so big data vendors also soon released drivers for ODBC.

ODBC does not appear as part of the system during Windows 3.x and Windows 95. It must be installed before use. However, when you install the operating system on Windows 98, ODBC does not need to be installed separately because it has become part of the operating system. This is not an excuse for many who refuse ODBC.

As a programmer, at least me, I can't find any reason not to cheer for ODBC. In addition, the ODBC structure is simple and clear. learning and understanding ODBC mechanisms and development methods will be helpful for learning other database access technologies such as ADO.

ODBC Structure
Figure 2.1 shows the ODBC structure.

Fig 2.1

Application)

The application itself does not directly deal with the database. It is mainly responsible for processing and calling ODBC functions, sending SQL requests to the database and obtaining results.

Driver Manager)

The driver manager is a dynamic link library (DLL) with an input program. It mainly aims to load the driver, process the initialization call of ODBC calls, and provide the parameter validity and sequence validity of ODBC calls.

Driver)

A driver is a DLL that completes ODBC function calls and interacts with the database. These drivers can process database access requests for specific data. For the commands sent by the application driver manager, the driver then interprets them to form commands that your database can understand. The driver processes all database access requests. For applications, you do not need to pay attention to the local database and the network database.

ODBC consistency
One of the advantages of ODBC interfaces is interoperability. programmers can create ODBC applications without specifying specific data sources. From the application perspective, to make every driver and data source support the same ODBC function call and SQL statement set, the ODBC interface defines the consistency level, that is, odbc api consistency and odbc SQL syntax consistency. SQL consistency specifies the requirements for SQL statement syntax, while API consistency specifies the ODBC function to be implemented by the driver. The consistency level helps developers of applications and drivers by setting up standard feature sets. Applications can easily determine whether the driver provides the required features, the driver can be developed to support application options without considering specific requests for each application.

[Use ODBCBasic knowledge about database development]

Create ODBC DSN
DSN (data source name) is used to specify an entry corresponding to the relevant ODBC driver. All DSN information is managed by the system, generally, when an application uses ODBC to access the database, you need to specify a DSN to connect to a specified ODBC driver. Open ODBC manager in the control panel and return to the 2.2 page.

Fig 2.2

DSN is divided into three types:

L user DSN: visible to the current Login User. It can only be used on the current computer.

L system DSN: visible to all users on the current system, including services in NT.

L file DSN: The DSN information is stored in the file, which is visible to users who can access the file.

The information in a DSN using the ACCESS database is as follows:

[ODBC]

Driver = driver do Microsoft Access (*. mdb)

Uid = Admin

Defaultdir = C:/www.vchelp.net/db

DBQ = C:/www.vchelp.net/db/chat.mdb

For file DSN, the information is stored in the file. for user DSN and system DSN, the information is stored in the registry. You can create a file DSN to view the information of each DSN.

The following example shows how to add an SQL Server DSN.

Fig 2.3

The four steps in Figure 2.3 are:

L select SQL Server as the driver

L enter the DSN name and SQL Server server address or Alias

L enter the user and password to connect

L select and complete the default database

ODBC files
You need the following file:

L SQL. h: contains basic ODBC API definitions.

L sqlext. h: contains the extended ODBC definition.

L odbc32.lib: library file.

These files are provided in vc6 and vc7 with development tools and do not need to be installed separately.

In addition, all ODBC functions start with SQL, such as sqlexecute and sqlallochandle.

Note: The above content is extracted from the http://www.vchelp.net/wyy/wyy.asp

What are other methods for further analysis?