Comparison of common Oracle databases with Access and SQL Server Applications

The following articles mainly introduce the comparison between Oracle databases and Access, SQL Server, and common applications. I made a product in a company, access, SQL Server, and Oracle databases are required. Some general statements should be used during SQL statement writing, but some differences may not be avoided, here is a brief summary.

The following A Indicates Access, S indicates SQL Server, and O indicates Oracle.

1. Obtain the current system time

A: Select Now ()

S: Select Getdate ()

O: Select Sysdate From Dual

2. connection string

A :&

S: +

O: |

3. String functions (intercepting strings, uppercase letters, lowercase letters, and searching string locations)

A: Mid, UCase, LCase, InStr

S: SubString, Upper, Lower, CharIndex

O: SubStr, Upper, Lower, InStr

4. Judgment value statement

A: IIF

S: Case When Then

O: Decode

5. Time Field

A: Use the # separator or convert it to A date.

SELECT * FROM Books WHERE RegDate = #2007-5-1 # Or

SELECT * FROM Books WHERE RegDate = CDate ('2017-5-1 ′)

S: You can use ''directly''

 
 

  
  
SELECT * FROM Books WHERE RegDate = ‘2007-5-1′  
  
  
O:To_Date  
  
  
SELECT * FROM Books WHERE RegDate=To_Date('2007-05-01','yyyy-mm-dd')  
 
 

6. Data Type Conversion

A: CStr, CInt, CDate

S: Convert

O: To_Char, To_Number, To_Date

7. Inser ..... Select... From Statement

 
 

  
  
Insert Into a(ii,jj,kk) (Select ii,jj,kk From b) 
 
 

Such a statement does not have errors in SQL Server and Oracle databases, but the syntax error of the INSERT INTO statement is reported in Access. Access should remove the brackets of the Select statement, as shown below:

 
 

  
  
Insert Into a(ii,jj,kk) Select ii,jj,kk From b 
 
 

At the same time, there will be no errors written in SQL Server and Oracle.

8. Keywords

Some letters are keywords in Access and SQL Server, which need to be defined by [] to solve the problem, for example: at, name

9. Alias

When fields in SQL Server and Oracle start aliases, as can be omitted, and Access cannot

10. Table name prefix

 
 

  
  
Select *,1 From A 
 
 

Such statements are not wrong in Access and SQL Server, but cannot be used in Oracle databases. They must be written as follows:

 
 

  
  
Select A.*,1 From A 
 
 

11. If the statement contains: (colon), for example, a query with a time field: CalcDate = #11:00:00 #, the following error occurs in Access: the parameter object is incorrectly defined. Provide inconsistent or incomplete information. Set the ParamCheck of the corresponding Query to False.

12. The logical values in Access are-1 and 0 in the database, while those in SQL Server are 1 and 0. Therefore, there is a compatibility problem when writing statements such as BoolField = 1. Change them to BoolField <> 0.

13. The varchar text type of Access has a maximum value of 255. Therefore, if a text field is greater than 255, it is best to define it as remarks type Access) or text type SQL Server );