How to connect sqlserver and Oracle at the same time in framework1.0

Source: Internet
Author: User
A project just a while ago is to port the database of SQL Server to Oracle. The requirement is to modify the original code so that the application can run on two different databases at the same time. This is my first attempt to operate Oracle using C. I 've taken a lot of detours, but now I want to take a few detours.

1. in Oracle, the table name, field name, stored procedure name, and variable name cannot exceed 30 string lengths.

2. Top syntax is not supported in Oracle. Use where rownum <n instead. However, you need to note that if there is a sorting operation before top, you need to first sort the operation and get it using rownum.

3. The usage of optical standards in Oracle is quite different from that in SQL Server, mainly in terms of loop control.

4. If a column in Oracle is of the date type, the default format is yyyy-mm-dd.

Where coldate = '2017-06-08 17:14:57 'will result in an error, '2017-06-08' Can. If you need to accurately compare the time (hours, minutes, seconds), you can use the following methods: to_date ('2017-6-8 17:14:57 ', 'yyyy-mm-dd hh24: MI: SS '). Since Oracle and sqlserver have different implementation methods for SQL standard expansion on datetime, the same

SQL implementation.

5. The timestamp (timestamp) in Oracle differs greatly from that in SQL Server. Timestamp in sqlserver is stored in the database in binary format. You can set this field type in dataset to base64binary. Timestamp in Oracle is stored in time format. Therefore, you cannot use the same dataset to load both SQL Server Timestamp and Oracle timestamp. Because this value is rarely used in applications, you can not retrieve this field.

6. The timestamp in Oracle cannot be automatically filled when SQL Server imports data. You can set the default value of systimestamp to implement similar functions.

7. Oracle does not have the Boolean field type. You can use intger or Char to replace the bit type in SQL Server.

8. Oracle will treat "null strings with multiple null values. That is to say, when an application inserts an empty string into the database, the actual database will try to insert a null string. If this field is of the notnull type, an error occurs. In this case, you can use another default value to replace the Null String. When importing data from sqlserver to Oracle, an empty string in the notnull column will cause the export operation to fail. In this case, you can manually modify the SQL script in the export wizard and set the null string to a special value.

9. When using oledbcommand to call the Oracle stored procedure, you should note that the parameters passed in by the application and stored procedure match in sequence rather than by name, which is essentially different from SQL Server. If you encounter errors such as e_fail during debugging, you can check the program in the following sequence.

(1) check whether the number of input parameters of the application is consistent with the number of parameters in the stored procedure.

(2) It is important to check whether the order of input parameters of an application is the same as that of stored procedure parameters. mismatch may easily lead to various seemingly strange problems. We recommend that you write an automatic generation tool for a relatively simple Stored Procedure (adding, deleting, and modifying a single table) to complete this operation.

(3) check whether the stored procedure is correct.

10. Pay special attention to using oledbdatareader objects. In SQL Server, we can use a SELECT statement to return a datareader In the stored procedure. However, we cannot do this in Oracle. We must use ORACLE ref cursor to return the query result. However, you must use the oraclecommand. executereader () method instead of the oledbcommand object. Therefore, to use datareader in Oracle, you must use the for ORACLE data provide class package provided by Microsoft. This framework1.0 does not exist by default and needs to be downloaded. It exists by default in framework11.1. For details, refer:

Http://msdn.microsoft.com/library/chs/default.asp? Url =/library/CHS/cpguide/html/cpconusingadonetproviderstoaccessdata. asp

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.