The practice of database development

Data | Development practice of database Database
Ma Lei, Zhang Wanli
A set of desktop systems originally developed in Access, Web Access using ASP programming, data Maintenance system using C++builder programming. Because access is a shared database of desktops, there are some problems with commonality and network development. Therefore, you need to migrate the system to a server-type database. Enable the system to maintain data through the desktop software, but also a large number of users through the Web page to see the data (results). System initial software is: Windows98 second Edition, ACCESS97, c++builder4.0, c++builder5.0, MDAC (Microsoft Data Access Component 2.6 Chinese). First, porting to Oracle's attempt to install the Oracle 7.3 Desktop version on the machine, because the use is not skilled, so choose the default installation, in addition to the choice of language Simplified Chinese, other choose the default, all the way down, no problem, reboot the system. Run NET Easy Config, configure the database alias you want, and then use SQL NET connection test, user name input system, password input manager, connect the server note, do not enter the server's address, but enter the alias just created, go in. Ok! At this point, start testing ODBC and establish an ODBC data source. At this point, ODBC has two drivers for Oracle, one is Microsoft ODBC for Oracle and the other is Oracle 7.3, the company's own development drive. But how to choose the driver is not good, not the prompt Oracle driver is not installed, that is, a DLL file could not be found. If you say no, you can't do it. Many tests are invalid .... Check the Autoexec.bat file, which has the Oracle path. Then, where will the problem be? In a skeptical attitude, in the Msdos way to enter a path command, strange, there is no Oracle path! Open Autoexec.bat file a ponder, the path is not too long? Comment out all other directories in the path and reboot the system, Hey! The data source can be established, and all two drivers can work. Originally because the system installed the two versions of C++builder, and the software's directory is particularly deep, causing the PATH environment variable overflow, and in the Windows environment when the startup prompts we do not see, so did not find this problem. Now you know, readjust the Autoexec.bat file and keep a c++builder path. You'll have to remember that when you install C++builder, it's best to specify a directory yourself, which shortens the path. It's OK to say yes, no, it's OK. After the OraclE's test, found that when you migrate an Access database directly to an Oracle database, there is always a problem with the migration of the text field (Text,memo), and the system is too complex to use and install, and the machine configuration requirements are too high, and the system needs more users to install, and the user level is uneven, So decided to switch to now in the free software world more popular free database system MySQL. Second, the experience of porting to MySQL 1. The installation of the system is relatively simple, if you think you do not have the ability to install the software, you can also download a software called Phptriad for Windows from the network, the software installed without any options, automatically installed MySQL. (In fact, the software also installed the Apache server, PHP language interpreter, which is exactly in line with the future of ASP's Web page into PHP; In addition, the software also installed a Web database maintenance tool phpMyAdmin, phpMyAdmin is extremely convenient for people who are not familiar with SQL language to manage databases. ) 2. Then install MYODBC (that is, ODBC for MySQL, my version 2.5), note that when downloading the software, to distinguish between the 95 version or NT version. The installation is also very simple, all the way OK. OK, set up the data source work, fill in the IP address, the database name fills in Mymember (at this time the system provides the database), carries on the test, does not? Instead, use the system to automatically install the Sample-mysql data source connection, on the chain. Carefully check the configuration, no errors, why the system to do their own data source can be used, I do not do it? The establishment of their own deleted, the Sample-mysql renamed as Work, and then connect, hey, no! Haha, originally on the name, in the back of the work plus-mysql, and then connected, OK, the original MYODBC requirements of its data source name must end with MySQL! The data source is resolved, and the original Access database is migrated below. Porting is easier, open an Access database, select the first table, select Save As/Export from the File menu, select Save As an external file or database, select "ODBC Database" in the "Save type" Drop-down menu in the pop-up dialog box, and eject an "export" dialog box, if you intend to rename the datasheet, you can now enter a new name, and then select OK, in the pop-up "Select Data Source" dialog box machine data source (machine), find the Work-mysql data source that you set up, click OK. The Access database is returned, and if the datasheet is large, the status bar will have an export progress, otherwise the export is complete. After successful export, the exported database is checked by phpMyAdmin, and the structure and content are normal. Need to pay attention toYes, if you use the ID (AutoIncrement) field provided by Access in the datasheet, you need to manually modify the properties of the corresponding field in the target database (which is easy to implement in phpMyAdmin, find the field after the point "change" in the field's property last "extra" Drop-down menu, select Auto_increment, and then save. phpMyAdmin's interface is in English? In fact, as long as in the installation of phpMyAdmin directory apache\htdocs\phpmyadmin find config.inc.php files, search require, the quotation marks in the "english.inc.php" to "Chinese_" Gb.inc.php ", then to call the Web page of the phpMyAdmin, Xi hee, is not become Chinese? In fact, chinese_gb.inc.php is a filename, in the directory, you can also turn the interface into BIG5 code it. 3. Finally finished the transplant work, the following began to use C++builder programming. Use ADO to access the database. The steps are as follows: Select adotable in the ADO Control tab, place a ADOTable1 on the main window, and then click the "..." icon in the Left Object Viewer (objects Inspector), and select Use Connection String ", Dot" build ", in the" Provider "tab, select" Microsoft OLE DB Provider for ODBC Drivers ", point" next>> "button, in the" specify The source of data "Choose Use data Source name", from the dropdown menu, select the DataSource work-mysql, point "Test Connection", show the connection succeeded, OK, OK, return, in the Object Viewer table Name property Point Drop-down menu, select the data table you want. Then select DataSource from the Data Access Control tab, place a DataSource1 on the main window, pull down the dataset attribute point in the Object Viewer, select ADOTable1, the layout of the interface output below, and choose Data The dbedit in the Controls Control tab is placed as needed and the DataSource property is set to DataSource1, and the DataField property is set to the desired field name. So place a few fields, and then select "Dbnavigator" from the "Data Controls" control label on the window. Then put ADOTable1 's aCtive property to True, Yi, how some field display (MEMO), first regardless of him, run the program, enter some content, click Submit, why not? Some fields are shortened, try several times, found that the new input content can not exceed the original length, then the original is empty field will never be able to enter the content. What's going on? This usage is definitely fine, as it used to be on Access databases. Then, is the MySQL ODBC problem, exit C++builder, open the data source options carefully analysis, which the first vaaaa very much like. Check it, OK, rerun C++builder, run the program, everything OK. The original MySQL such free software, its ODBC options are many, is to adapt to all aspects of the need, unlike Microsoft or other databases, the default value can be used. This option may be to optimize some network applications, and the default value of the database field is always assumed to be the smallest state, resulting in this problem. This may also be a small drawback of free software. Iii. ASP access to MySQL database The original home page used a large number of ASP access to the database, where the typical use is: Dim Lk_connset lk_conn=server.createobject ("ADODB. CONNECTION ") Lk_conn.open" Onduty_mysql "," Root "," "" the connection to establish the database Dim Rs_newsset rs_news = Server.CreateObject (" Adodb.recordset ") sql =" SELECT * from Newspic "Rs_news.open sql,lk_conn,1,1 ' above establish access to table ' below display content for simple expression, no formatting, quotation marks for field name =rs_ NEWS ("Extract") =rs_news ("Pic") This piece of code is not a problem, to achieve a smooth transition, but there are individual places, there are write operations, the data can not read normally. The code is as follows: Dim Rs_countset Rs_count = Server.CreateObject ("Adodb.recordset") Rs_count.open "SELECT * from COUNT", Lk_conn, 3,3 ' Access counter Dim zcountzcount=rs_count ("Count") Zcount = Zcount + 1rs_count ("Count"). Value=zcountrs_count. Updaters_count. Close began to suspect that a paragraph behind theWrite operation, so it does not execute correctly, but after careful comparison and testing, it is found that the problem is not written on. This section of the code is the main use of ASP access to the Access database directly fill in the database name of the method, instead of using SQL statements, we open the database to the "SELECT * from table name", the program works properly. Conclusion: When programming, we should try to use the method of universality, so it is very important for the universality and portability of the system. Four, the problem of ODBC in C++builder some places to use SQL query, therefore, there is a program to use the Adoquery control, this usage was used on other machines, no problem, but here is not. As follows: Place a adoquery,connectionstring property, TableName property, and the previous Adotable property setting, the active property remains false, Add a DataSource control and several dbtext controls to set the previous. Then add the following code in the Formcreate (form constructor):adoquery1->active=false; Adoquery1->sql->clear (); Adoquery1->sql->add ("SELECT * from Names"); Adoquery1->active=true;while (! adoquery1->eof) {Combobox1->items->add (Adoquery1->fieldbyname ("Name")->asstring); Adoquery1->next ();} The latter section of adoquery1->active=false; Adoquery1->sql->clear (); Adoquery1->sql->add ("SELECT * from Dutys"); Adoquery1->active=true;while (! adoquery1->eof) {Combobox2->items->add (Adoquery1->fieldbyname ("Duty")->asstring); Adoquery1->next ();} Just such a piece of code, on the machine how can not run, the system always prompts "BOF or EOF in a true ...." "The mistake will be to noteAfter the deletion of the following paragraph, the system can be run, but the exit program when there is an illegal error, indicating that it is running with fault. Repeatedly see the obvious error can not find the program, the program to the other machine to pass, the main difference of two machines is the version of ODBC is different. So want to reduce ODBC to 2.5 English version, find MDAC2.5 version installed a few times, had to manually deleted, in the Windows directory to search out all the files prefixed to ODBC, and then delete program Files\Common files in the system directory, Reinstall MDAC (Microsoft Data Access Component 2.5 English), and then run the program again, everything is OK. Conclusion: Do not know is the MDAC2.6 version of the problem, or because it is the Chinese version of the problem, in short, resulting in system compatibility is not very good, it is recommended that you should be in the programming should also try to use the familiar environment, rather than always try too new stuff. Of course, if it is my code has a problem, but also hope that the master can come out to guide. The above is our database system in the process of porting the development of the problem and the real record of the solution, although the problem is very small, but let us in the development process has gone a lot of detours, so it is recorded for your reference, I hope the development of friends to help. The problem is also that friends will be able to point out better solutions.