Cross-platform database design)

Developers should know that using non-standard SQL commands (such as Oracle, Microsoft, MySQL, and other database systems) is highly risky. That is to say, from the perspective of cross-platform and compliance with standards, you should try to adopt ansi SQL, which is a database language unrelated to the platform. No matter which database system you are using, if it fully supports SQL, it should support ANSI SQL-92 standards. However, "should support" and "true support" are totally different. Not all systems fully support the ansi SQL standard during cross-platform DBMS programming. This articleArticleThe main purpose is to put forward the risks and related suggestions when using non-standard SQL.

Do not use stored procedures

In some programming environments, stored procedures becomeProgramThe only method for developers to develop database applications. Visual Basic, C, and Java programmers do not need to understand SQL. However, in some other programming environments, stored procedures are completely disabled. MySQL is usually used in combination with Apache Web Servers because these two software are free, reliable, and powerful. However, MySQL does not support stored procedures.

--------------------------------------------------------------------------------

Definition of Stored Procedure
A stored procedure is a set of SQL statements that constitute a logical unit and execute special tasks. Stored procedures are used to encapsulate a complete set of operations or queries for database servers.

--------------------------------------------------------------------------------

Both Oracle and SQL Server include "Built-in" stored procedures, which are convenient to use. However, if wise, never count on them. You cannot ensure that the database administrator has locked, deleted, or modified these stored procedures. If you find that some of the available stored procedures are exactly what you need, you canCodeTo use their functions.

Generally, you can use stored procedures on Oracle or SQL Server, but cannot use them on MySQL. If you have used them, you must ensure that you have not used commands related to a specific platform (for further discussion ).

Do not use connection
Connection is a headache between Oracle and SQL Server DBMS because the two systems have different connection concepts. Basically, the connection principle of each DBMS is not always the same, and you may eventually get an unexpected result set. Before talking about the concept differences of connections between systems and how to solve these problems, you should understand the basic connection clauses:

Connection
Join is the SQL data table creation command for retrieving data from multiple data sources.
And other connections
Equi-join obtains data from two independent data sources and combines them into a large table.
Internal/external connections
Inner join connects the internal columns of two tables. The outer join connects the external columns of the two tables.
Left/right join
Left join joins the columns on the left of the two tables. Right join connects the columns on the right of the two tables.
Composite/complex connection
Other connections: Left/inside, left/outside, right/inside and right/outside.
Remember, if you want your applications to work on a variety of database servers, do not use connections.

Ansi SQL: Create, drop, add, update, delete, insert, select
In short, the only command you should use is as follows:

Create and drop in the Data Definition Language (DDL.
Add, update, delete, and insert in the data operation language (DML.
Select to obtain data.
If you use case-sensitive table or field names in these commands, or these names contain spaces or other special characters, you 'd better enclose the names in square brackets. This helps prevent non-standard characters from causing abnormal DBMS responses.

Integrity of reference: Key and Data Type

The decisive reason for using relational databases is to clarify the relationships between data and maintain the integrity of those relationships. In this way, developers can store data in the most efficient and minimal redundancy mode. The key defines the link. Data Types define rules for storing and operating data. These are the basic aspects of a database, but the specific syntax may vary depending on the database.

Data Type
Only a few data types are supported by all database servers. Each DBMS has its own data type and why they are used in this way. Some proprietary data types (such as Microsoft Access's autonumber) are indeed quite convenient to use.

The following is an ANSI data type:
Bit, character, date, decimal, double precision, float, integer, interval, numeric, real, smallint, timestamp, time, varbit, varchar, char

The following are the "standard" data types not supported by Oracle/access/SQL:
Interval, time, varbit

Only Oracle supports date, but the following "standard" data types are not supported by Oracle:
Bit, decimal, numeric, timestamp

The following "standard" data types are not supported by access:
Bit, character, date, numeric, smallint, timestamp

In this way, only the following data types can be used in various databases:
Double precision, float, integer, numeric, real, smallint, varchar, char

Primary/foreign key

Assume that a table has two fields. The syntax for creating this table is as follows:
Create Table [mytable] ([fielda] varchar, [fieldb] varchar );

To add a primary key so that each record in the table is uniquely identified, you can use the primary key expression:
Create Table [mytablea] ([recordid] varchar primary key, [fielda] varchar, [fieldb] varchar );

When creating another table, where one field index is 1st tables, you can define this field as a foreign key with a link between the fields of the 1st tables:

Create Table [mytableb] ([recordid] varchar primary key, [fielda] varchar, [fieldb] varchar, [fieldc] varchar references [mytablea] ([fielda])

In the preceding example, all fields are of the varchar type. However, you must remember that a certain data type field can only be indexed for fields of the same data type.

ODBC and JDBC
If you want to use SQL database connection for the application you are writing, you can use ODBC to complete this function. If it is a Java application, you can use JDBC (in short, the Java interface of ODBC ).

Because you want your applications to be ignored by administrators, you should include all the necessary ODBC drivers for your DBMS. In this way, if users move from one platform to another, the drivers they need are already in place.

Your application should determine the DBMS corresponding to the ODBC driver currently used through programming. In this way, you can use powerful commands on database servers related to specific platforms.

The battle for alternative browsers
Of course, the most popular browsers are Microsoft's Internet Explorer and Netscape Navigator. However, no matter which one you are using, can it support all existing websites? Error! Many fancy things, such as DHTML, framework, and multimedia, are supported by non-standard HTML tags in both browsers.

Because the program is at a loss at the crossroads of these two international standards, you must write code for the two browsers or write the most universal functional code without a lot of dynamic content.

Database servers also face the same problems. ANSI SQL-92 is a standard that everyone agrees. However, Oracle, Microsoft, and other database vendors have added many features that disrupt SQL code. As a result, you not only have to write code that complies with the Basic specification (SQL-92), but also the Code has to implement different features according to the specific product used.

In your own applications, you must correctly decide what type of database the application will use and where the database should be located. You have to handle any errors generated by applications or users in a sophisticated manner. Of course, you do not want the program to crash (or the server to crash) Just because the file is deleted or the network connection is relaxed ). On a well-designed network, database servers are physically separated from workstation, Web servers, and application servers. Therefore, disconnecting is common.

Summary
As a web developer, you need to be highly careful about the dangers of using non-standard SQL commands in applications. The central idea of this article is to use ansi SQL to write cross-platform applications. Ansi SQL is used to reduce the headache caused by non-standard dialects in commercial database systems.