To construct a common function between adaptive Server anywhere and Oracle

oracle|server| function constructs a common function between adaptive server anywhere and Oracle
Jin-mining geological Brigade, Deng Xianyong 01-7-2, Liaohe oilfield, 10:29:41

The most used database interface when using PowerBuilder is its own integrated adaptive Server anywhere and large database management system Oracle; Sometimes, in order to make programs run and migrate more easily, For example, from the server's data management side immediately to the single computer data management end, often involves the user in the program SQL statements used in different database platform, function compatibility problem. For example, in the final data rollup, in order to ensure the correctness of the calculation results, we need to use a function to prevent null values, this function is NVL in Oracle (X,Y); use: Select NAME,NVL (age, unknown) Age,nvl (ZipCode, " No postal Code ") ZipCode from unknow_personal; the possible results are: Name Age zipcode 345 No postal code dick ages unknown 234567 Harry age unknown No zip code in adaptive serv Er anywhere on a stand-alone database platform, it is necessary to use functions such as IsNull, the syntax is: Select Name,isnull (age, unknown) age,isnull (zipcode, "no zip Code") ZipCode from Unknow_personal; The final result is the same. The problem now is that when we use a database to be ported from Oracle to a stand-alone version of adaptive Server anywhere, because of the generality of some functions, the program will modify the function call and recompile, if the function is used in large or many places in the program, is likely to lead to human error, so that the maintenance of the program increased workload, I have been posted on the Internet with relevant help posts, but not very pertinent. After careful study for some time, finally on the adaptive Server anywhere stand-alone platform to implement the functions of Oracle NVL (), so that my application more convenient, now write down to share with you. In addition, I used to use PB6 time, in the PB6 database Administrater painter has written a more complex stored Procedure, but because the PB6 in the processing and database direct interface script, for the " ; "" and "'" is more inconvenient to use, so it takes a lot of effort to achieve, although this problem still exists in PB7, but the adaptive Server Anywhere 6.0 management tool integrated in PB7 CD is much better than that in PB6, usingThe Manager Adaptive Server anywhere tool makes it easy to manipulate the database. First, if you have installed the PB7.0.1 correctly, you can start the Manager Adaptive server anywhere by following these steps: Start →sybase→manager Adaptive server anywhere. The admin interface appears after startup, and then follow these steps: Choose Menu Tools→connect→adaptive Server Anywhere, and then select the data source you configured in System ODBC ( PB7.0.1 for this process is better, the various data sources are integrated into the system of the ODBC configuration to carry out, more formal, may be beginners are not too adapt to it! , if necessary, enter the user name and password for the connection database at User ID: and password: And then press OK to connect to the database. For example, we connect ASA 6.0 Sample, of course, users in the actual application will be connected to their own database, if the connection is successful, will be under Sybase Central Adaptive Server anywhere under the icon Asademo; double-click the mouse, Open Asademo, and then double-click Asademo (DBA) to open the tables,views,procedures&functions under it
And so on, click Rocedures&functions
Item, the process and function supported in the currently connected database appears in the right window, and the Add function (Template) is clicked in the right window, and the new function window appears with a general frame of the function in the window. However, to implement the NVL () function in Oracle, change the statement in the new function window to the following form: Create function Nvl (in x text,in y-text) returns Textbegindeclare Z Text;if x is null thenset z=yelseset z=xend If;return (z) end for several lines of script, I have tried several times to succeed, first of all (in x text,in y text), indicating that the function has two input parameters Number: X,y, and the data type is text. This text data type is really interesting, I think it is Sybase company very good idea! In the beginning, I used the real type, but it can work with numeric fields, but it doesn't work when you're working with character fields, and then after a careful analysis of the type of data that adaptive Server anywhere offers, text is ok!. Later script, I wanted to directly call the adaptive Server Anywhere IsNull () function, but did not succeed, welcome the broad masses of netizens actively participate in the discussion; As for the other script, let's take a closer look at adaptive server The help of anywhere should be written out. Then, select the File→excute script for the new function window to execute this statement, and then return to the manager Adaptive Server Anywhere management interface, procedures& In the functions item, there will be a NVL () function, which can be used to participate in combat. There's another step down, originally this step is dispensable, because in the database as long as the definition of NVL (), you can write directly in the PB with Nvl () script, and will not appear syntax errors, but, in order to users in the PB directly call, even if it is to form a complete application, Suggest that we still adhere to the following process, anyway, not too strenuous, in addition to long knowledge. When you build DataWindow, you believe you've used the functions in the database, especially if you're using a computed column, it's possible to use a function, and now we're going to implement an oracle-like N in adaptive Server anywhereThe VL () function, which appears in the name of a function that can be directly selected by DataWindow painter (originally available with Oracle Lianku, but adaptive () function is optional with Lianku Server anywhere isnull, without NVL () Optional), after our analysis, you can use the following steps to achieve: 1. Locate the Pbodb70.ini file under the Hared\powerbuilder of the Sybase installation directory, open it, locate [adaptive Server Anywhere], and see pbfunctions= ' Asa_ Functions ', that is, the function used by the adaptive Server anywhere database is obtained from the asa_functions, so long as we put the Declaration on NVL () to the corresponding part of the asa_functions, we can In the Server Anywhere database, the NVL () function is used directly in the PB development interface (this shows the importance of the INI file in PB). 2. In the Pbodb70.ini file find the [asa_functions] of the functions segment, in Functions=abs (x) ... The right place to add a statement about NVL (): "Nvl (X,y)," (Recommended to Ifnull (X,y,z), where). Save and then start PB, you can see that even with the adaptive Server Anywhere database, you can use the NVL () function in Oracle. This has a significant meaning: after the implementation of such a function, the user's program can be in the server version and stand-alone version of the common, eliminating the server version and stand-alone version of the development of different code of the trouble; Once the same library structure on a stand-alone database, the same as the server to load similar data, you can use the same. The above is my daily experience of development, errors and deficiencies are unavoidable, this article aims to stimulate the use of database development tools PowerBuilder, and in this article on all aspects, there are many can open a forum, discussion of the need to welcome the broad masses of Pb, VC developer and I contact hard_deng@netease.com. June 9, 2000 In addition, I've created several other functions: The Ceil, add_months, and TO_CHAR functions that are generated under Sqlanywhere: Create function Ceil (in x Real) returns Integerbegindeclare Z integer;set Z=ceiliNg (x); return (z) endcreate function add_months (in x date,in y integer) returns datebegindeclare z date;set z=months (x,y); Return (z) endcreate function To_char (in x date,in y-TEXT) returns Textbegindeclare z Text;declare II integer;if y= ' yyyymm ' t Henset ii=112end if;set Z=convert (char (8), x,ii); set z= "left" (z,6);