Backend database:
[Microsoft Access]
and
[Microsoft SQL Server]
After the replacement, the ASP code should pay attention to some places that need to be modified:
[1] Connection problems (example)
[Microsoft Access]
constr = "DBQ=c:dataclwz.mdb; DRIVER={Microsoft Access Driver (*.mdb)}"
[Microsoft SQL Server]
constr = "DRIVER={SQL Server};SERVER=host;DATABASE=mydata;uid=sa;pwd="
[2] Similar functions (example)
[1]DATEDIFF(datepart, startdate, enddate)
The "datepart" parameter options are as follows:
Setting Description————————————
[Microsoft Access]
yearyyyy
quarter q
month m
number of days in year y
day
Days of the week w
Zhou ww
hours h
minutesn
seconds
[Microsoft Sql Server]
year yy, yyyy
quarter qq, q
month mm, m
day of year dy, y
day dd, d
week wk, ww
hour hh
minute mi, n
second ss, s
millisecond ms
----------------------
It's basically the same, but pay attention when writing,
[Microsoft Access] Add quotation marks, such as: datediff('d',enddate,'2004/08/01')
[Microsoft Sql Server] is not required, such as: datediff(d,enddate,'2004/08/01')
[2][Microsoft Access] can use data type conversion functions such as cstr, and
In [Microsoft Sql Server], use the convert or cast function, such as:
convert(varchar,[amount]) etc.
[3][Microsoft Sql Server]
To get the current time, use getdate, etc...
[3] Statements
[Microsoft SQL Server]
available
CASE
WHEN THEN
WHEN THEN
...
ELSE
END
statement, while
[Microsoft Access]
Not supported.
[Microsoft Access] also does not support the between statement
[Microsoft Sql Server] can be written like this:
[date] between @date1 and @date2
[4] Query table
[Microsoft SQL Server]
It can be queried by joining three or more tables, and
[Microsoft Access]
It seems that only two tables can be joined for query (to be confirmed by the authority).
And [Microsoft Sql Server] can use "*=" and "=*" connectors. [5] Divide by zero problem
[Microsoft Access]
When the divisor is zero, the relevant records are automatically discarded, and
[Microsoft SQL Server]
An error will be reported and the query will be terminated. Delete code:
[Microsoft Access]
You can write it like this: delete * from [table]
[Microsoft SQL Server]
It can only be written like this: delete from [table]
Many times an error will be reported
________________________________________
Current date:
[Microsoft Access]
Use date()
[Microsoft SQL Server]
Using getdate(), if the database may change the type, you can add this to the ASP code:
if inStr(constr,"Microsoft Access") > 0 then
sqlstr=[Microsoft Access][sql code]
else
sqlstr=[Microsoft Sql Server][sql code]
end if
(constr--connection string)
In this way, even if the database is changed, there is no need to change the database query and update code.
In addition: there are true and false field records in access, but there are only smallint in sql. Correspondingly, if there is "field name=true" in access, it should be changed to "field name=1" in sql.
Most of the free ASP programs on the Internet use the access database. However, the access database, as a small and medium-sized stand-alone database system, is often overwhelmed when it is used for website applications with large amounts of visits and data. It is generally believed that the performance of an access database will begin to decline significantly when it exceeds 50M. After it exceeds 100M, the problems of errors and slow operation will become more prominent. Although we can try our best to optimize from a program perspective to improve performance as we did after Dongwang 7.0, it cannot fundamentally solve the problem.
At this time, perhaps using Microsoft's SQL Server database is the most likely way. Of course, you can also use other databases such as Oracle, MySQL, etc., but as a rewrite, since they are both Microsoft products, rewriting into SQL Server should be the most labor-saving. way.
1. Prerequisites for rewriting:
SQL Server2000 has been installed on the system and SP3 patch has been applied; Access in the Office suite has been installed; use an editor that supports plain text editing and has line number display. Ultra Edit is recommended, but of course FrontPage2003 can also be used. , but the line number display in the previous version was not very easy to use.
Personal ability requirements: Be able to understand basic ASP syntax, access database operations, and basic operations of SQL Server Enterprise Manager.
2. Generally speaking, there are two situations for database preparation
:
1. The program provides a SQL database format: there is an MDF file, or a SQL script file (suffix .sql) for creating a SQL database is provided.
If there is an mdf file, you can directly attach it using the Enterprise Manager. If the sql script file is provided, then use the Enterprise Manager to create a sql database yourself, and then use the query analyzer in the Enterprise Manager to run the script to create the database. Database table.
The database established in this way basically does not need to be rewritten.
2. Most of them do not provide SQL database or script files. At this time, you have to do it yourself. This is also the main problem our post solves. Generally, such a program will provide an access database, so you can use Enterprise Manager to import the access database. After importing, you need to rewrite the following things:
Compared with the original access, rewrite the following part:
(1) SQL database tables do not have automatic fields, so the original automatic fields in access are converted into ordinary fields and need to be manually changed to the identification type, with an increment of 1.
(2) For all time fields, if a default value is defined, it must be now() and needs to be changed to getdate()
(3) The default values of the original fields are generally not automatically introduced and need to be added manually against the fields of the original table.
(4) Due to different databases, many field types of access and sql change after conversion. For example, the original "whether" field will be converted to bit or int, the memo field will be converted to longtext, the text field will be converted to varchar, etc. , generally speaking, it will not affect program operation. If there is a problem, we will discuss it in the program rewriting section below.
(5) If you want to use a For SQL program that uses stored procedures, then you should have a way to create a SQL database in the program itself: have its own SQL database file, or sql script; if not, use import The access database method cannot create a stored procedure, so you'd better abandon this For SQL program version, use the same version of the For Access program, import the access database, and then use the following rewriting method to change it to the SQL version program yourself.
3. To rewrite the connection string,
please refer to this section of Dongwang, which is for access and SQL respectively.
DimConnStr
If IsSqlDataBase = 1 Then
'sql database connection parameters: database name, user password, user name, connection name (use local locally, use IP abroad)
Dim SqlDatabaseName,SqlPassword,SqlUsername,SqlLocalName
SqlDatabaseName = "dvbbs7"
SqlPassword = ""
SqlUsername = "dvbbs"
SqlLocalName = "(local)"
ConnStr = "Provider = Sqloledb; User ID = " & SqlUsername & "; Password = " & SqlPassword & "; Initial Catalog = " & SqlDatabaseName & "; Data Source = " & SqlLocalName & ";"
Else
'For free users, please modify the database address and the database name in the data directory accordingly, for example, change dvbbs6.mdb to dvbbs6.asp
'http://www.downcodes.com/
Db = "data/fengerqingqing.mdb"
ConnStr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & Server.MapPath(db)
End If
On Error Resume Next
Set conn = Server.CreateObject("ADODB.Connection")
conn.open ConnStr
Of course, if you use SQL, the access statement can be deleted, that is, after else and before on error resume next, it becomes like this:
Dim ConnStr
'sql database connection parameters: database name, user password, user name, connection name (use local locally, use IP abroad)
Dim SqlDatabaseName,SqlPassword,SqlUsername,SqlLocalName
SqlDatabaseName = "dvbbs7"
SqlPassword = ""
SqlUsername = "dvbbs"
SqlLocalName = "(local)"
ConnStr = "Provider = Sqloledb; User ID = " & SqlUsername & "; Password = " & SqlPassword & "; Initial Catalog = " & SqlDatabaseName & "; Data Source = " & SqlLocalName & ";"
On Error Resume Next
Set conn = Server.CreateObject("ADODB.Connection")
conn.open ConnStr
can also be more concise and written like this:
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "Provider = Sqloledb; User ID = sa; Password = 1234567; Initial Catalog = dvbbs7; Data Source = (local);"
Rewrite the database name, data source, user, and password according to your actual situation.
4. Program rewriting
There are two situations:
1. If you are lucky and get the For SQL program, then if there is no trouble in the above database creation process, the program can basically run. If there is an error, it is just a bug in the program itself. How to modify it is not discussed in this post. The content will not be described in detail.
2. In most cases, the program itself is For Access. The difference from the For SQL program is mainly the SQL query statements used in the program. Note that SQL query statements are an indispensable part of database applications. The syntax used by programs whether it is For SQL or For Aceess is roughly the same, but there are some subtle differences. It is these differences that make the program not universal, and what we need The main content of the modification. The parts that generally need to be modified are as follows:
(1) The problem of time function: The time function of SQL database is different from that of access. The most common function is to get the current time. Access is now() and SQL is getdate(). Therefore, wherever now() is used in the where clause, it must be changed to getdate(); note that the now() function must also be used in the asp program itself. Any now() function that is not used in database queries or execution statements Be sure not to change it.
(2) Time comparison function: datediff('d','time1','time2') This is the format used for access queries. These quotation marks in SQl must be removed. At the same time, # may be added before and after the time format. This also needs to be removed. Similarly, this also refers to the sql statement, and the asp statement should remain intact.
(3) Representation of null value: In access, judging the null value is generally expressed by whether ="", but this often goes wrong in SQL. If you encounter an error problem or the program does not run normally, you can change it to judge like this ：where (name is null)
(4) True and false value judgment: You can use =true and =false to judge in access, but it will make an error in SQL. Therefore, in SQL query or execution statement, this type of judgment should be changed to =1 and =0 respectively. Note: Although some programs write = "true", due to the quotation marks, this field is of character type. You cannot change it to =1, just keep it as is.
The above are the more common places to rewrite, and there are some that are less common. If you encounter them, you can reply here to discuss them.
5. Program debugging
It is recommended to use an editor with line numbers because the above rewriting is unlikely to be done by directly searching the program source code, and it is difficult to find it all.
The method I adopt is generally as follows: after the database rewriting is completed, the program is debugged directly. After an error occurs, look at the error message and find the code line of the corresponding file. However, the root cause is often not that line. For example, the error statement is: conn.execute(sql) , but this sentence itself is not wrong. The cause of the error is the sql string inside. Then look up how the sql string is generated and modify it according to the program modification method mentioned above.
After the database is imported, the automatically added fields need to be rewritten, and all numeric types need to be increased in length. It is best to use decimal.
All default values are lost. Mainly numeric types and date types.
All now(), time(), date() should be changed to getdate().
All datediff('d', time1, time2) should be changed to datediff(day, time1, time2).
It is possible that some true/false types cannot be used and should be changed to 1/0.
The remark type must be used through cast(column as varchar).
CursorType needs to be changed to 1, that is, the first numerical parameter must be given as 1 when opening the database, otherwise the record may not be displayed completely.
Whenconverting isnull(rowname) to rowname = null
, the automatic numbering type in the ACCESS database is not set to the automatic numbering type by SQL Server. We need to add identity to the SQL creation statement to indicate automatic numbering!
During conversion, SQL SERVER defaults to the smalldatetime type for date-related fields. It is best to change it to the datetime type, because the datetime type has a larger range than the smalldatetime type. Sometimes when using the smalldatetime type, the conversion fails, but when using the datetime type, the conversion is successful.
The SQL statements used to operate the two databases are not exactly the same. For example: when deleting records in the ACCESS database, use: "delete * from user where id=10", while when deleting the SQL SERVER database, use: "delete user where id=10".
The date functions are different. When processing the ACCESS database, functions such as date() and time() can be used. However, when processing the SQL SERVER database, only functions such as datediff and dateadd can be used, but not date( ), time() and other functions.
When processing the ACCESS database, some VB functions, such as the cstr() function, can be used directly in the sql statement, but when processing the SQL SERVER database, they cannot be used.
The following table compares a Microsoft Access database (Microsoft Access database: A collection of data and objects, such as tables, queries, or forms, related to a specific topic or purpose. The Microsoft Jet database engine is used to manage data.) and a Microsoft Access project (Microsoft Access project: Access files that connect to a Microsoft SQL Server database and are used to create client/server applications. The project file does not contain any data or data-based objects (such as tables or views). Field characteristics of data types. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default)).
Microsoft Access Data Type SQL Server Data Type Yes/No (Yes/No data type: A field data type used for fields with only two possible values (such as Yes or No, True or False). Null values are not allowed.) bit (bit data type: In Access projects, a data type that stores a value of 1 or 0. Accepts integer values other than 1 and 0, but always interprets them as 1.)
Number (Number data type: A field data type in Microsoft Access databases used for numeric data that will be used in mathematical operations. However, if you want to display or calculate monetary values, you should use the Currency data type.) (byte) tinyint (tinyint data type: a one-byte (8-bit) data type in the Access project, used to store integers ranging from 0 to 255.)
Number (integer) smallint (smallint data type: a 2-byte (16-bit) data type in the Access project, storing numbers between -2^15(-32,768) and 2^15-1(32,767) .)
Number (long integer) int (int data type: a 4-byte (32-bit) data type in the Access project, storing values between -2^31(-2,147,483,648) and 2^31-1(2,147,483,647) number.)
Number (single-precision floating point type) real (real data type: In Access projects, an approximate numeric data type with a precision of 7 digits, positive values ranging from 1.18E-38 to 3.40E+38, negative values The value range is roughly from -1.18E-38 to -3.40E+38, and can also be 0).
(No equivalent data type) bigint (bigint data type: an 8-byte (64-bit) data type in the Access project, stored between -2^63 (-9,223,372,036,854,775,808) and 2^63-1 (9,223,372,036,854,775,807) ).
Number (double precision floating point) float (float data type: In Access projects, an approximate numeric data type with a precision of 15 digits. The range of positive values it stores is roughly from 2.23E-308 to 1.79E+ 308, the negative value range is roughly from -2.23E-308 to -1.79E+308, it can also be 0).
currency (the "currency" data type: A data type in Microsoft Access databases used for currency-related calculations or fixed-point calculations where accuracy is extremely important.) money (money data type: in Access projects, used to store currency The data type of the value, the value range is from -922,337,203,685,477.5707 to 922,337,203,685,477.5807, the accuracy is one ten thousandth of a currency unit.)
smallmoney (smallmoney data type: a data type that stores currency values in the Access project, the value range is from - 214,748.3648 to 214,748.3647, with an accuracy of one ten thousandth of a monetary unit. When smallmoney values are displayed, they are rounded to two decimal places.)
Decimal/Numerical (decimal data type (Access database): Exact numeric data type used to store values from -10^38-1 to 10^38-1. Numeric range (maximum total digits) and precision (decimal point) can be specified Maximum number of digits on the right). ) decimal (decimal data type (Access project): Exact numeric data type used to store values from -10^38-1 to 10^38-1. Numeric range (maximum total digits) can be specified number) and precision (the maximum number of digits to the right of the decimal point).)
numeric (numeric data type: In Access projects, a precise numeric data type with values from -10^38-1 to 10^38-1. Can Specify the numeric range (maximum total number of digits) and precision (maximum number of digits to the right of the decimal point).
Date/Time ("Date/Time" data type: a data type in the Access database, used to store date and time information.) datetime (datetime data type: In Access projects, the data type of date and time, ranging from 1753 From January 1st to December 31st, 9999, the accuracy is three hundredths of a second, or 3.33 milliseconds)
smalldatetime (smalldatetime data type: a date and time data type in the Access project, the accuracy is not as good as datetime time. Data type. The data value range is from January 1, 1900 to June 6, 2079, with an accuracy of one minute).
AutoNumber ("AutoNumber" data type: A field data type in a Microsoft Access database that automatically stores a unique number for each record when a new record is added to the table. Three types can be generated Number: sequence number, random number and synchronous replication ID. ) (incrementing) int (int data type: a 4-byte (32-bit) data type in the Access project, stored at -2^31 (-2,147,483,648) and 2 ^31-1(2,147,483,647)) (the Identity attribute is defined).
Text ("Text" data type: A field data type in a Microsoft Access database. The "Text" data type can contain up to 255 characters, or a smaller number of characters specified by the FieldSize property.) (n) varchar( n) (varchar(n) data type: A variable-length data type in Access projects with a maximum length of 8,000 ANSI characters.)
nvarchar(n) (nvarchar(n) data type: In Access projects, a A variable-length data type that can contain up to 4,000 Unicode characters. Unicode characters occupy two bytes per character and support all international characters.)
Remarks ("Remarks" data type: a field data type in the Microsoft Access database. The "Remarks" field can contain up to 65,535 characters.) text (text data type: a variable-length data type in the Access project, Can store up to 2^31-1(2,147,483,647) characters; default length is 16).
OLE Object ("OLE Object" data type: One of the data types for fields used in objects created in other applications that can be linked or embedded (inserted) into an Access database.) image (image data type: In Access In the project, a variable-length data type can store up to 2^31-1 (2,147,483,647) bytes of binary data. The image data type is used to store BLOB (binary large objects), such as pictures, documents, sounds, and files. compiled code.)
Synchronous replication ID (aka Globally Unique Identifier (GUID): In Access databases, a 16-byte field used to establish a unique identifier for synchronous replication. GUIDs are used to identify replicas, replica sets, tables, records, and other objects. In Access database, GUID refers to synchronous replication ID. (GUID)) uniqueidentifier (uniqueidentifier data type: In Access projects, 16-byte globally unique identifier (GUID).) (Applicable only to SQL Server 7.0 or above. higher version)
hyperlink ("hyperlink" data type: The data type of the Access database field that stores hyperlink addresses. Addresses can contain up to four parts and are written in the following syntax format: displaytext#address#subaddress#.) char (char data type: in In Access projects, a fixed-length data type that can contain up to 8,000 ANSI characters. ),
nchar (nchar data type: In Access projects, a fixed-length data type that can contain up to 4,000 Unicode characters. Unicode Each character occupies two bytes, and supports all international characters), varchar, nvarchar (Hyperlink attribute is set to Yes).
(No equivalent data type) varbinary (varbinary data type: A variable-length data type in Access projects that can store up to 8,000 bytes of binary data.)
(No equivalent data type) smallint (smallint data type: a 2-byte (16-bit) data type in the Access project, stored between -2^15(-32,768) and 2^15-1(32,767) ).
(No equivalent data type) timestamp (timestamp data type: In Access projects, a data type that is automatically updated every time a row is inserted or updated. The value in the timestamp column is not datetime data, but binary(8) or varbinary(8), indicating the order of data modification).
(no equivalent data type) charnchar
(No equivalent data type) sql_variant (sql_variant data type: a data type in the Access project that stores values of multiple data types other than text, ntext, image, timestamp and sql_variant types. In columns, parameters, variables or used in the return value of a user-defined function).
(No equivalent data type) User-defined (User-defined data type: In a Microsoft SQL Server database, the type definition of the data contained in a column is allowed to be defined by the user using existing system data type definitions. Rules and default values can only be bound to Defined to a user-defined data type. )
Note In an Access project or SQL Server database, the prefix "n" stands for "country/region", meaning that this data type is Unicode enabled. In Access databases, all text columns are Unicode-enabled by default.
Issues you need to pay attention to when converting ACCESS to SQL
2006-2-13 16:01:20
Many friends want to use SQL2000 database programming methods, but they suffer from the fact that they are learning ACCESS and only have a little understanding of SQL. Here I provide you with the following reference---Methods and precautions for converting ACCESS into SQL2000. , First of all, what I’m talking about is converting between ACCESS2000 and SQL2000. I haven’t tried the other ones yet. I hope you can experiment more, and there must be a way;
2. Conversion method
1. Open "Database Source" in "Administrative Tools" under "Control Panel";
2. Click "Add" to add a new data source, and select "DriverdomicrosoftAccess" in the selection column.
(*.mdb)", a box will appear after completion.
Enter the name you want to write in "Database Source". I named it "ABC", indicating that there is no need to fill in. Then, press the selection below to find your Database address and selection (note, please back up your ACCESS database first), and then confirm.
The data source is created here, and all that remains is conversion.
3. Open SQL2000 Enterprise Manager, enter the database, and create a new empty database "ABC";
4. Select the newly created database, right-click the mouse, select "Import Data" under "All Tasks", and click "Next" to continue;
5. Select "DriverdoMicrosoft Access (*.mdb)" in the database source drop-down list. In "User/System DSN", select the "ABC" you just added and click "Next";
6. "Purpose" does not need to be modified. Select the server (usually your own machine "local", you can also select the server address or LAN address to determine whether your permissions can operate), "Use WINDOWS authentication" means use For your own system administrator identity operation, "Use SQL identity operation verification" can be used for website operations, and the latter is recommended;
7. After selecting "Use SQL identity operation verification", fill in your user name and password. I chose the system default numbers "sa" and "****". For the database, select the newly created "ABC" and press " Next step";
8. There are two single choices in this step, "Copy tables and views from the data source" and "Use a query command to specify the data to be transferred". Select the former and click "Next" to continue;
9. The tables of your own ACCESS database will appear here. Click "Select All" and go to the next step;
10. "DTS Import/Export Wizard", see that "Run Now" is selected and click "Next".
11. Press "Finish" to continue;
12. In this step, you will see your data being imported into SQL2000. When the words "XXX tables have been successfully imported into the database" appear, and there are green ticks in front of all tables, it means that all data has been successfully imported. If a problem occurs during the process or there is a red * in front of the table, it means that the table was not successfully imported. At this time, you need to go back and check whether your operation is correct.
3. Data modification
1. Since there is no "automatic numbering" in SQL2000, the fields you set with "automatic numbering" will become non-empty fields. In this case, you must manually modify these fields and select "Yes" for "Mark". The seed is "1", the increment is "1",
2. In addition, after converting ACCESS2000 to SQL2000, the fields with the original attribute "yes/no" will be converted into non-empty "bit". At this time, you must modify it to the attribute you want;
3. In addition, everyone should pay attention to the grasp of time functions. There are many differences between ACCESS and SQL.
4. Related field issues
1. When converting the automatic numbering type in the ACCESS database, sqlserver did not set it to the automatic numbering type. We need to add identity to the SQL creation statement to indicate automatic numbering!
2. During conversion, SQLSERVER defaults to the smalldatetime type for date-related fields. It is best to change it to the datetime type, because the datetime type has a larger range than the smalldatetime type. I encountered this situation. When using the smalldatetime type, the conversion failed, but when using the datetime type, the conversion was successful.
3. The SQL statements used to operate the two databases are not exactly the same. For example: when deleting records in the ACCESS database, use: "delete*fromuserwhereid=10", but when deleting the SQLSERVER database, use: "deleteuserwhereid=10".
4. The date functions are different. When processing the ACCESS database, functions such as date() and time() can be used. However, when processing the SQLSERVER database, only functions such as datediff and dateadd can be used, but not date(), time( ) and other functions.
5. When processing the ACCESS database, some VB functions, such as the cstr() function, can be used directly in the sql statement, but when processing the SQLSERVER database, they cannot be used.
5. Related statement issues, automatically adding fields need to be rewritten. The automatic numbering field that is often used in access is not an auto-incrementing int after it is imported into mssql. It needs to be set manually. Change the "no" of the imported automatic numbering field to "yes" and "seed". and "increment amount" are both "1" to become an automatic number.
All default values are lost. Mainly numeric types and date types
All now(), time(), and date() must be changed to getdate()
All datediff('d',time1,time2) should be changed to datediff(day,time1,time2)
All datediff('ww',time1,time2) should be changed to datediff(week,time1,time2)
All datediff('d',time1,time2) should be changed to datediff(day,time1,time2)
In mssqlserver, there are many reserved words that are not available in access. When you import data into mssql, problems arise. When mssql is imported, it will automatically add "[field name]" to these fields (including table names in the database). Therefore, you must modify your script and add the corresponding field names (or table names). brackets, or change the field name to a reserved word that is not mssql
When using access to use time, everyone likes to use SQL statements such as "select*fromaaaawhiletime="&now()". However, there is no "now()" function in mssql, but "getdate()" is used, so , "now()" in all sql statements must be replaced by "getdate()".
The date functions are different. In processing the ACCESS database, functions such as date() and time() can be used, but for
In SQLSERVER database processing, only functions such as datediff and dateadd can be used, but functions such as date() and time() cannot be used.
During conversion, SQLSERVER defaults to smalldatetime type for date-related fields. We'd better change it to datetime type.