Talking about Creating Database in PB Dalian University of Technology Li Hong 01-6-25 05:41:21
When we hand over the database client application developed by PowerBuilder (hereinafter referred to as PB), we also need to make a series of configurations on the database system of the user's server-side, such as establishing business databases and buildings, views, primary keys, Index and other data objects. Only the data structure on the database server is properly configured, the application can run normally. It is usually done by experienced database administrators, tools, or through other auxiliary tools, using database systems to complete the configuration of the database end. This article uses Microsoft's SQL Server 2000 as an example, introduces a program that generates a business database and various business data objects with PB. Users can establish data structures in the database end as long as they run this program.
Program function and structure
The main function of the program is to establish a business database required for the client application and establish a data object on a newly established business database.
The input information of the program is obtained by editing the text box by 4 separate:
● SLE_DATABASE: The newly established database name;
● SLE_DATAFILE: The data file used in the new database;
● SLE_LOGFILE: New database used by the database;
● SLE_Script: Create the script file used by the data object in the database.
The two buttons of the completion program feature are: cb_create is used to establish a database; CB_TABLE is used to create data objects such as tables, primary keys in the newly created database.
Output results in two output windows: DW_DATABASE is used to display newly built database names and data files; DW_OBJECTS is used to display data objects established by the CB_TABLE button.
Implementation
Considering a specific application, some systematic physical information such as the data space required to determine the data space required by the database has been determined in the system analysis phase, and does not wait until the program runtime is determined by the user, so this information does not require user input. The name of the database is generally fixed, the user entered database name (SLE_DATABASE), which can be used to check if there is a database that is already the same name in the database system. The physical location and name of the data file and the logical file are input by the user according to the disk space on the server (SLE_DATAFILE, SLE_LOGFILE), the initial value is given in the program, and the specified file location is the SQL Server usual data file storage location.
1. Create a script file for data objects (SLE_Script)
This file is a normal text file type, which consists of the data definition language (DDL) of the data object such as the establishment table, the primary key, and its syntax is in line with the rules of the database system. For example, the script Test.sql content is a table with the primary key:
CREATE TABLE XZ_TJ_ALL
(gxjg char (4) Not null,
Nian Smallint Not NULL,
Yue Tinyint NOT NULL,
BZ1 Tinyint NOT NULL,
BZ2 Tinyint Not NULL,
A1 INTEGER,
A2 INTEGER,
A3 INTEGER,
Primary Key (Gxjg, Nian, Yue, BZ1, BZ2)
)
CREATE TABLE XZ_TJ_4_1
(gxjg char (4) Not null,
Nian Smallint Not NULL,
Yue Tinyint NOT NULL,
BZ1 Tinyint NOT NULL,
BZ2 Tinyint Not NULL,
A1 INTEGER,
Primary Key (Gxjg, Nian, Yue, BZ1, BZ2)
)
2. The OPEN event application of the window is only used to one window (w_create_database), in its OPEN event, created two global transaction objects, and using one of the transaction objects (TR_SQL) and the System Database (Master). At this point, there is no creation of the business database, and the business database does not exist, so it cannot be connected to the new service database here. W_create_database's OPEN event script is as follows:
// Create a transaction object connected to the Master system database
Tr_SQL = CREATE TRANSTEN
Tr_sql.dbms = "MSS Microsoft SQL Server 6.x"
Tr_sql.database = "master"
// Database System Administrator
Tr_sql.logpass = "sa"
//server name
Tr_sql.servername = "station4"
// System Administrator Password
Tr_sql.logid = "sa"
Tr_sql.autocommit = false
Tr_sql.dbparm = "" "
/ / Connect to the Master system database
CONNECT Using TR_SQL;
IF TR_SQL.SQLCODE <> 0 THEN
MessageBox ("Database Connection Errors", "You cannot connect to the SQL Server database master. Please confirm if the SQL Server database is started. ~ N ~ r" "error message:" Tr_sql.sqlerrtext)
Return
END IF
// Retrieve database information from system table sysdatabases
DW_DATABASE.SETTRANSOBJECT (TR_SQL);
// Create a transaction object connected to the new business database
TR_NewBase = CREATE TRANSACTION
Tr_newbase.dbms = "MSS Microsoft SQL Server 6.x"
Tr_newbase.logpass = "sa"
Tr_newbase.servername = "station4"
Tr_newbase.logid = "sa"
Tr_NewBase.Autocommit = false
Tr_newbase.dbparm = ""
3. Click Events of the CB_CREATE button
Depending on the business needs, the data files created here are 100MB, the maximum value is 200MB, and the increment is 20MB. The button CB_CREATE consulates a dynamic SQL statement based on the input parameter, creates a database. CB_CREATE's CLICKED event script is as follows:
//Database name
String ls_database
// Data file path and file name
String ls_datafile
// Log file path and file name
String ls_logfile
// Create a SQL statement of a database
String ls_mysql
// Logic data file name
String ls_logicalfilename
// Logic log file name
String Ls_LogicalLogname
SetPointer (HOURGLASS!)
/ / Get the database name entered by the user
LS_DATABASE = TRIM (SLE_DATABASE.TEXT)
// acquire data file location and name
LS_DataFile = Trim (SLE_DATAFILE.TEXT)
/ / Get the location and name of the log file
LS_Logfile = Trim (SLE_LOGFILE.TEXT)
LS_LogicalFileName = LS_DATABASE "Arch1" LS_LogicalLogName = LS_DATABASE "Archlog1"
Tr_sql.autocommit = true
// Establish a statement of the database
LS_MYSQL = "CREATE DATABASE" LS_DATABASE "ON" &
"(Name =" LS_LogicalFileName "," &
"Filename = '" ls_datafile "," & "
"SIZE = 100MB," &
"Maxsize = 200," &
"Filegrowth = 20)" &
"LOG ON" &
"(Name =" LS_LogicalLogname "," &
"Filename = '" ls_logfile "," & "
"SIZE = 100MB," &
"Maxsize = 200," &
"FileGrowth = 20)"
EXECUTE IMMEDIATE: LS_MYSQL Using TR_SQL;
Tr_sql.autocommit = false
// Retrieve the database that has just been established
DW_DATABASE.RETRIEVE (LS_DATABASE)
CB_TABLE.Enabled = TRUE
SetPointer (arrow!)
4. Click Events of the CB_TABLE button
Buttons CB_TABLE reads content from the specified script file (SLE_Script.text), constructs dynamic SQL statements, and creates data objects. For the simplified procedure, this article has not been processed for the script file greater than 32765 bytes, and the reader can use technical self-handage.
The CB_TABLE ClickE event script is as follows:
// Time before creating data objects
DateTime LDT_CREATE
//Database name
String ls_database
// Create a SQL statement for data objects
String ls_sql
// Store the script file name
String ls_filename
/ / Open the file number of the file
INT Li_Fileno
// File length, read file bytes
Long ll_filelength, ll_number
/ / Get a newly built database name
LS_DATABASE = TRIM (SLE_DATABASE.TEXT)
If MessageBox ("Please confirm", "will you generate a table structure in the" LS_DATABASE "database?" Question!, Yesno!, 2) = 2 THEN
Return
END IF
SetPointer (HOURGLASS!)
/ / Set the database value for the transaction object of the business database
Tr_newbase.database = ls_database
CONNECT Using TR_NEWBASE;
IF TR_NewBase.Sqlcode <> 0 THEN
MessageBox ("Database Connection Error", "You cannot connect to SQL Server Database:" LS_Database ". ~ N ~ r" "error message:" TR_NewBase.sqlerrtext) Return
END IF
/ / Acquire the time of establishing the data object and extract the established data object from the system table sysObjects
Select distinct getdate ()
INTO: LDT_CREATE
From sysobjects
USING TR_NEWBASE;
// This data window will display a new transaction object
DW_Objects.settransObject (TR_NewBase)
// Name of the script file from the input
Ls_filename = trim (Sle_Script.text)
LL_FileLength = filelength (ls_filename)
/ / Do not process the file greater than 32765 bytes
IF ll_filelength> 32765 THEN
Messagebox ("", "Script file is too big")
Disconnect use TR_Newbase;
Return
Elseif Ll_FileLength> 0 THEN
/ / Read file content, perform dynamic SQL statements
Li_fileno = fileopen (ls_filename, streammode!)
LL_NUMBER = fileRead (li_fileno, ls_sql)
IF ll_number> 0 and ll_number <= 32765 THEN
TR_NewBase.Autocommit = TRUE
Execute Immediate: LS_SQL
Usingtr_newbase;
Tr_NewBase.Autocommit = false
END IF
FILECLOSE (Li_Fileno)
ELSE // Do not read file information correctly
Sle_script.setfocus ()
SLE_Script.selecttext (1, le (Sle_Script.text))
MessageBox ("Open the script file error", "Please enter the correct table structure script file name")
Disconnect use TR_Newbase;
Return
END IF
// Retrieve the data object that has just been established
DW_OBJECTS.RETRIEVE (LDT_CREATE)
Disconnect use TR_Newbase;
SetPointer (arrow!)
