Constructor between Adaptive Server Anywhere and Oracle Liaohe Oilfield Jinling Geological Brigade Deng Xianyong 01-7-2 at 11:29:41
When using the PowerBuilder programming, the most database interface is used by its own integrated Adaptive Server Anywhere and large database management system Oracle; sometimes, in order to make the program run, transplant is more convenient, such as from the server's data management terminal When a single-machine data management is used, it is often related to the compatibility of the user on the different database platforms used in the program's SQL statement.
For example, in the final data aggregation, in order to ensure the correctness of the calculation result, use a function that prevents null values from occurring, this function is NVL (X, Y) in Oracle; usage is:
Select Name, NVL (Age, "Age Unknown") AGE, NVL (Zipcode, "None Postal Coding") Zipcode from UNKNOW_PERSONAL DIPCODE
The result that may occur is:
Name Age Zipcode
Zhang San 45 No Postal Code
Lee Fourth Age Unknown 234567
Wang five age is unknown without postal code
On the stand-alone database platform of Adaptive Server Anywhere, you have to use Isnull and so on, the syntax is:
Select Name, ISNULL (AGE, "Age Unknown") AGE, ISNULL (Zipcode, "None Postal Coding") Zipcode from UNKNOW_PERSONAL DIPCODE
The last result is the same.
Now the problem is when we use the database to be placed from Oracle to the stand-alone version of Adaptive Server Anywhere, the program is to modify the corresponding function call, recompile if it is in the program. When using this function, it is very likely to cause human errors, so that the maintenance work of the program is increased, I have sent the relevant help post on the Internet, but I don't cut the principle. After a study, after a period of time, I finally realized the function NVL () on the Adaptive Server Anywhere stand-alone platform, making my program app more convenient, and now write down.
In addition, I used to use PB6 before, I have written more complex Stored Procedure in PB6 Database Administratr Painter, but because of the Script of the Database Direct Interface in PB6, for ";" and "'" More inconvenient, there is a lot of strength, although this problem is still present in PB7, but the management tool integrated in the PB7 CD is more than a lot of excellent in PB6. Using Manager Adaptive Server Anywhere This tool can easily operate the database.
First, if you have installed PB7.0.1 correctly, you can start the Manager Adaptive Server Anywhere: Start → Sybase → Manager Adaptive Server Anywhere.
There is a management interface after startup, then follow these steps: select menu Tools → Connect → Adaptive Server Anywhere, then select the data source you configure in the system ODBC (PB7.0.1 is better to handle this, integrate various data sources In the ODBC configuration of the system, it is more regular. Maybe beginners are not well adapted!), If necessary, you can enter the user name and password to connect the database at User ID: and Password:, then press OK, Database connection. For example, we connect the ASA 6.0 Sample. Of course, users will connect their database in practical applications. If the connection is successful, the Asademo icon will appear under Adaptive Server Anywhere under Sybase Central; double-click the mouse, open asademo; Double-click on ASADEMO (DBA), open the child's tables, views, procedures & functions, etc., click the rotedures & functions item, and the process and functions supported in the currently connected database will appear in the right window, double-click Add Function in the right window. (Template), the New function window appears, there is a rough framework of a function in the window, but in order to implement the NVL () function in Oracle, change the statement in the New Function window as follows:
Create Function NVL (in x text, in y TEX)
Returns text
Begin
Declare z text;
IF x is null then
Set z = y
Else
SET Z = X
END IF;
Return (z)
end
For the above rows of Script, I also tried several times successful, first is the above (in x text, in y tom), indicating that the function has two input parameters: x, y, and the data type is Text. This text data type is really interesting, I think it is a good idea of Sybase! At the beginning, I used the REAL type, but it can handle the numeric fields, but when the field of handling the character, it is not good, and then carefully analyzes the data type provided by Adaptive Server Anywhere. Use text everything is OK! The back Script, I have wanted to directly call the ISNULL () function of Adaptive Server Anywhere, but there is no success. Welcome netizens to actively participate in discussions; as for other script, everyone should take a closer look at the help of Adaptive Server Anywhere, it should be written of.
After that, select the file → Excute Script of the New Function window to perform this statement, then go back to the management interface of Manager Adaptive Server Anywhere. In the ProCedures & Functions item, there will be nvl () functions, use it to participate in actual combat .
There is another step, this step can be available, because after the database is defined in the database, you can write directly with NVL () script directly, without syntax errors, but In order to be directly called in the PB, even if you form a complete application, it is recommended that everyone still insists on completing the following process, anyway, not too strong, and can also have a long knowledge. Everyone is building DataWindow, I believe that the functions that have been used in the corresponding database, especially when using the calculation column, it is likely to use the function, now we have to make similar to Oracle in Adaptive Server Anywhere. The NVL () function allows it to appear in the function name that the DataWindow PainTer can select (this is available in the Oracle library, but there is an isnull () function when using the Adaptive Server Anywhere library, and there is no NVL () Optional), after our analysis, you can use the following steps:
1. Find the PBODB70.INI file under the Sybase installation directory, open it, find [Adaptive Server Anywhere], you can see PBFunctions = 'asa_functions', that is, the function used by the Adaptive Server Anywhere database is obtained from ASA_Functions. As long as we put a statement about NVL () in the corresponding part of ASA_Functions, you can use the NVL () function in the development interface of the PB in the Adaptive Server Anywhere database (which is important in the INI file in the PB). Sexuality).
2. In the PBODB70.INI file, find [ASA_Functions] in the Functions section, in the appropriate place of functions = abs (x) ... "NVL (X, Y)," (recommended to IFNULL (X , Y, Z), place) The deployment is exited, then start PB, you can see that even if you use the Adaptive Server Anywhere database, you can use the NVL () function in Oracle.
This is a more significant meaning that after this function is implemented, the user's program can be universal in the server version and the stand-alone version, saving the trouble for the server version and stand-alone version, once in a single machine On the database, the same library structure as the server is established, and after loading similar data, you can use it.
The above is the experience of the daily development, the wrong and insufficient omissions are inevitable, this article is intended to throw the jade, regarding the use of the database development tool Powerbuilder, and there are many aspects described above, there are many open spaces, discuss one The necessary PB, the VC developer is welcome to contact me with hard_deng@netese.com.
June 9, 2000
In addition, I still have several other functions: the CEIL, add_months, and to_char functions that are generated under SQLANYWHERE:
Create Function CEIL (in x real)
Returns integer
Begin
Declare z integer;
SET Z = CEILING (X);
Return (z)
end
Create Function Add_MontHS (In x Date, In Y Integer) Returns Date
Begin
Declare z Date;
SET Z = MONTHS (X, Y);
Return (z)
end
Create function to_char (in x Date, in y TEX)
Returns text
Begin
Declare z text;
Declare II integer;
IF Y = 'YYYYMM' THEN
SET II = 112
END IF;
Set Z = Convert (Char (8), X, II);
SET Z = "Left" (z, 6);
Return (z)
end
