How to quickly export Sybase stored procedure Xu Maohong 2002-7-25 14:14:28
statement of problem
In an application system, if a large number of Sybase stored procedures are written, the stored procedure is systematically, and the backup is required. And the way we usually use is to select the stored procedure using the Sybase Central tool provided by Sybase, and then select the stored procedure back up by right-click. Using this way, not only the backup time is particularly long (when the backup file is about 6m or so, it is more important to use the stored procedure text that is backed up by this mode often cannot be used directly because in each stored procedure After the body is automatically added, it is sometimes placed directly in the end of the process body, no space, so that it takes a lot of time to fix the process when using this text. Therefore, finding a backup speed that is fast backup, and the content is still necessary to replace the tool.
In actual work, the author uses the following two methods to back up the stored procedure. Method 1: Realize the quick export of the entire replacement process by writing an embedded SQL C program. Method 2: Quickly export the specified stored procedure via DEFNCOPY. The details are now described in detail:
Method 1: Fast export by CPRE
After the following procedure generates an executable by Sybase's CPRE precompilation processing, the quick export of the stored procedure can be implemented. When the database is idle, you can export all stored procedures within a minute (about 6m or so, and Sybase Central It is necessary to take a hourglass).
The program is running as follows (setup executable):
Sybase> EXPROTPROC file name
After execution, all stored processes are stored in this file, and the contents of the file also save the writing format of the stored procedure, and add a separate line Go after each stored procedure. In order to make the program flexible, enter the username, password, online string, and database name, according to prompt before the official guide.
#include
#include
EXEC SQL include Sqlca;
Void SQL_ERROR ();
Main (Argc, Argv)
Int argc;
Char ** argv;
{
File * fp;
CHAR Usename [11], usepasswd [11], dbstring [16];
Char * fpass;
Long Oldid;
EXEC SQL Begin Declare Section;
CHAR TEXTLINE [256];
Long id;
Char dbname [20];
Exec SQL End Declare Section;
/ * Insufficient input parameters, give us a prompt * /
IF (argc! = 2) {
Printf ("USAGE AS% S
exit (0);
}
IF ((fp = fopen (argv [1], "w ")) == null) {
Printf ("Open File% S Error / N", Argv [1]);
exit (0);
}
/ * Enter a link user name * /
PRINTF ("" "" ");
Gets (useename);
/ * Enter the user password, when the screen does not display the input content * /
FPASS = GetPass ("Please Input Passwd:");
STRCPY (UsePasswd, FPASS);
/ * Enter a link string * / printf ("" please input data string: ");
Gets (dbstring);
/ * Enter the library name of the stored procedure * /
Printf ("Please Input Database Name:");
Gets (dbname);
EXEC SQL WHENEVER SQLEC CALL SQL_ERROR ();
IF (connectDb (useename, usepasswd, dbstring)! = 0) {/ * connection database * /
Printf ("Can't Connect Database / N);
exit (0);
}
Else
Printf ("Connect Database OK! / N");
Printf ("Begin to Export Procedure Please Wait ... / N");
EXEC SQL USE: DBNAME;
EXEC SQL COMMIT;
/ * Declare the cursor, find out what all stored procedures in the library * /
EXEC SQL DECLARE PRO_CUR CURSOR for
Select ID, Text from SysComments
WHERE ID in (Select ID from sysobjects
WHERE TYPE = 'P')
And TextType = 0
And text is not null
Order By ID, Number, Colid2, Colid
EXEC SQL OPEN PRO_CUR;
EXEC SQL FETCH PRO_CUR INTO: ID,: TEXTLINE
Oldid = -9999L;
While (! sqlca.sqlcode) {
IF (ID! = Oldid && OldID! = - 9999L) {
/ * When a stored procedure ends, add a new line Go * / after its process.
FPRINTF (FP, "/ NGO / N / N");
}
OldId = ID;
FPRINTF (FP, "% s", TextLine;
EXEC SQL FETCH PRO_CUR INTO: ID,: TEXTLINE
}
EXEC SQL Close Pro_CUR;
FPRINTF (FP, "/ NGO / N / N");
Fclose (fp);
Printf ("End Export Procedure! / N");
/ * Disconnected in the database * /
DisconnectDB ();
Printf ("Disconnect Database Success! / N");
}
Void SQL_ERROR ()
{
Exec SQL WHENEVER SQLERROR Continue;
Printf ("Error./N");
Printf ("% s / n", sqlca.sqlerrm.sqlerrmc);
exit (0);
}
/ * Connection database function * /
Int ConnectDB (Username, Password, dbstring)
EXEC SQL Begin Declare Section;
CS_CHAR * Username, * Password, * dbstring;
Exec SQL End Declare Section;
{
EXEC SQL SET CHAINED OFF;
Exec SQL Connect: UserName Identified by: Password Using: dbstring;
Return (Sqlca.sqlcode);
}
/ * Disconnect Database Function * /
INT disconnectdb ()
{
EXEC SQL Disconnect All; Return 0;
}
The program is passed on IBM AIX4.3, HP-UNIX 11.0, and TRU64 Unix 5.0 platform.
Method 2: Fast export by DEFNCOPY
When you need to back up separately for some stored procedures, DEFNCOPY is often used by a spelling. Specific steps are as follows:
1. Write this CRTPROCOUT.SQL file according to the stored procedure to be backed up, and assume that all stored procedures starting with PR_JF, as follows:
SELECT "DEFNCOPY -U Username" Name ". SQL library name" Name from sysobjects where type = 'p' and name Like "Pr_JF%"
2. Use the above documents to generate a process script
ISQL -U User Name -P Password -S Online Strings -i Crtprocout.sql -o Procout
3. Change the permissions of file procout
CHMOD X PROCOUT
4. Execute the script export process, each process is called: process name suffix ".sql"
./procout
Summary: The above two methods can be replaced by modifying WHERE conditions in the SQL statement. But the author believes that the former is suitable for backup the process of the entire library, while the latter is suitable for backup of the specified processes. Because the former will generate to all process scripts to a file, it is suitable for management and backups for multiple processes; and a process script generates a file that is suitable for the management and backup of a single process. The above two methods can also be exported by simply modification. .a1 {font-size: 1}
