Database integrated development practice Ma Lei, Zhang Wanli
A desktop system that is originally developed on Access, web page uses ASP programming, and the data maintenance system uses C Builder programming. Since Access is a shared database of a desktop, there are some problems in terms of versatility and network development. Therefore, the system needs to be transplanted to the server type database. Enable the system to maintain data through desktop software, and there is a large number of users to show data (results) through web pages.
System initial software is: Windows98 second edition, Access97, C Builder 4.0, C Builder 5.0, MDAC (Microsoft Data Access Component 2.6 Chinese).
First, transplanted to Oracle attempt
Install Oracle 7.3 desktop version on the machine, because unprinted use, select the default installation, in addition to selecting language simplified Chinese, all choose the default, all the way, no problem, restart the system. Run Net Easy Config, configure the required database alias, then use SQL NET connection test, username Enter system, password input Manager, connect to the server, do not enter the server's address, but enter the alias that just created, go in. OK!
At this time, the ODBC starts to establish an ODBC data source. At this point, there are two other drivers related to Oracle, one is Microsoft Odbc for Oracle, and the other is Oracle 7.3, which is the driver of the company's own development. But how can you choose a driver, not prompting Oracle's driver, that is, a DLL file cannot be found. It's really can't say it, it will not work.
Multiple tests were invalid. . . .
Check the autoexec.bat file, where there is Oracle path. At the end, where is the problem? In this way, I entered a path command, strange, actually there is no Oracle path in the MSDOS mode. Open the autoexec.bat file, is it too long? Comment all the other directories in the path, restart the system, hehe! The data source can be created, and the two drivers can work. It turned out that the two versions of C Builder were installed in the system, and the directory of the software was particularly deep, causing the spillation of the PATH environment variable, and the prompt when started in the Windows environment, so no this problem was found. . Now I know, re-adjust the autoexec.bat file, keep a C Builder path. Before you remember to install C Builder, you will best specify a directory yourself, which can shorten the path. This is, it is, it is not possible.
After the test of Oracle, it is found that the access, Memo is directly migrated to the Oracle database, and the system is used, the system is installed, and the requirements for machine configuration are also too high. And the system needs to be installed more, and the user's horizontal is uneven, so it is decided to use the free database system MYSQL that is now in the free software world.
Second, transplanted into mysql experience
1. The system is installed and simple, if you think that you have no ability to install the software, you can also download a software called PHPTRIAD for Windows from the web. There is no option when installing, and MySQL is automatically installed. (In fact, the software also has an Apache server, the PHP language interpreter, which is in line with the future to change the ASP page to PHP; in addition, the software also installed a web database maintenance tool phpmyadmin, phpMyadmin For people who are not familiar with SQL language, the management database is extremely convenient.) 2. Install myodbc (ie 即BC for mysql, I am 2.5 version), pay attention to downloading the software, it is clear 95 or NT. The installation is also very simple, all the way OK. Ok, create a data source Work, fill in the IP address, and the database name fills mymember (now the system provided by the system), test, no? Use the Sample-MySQL data source that automatically installed automatically and the chain. Carefully check the configuration, no mistakes, why the system you have done can be used, can I do it? Delete yourself, rename the sample-mysql to work, then connect, oh, no! Haha, I originally on my name, add -mysql after work, then connect, ok, the original Myodbc requires its data source name to end with mysql! Added a data source, starting to transplant the original Access database.
The transplant work is relatively simple, open the Access database, select the first table, select "Save As / Export" from the File menu, select "Save As an External File or Database", select OK, "Save" in the pop-up dialog The "ODBC Database" in the Type "drop-down menu," Export "dialog box, if you plan to change the name to the data table, now you can enter a new name, then select OK," Select Data Source "conversation in the pop-up In the box, click Machine Data Source (machine data source), find your own Work-MySQL data source, press OK. It will return to the Access database, if the data table is large, there will be an exported progress in the status bar, otherwise, the export has been completed.
After the export is successful, check the derived database, structure, and content through phpmyadmin. It should be noted that if you use the Id (Auto Increment) field provided by Access in the data table, you need to manually modify the properties of the corresponding field in the target database (this is easy to implement in phpMyAdmin, find the field after finding this " ", Select Auto_InCrement in the last" additional "drop-down menu of this field, then save .phpMyadmin's interface is English? In fact, you can find config.inc.php in the directory Apache / HTDOCS / PHPMYADMIN installed on the installation of phpMyAdmin File, search for Require, change "English.inc.php" in quotation marks to "Chinese_gb.inc.php", then call phpmyadmin in the web page, 嘻嘻, is it turning into Chinese? In fact, Chinese_Gb.inc.php Is a file name, just in this directory, you can also turn the interface into a BIG5 code).
3. Finally finished the work of the transplant, and started with C Builder below. Use ADO to your database. The steps are as follows: select adotable in the ADO control tab, put an adotable1 on the main window, then click on the "..." icon on the right side of Connectionstring in the Object Inspector, select "Use connection string", click "Build", Select "Microsoft Ole DB Provider for ODBC Drivers" in the "Provider" tab, click "Next >>" button, select "Use Data Source Name" in "Specify The Source Of Data", select the data source from the drop-down menu- MySQL, click "Test Connection", the display connection is successful, determined, OK, returns, the Table Name attribute point drop-down selection in the object viewer, select the required data table. Select DataSource from the Data Access Control Tab, placed a DataSource1 on the main window, pull the selection in the object viewer, select Adotable1, the layout of the interface output, select the DTA Controls control tab as need Place and set the DataSource property to DataSource1, the DataField property set to the desired field name. Place several fields, and finally select "DBNAVIGATOR" from the "Data Controls" control tab. Revete the ADOTABLE1's Active property to True, 咦, how is the field displayed (Memo), no matter where he, run the program, enter some content, click to submit, how is it? Some fields are still shortened, try a few more times, find that the content of the new input cannot exceed the original length, then it is an empty field that will never enter content. How is this going? This usage is certainly no problem, which is used in the Access database. Then, it is the ilSql ODBC problem, exiting C Builder, and the option to open the data source is carefully analyzed, the first VAAAA is very like. Select it, determine, re-run C Builder, then run the program, everything OK. The original mysql is free software, and its ODBC option is available in adapting to the needs of all aspects, and it can be used by Microsoft or other databases. This option may be to optimize applications on some networks, while the default value of the database field is always assumed to be minimized, resulting in this problem. This may also be a shortcomings of free software. Third, the ASP access to the mysql database
A large number of ASP access databases in the original home page, typical usage is:
DIM LK_CONN
Set LK_CONN = Server.createObject ("AdoDb.Connection")
LK_CONN.Open "Ondupy_Mysql", "root", ""
'Establish a database connection
DIM RS_NEWS
SET RS_NEWS = Server.createObject ("AdoDb.Recordset")
SQL = "SELECT * from newspic" rs_news.open sql, lk_conn, 1, 1
'Access to the table above
'Display content below, in order to express, no format, quotation number in the field name
= RS_NEWS ("extract")
= RS_NEWS ("PIC")
This code does not have a problem, achieving a smooth transition; however, there are individual places, there is a write operation, and the data cannot be read normally. The code is as follows:
DIM RS_COUNT
SET RS_COUNT = Server.createObject ("AdoDb.Recordset")
Rs_count.open "Select * from count", LK_CONN, 3, 3
'Access Counter
DIM ZCOUNT
Zcount = RS_COUNT ("count")
Zcount = Zcount 1
Rs_count ("count"). Value = ZCOUNT
RS_COUNT.UPDATE
RS_Count.close
Start suspect that this will be written later, so it cannot be performed correctly, but after careful comparison and testing, it is found that the problem is not written. This code is mainly to fill in the database name, without using the SQL statement, and we don't use the SQL statement.
Conclusion: It should be used to use a wide range of genericity when programming, so that it is still important for the versatility of the system.
Fourth, ODBC problem
Where there is a SQL query in C Builder, there is a program that is used by the AdoQuery control. This usage is used in other machines, there is no problem, but it will not work here. The specific situation is as follows: Place an adoQuery, the connectionString property, the TABLENAME attribute, the ADOTABLE property settings, the Active property keeps false, add a DataSource control and several DBText controls, set the same. Then add the following code in the FormCreate (form constructor):
AdoQuery1-> Active = false;
AdoQuery1-> SQL-> CLEAR ();
AdoQuery1-> SQL-> Add ("SELECT * from names order by id);
AdoQuery1-> Active = true;
While (! adoquery1-> eof)
{
ComboBox1-> items-> add (adoQuery1-> fieldbyname) -> asstring);
Adoquery1-> next ();
}
//
AdoQuery1-> Active = false;
AdoQuery1-> SQL-> CLEAR ();
AdoQuery1-> SQL-> Add ("Select * from Dutys Order By ID);
AdoQuery1-> Active = true;
While (! adoquery1-> eof)
{
ComboBox2-> items-> add (adoQuery1-> fieldbyname) -> asstring); adoQuery1-> next ();
}
In such a code, how can I run in the machine, the system always prompts "There is a true ..." in Bof or EOF ... ", delete the comment, the system can run, but exit the program An illegal error occurs, indicating that the fault is running. Repeatedly viewing obvious errors that can't find the procedure, bring the program to other machines can pass, the most important difference between the two machines is the version of the ODBC.
So I wanted ODBC to 2.5 English. I didn't find MDAC2.5 installed several times. I had to manually delete it. Search all prefixed files in the Windows directory, then delete the System in Program Files / Common Files. Directory, reinstall MDAC (Microsoft Data Access Component 2.5 English), running the program, everything is normal.
Conclusion: I don't know if it is MDAC2.6 version, or because it is a Chinese version of the problem, in short, the compatibility of the system is not very good. It is recommended that everyone should try to use the already familiar environment when programming, not always Try to try too new stuff.
Of course, if it is my code, I have a problem, I hope there is a master to come out.
The above is a real record of our problems and solutions in the transplantation development of a database system. Although the problem is small, let us have gone a lot of detours during the development process, so record it, for your reference, I hope to help from the development of friends. The problems also hope that there are friends to point out better solutions.