Adding records to a database with an ISAPI implementation

This article describes the use of HTML (hypertextmakeuplanguage) to write an interface, Write an ISAPI (INTERNETSERVERAPPLICATIONPROGRAMMINGINTERFACE) Interaction program to implement methods for adding records to a database that was built with Microsoftsqlserver. 1. Preface Microsoftsqlserver is a scalable, high-performance relational database management system (RDBMS) designed for distributed client-server computing environments. Its built-in data replication capabilities, powerful management tools, and open system architecture provide an excellent platform for publishing information. Its built-in data replication capabilities provide a powerful and reliable way to distribute accurate information throughout an organization, and the data in the library can be replicated not only to Microsoftsqlserver databases but also to Oracle, IBMDB2, Sybase, and other databases. Microsoftsqlserver provides users with built-in data replication capabilities-block Copy program (BCP) to migrate data between database management systems and copy data from files to SQL Server. This means that users can use BCP to add the contents of a file to a database. However, BCP is an executable program that must be run at a command prompt (such as DOS), which is a great inconvenience to Windows users, and the bcp command has an intrinsic format with many parameters that makes it difficult for users to remember. We have developed methods to add records to a database using ISAPI implementations. This method uses HTML to write the user input file name interface, using ISAPI as an interactive program. Connects to the SQL Server Library through ODBC (opendatabaseconnectivity) in the ISAPI. The user simply enters the filename in the HTML interface, and the contents of the data in the file are conveniently added to the specified table. This method overcomes the drawbacks of the bcp command. This paper introduces the method of adding records to Traditional Chinese medicine database by using ISAPI, and gives the method of establishing database table, format of data file, ISAPI main program, HTML file format of input data file name interface, etc. 2, the establishment of database tables Structured Query Language SQL (structuralquerylanguage) is a high-level language of relational database systems, which was developed by IBM's Sanjose Research Laboratory in the late 70 and has been adopted by many relational database management systems, And by the United States National Standards Bureau identified as an industry standard relational database query language. It has powerful data maintenance and query function, and it is a kind of data sub language which can work on multi-user system. Its command statement is similar to the English sentence, the user is very convenient to use, easy to understand. The following is an example of a "CMT1" table established with Microsoftsqlserver: 　　 CREATETABLECMT1 ( Zy00varchar (Notnull), Zy01varchar (m) Notnull, Zy02textnull, Zy03varchar (Notnull), Zy04textnull, Zy05varchar (Notnull), Zy06varchar (9) Notnull, Zy07varchar (5) Notnull, Zy08varchar (2) notnull ) 3. Data file format The following is a data file format that adds records to the Chinese Herbal Medicine database table CMT1: ZY00 (Chinese herbal name): {37} ZY01 (Chinese name Hanyu Pinyin (capital)): {Sanqi} ZY02 (Chinese herbal alias): {Panax Ginseng 37, Tianqi, Panlong Seven (Sichuan), Jinbuhuan (Jiangxi)} ZY03 (Chinese Medicine English name): {Sanchi} ZY04 (Chinese herbal English alias): {} ZY05 (Latin name of Chinese herbal Medicine): {Radixnotoginseng} ZY06 (Chinese herbal Medicine unique code): {mmh35002a} ZY07 (Code of Chinese Medicine Dictionary): {00096} ZY08 (Efficacy category): {18} In the above data file, only the characters between the "{" and "}" symbols are added to the corresponding data fields in the CMT1 database table. {"The preceding characters are used to prompt the user who writes the data file, the characters between" {"and"} "can be implemented in the ISAPI program, the prompt character before" {"is ignored, and "{" and "}" characters, and so on. 4. Authoring of ISAPI applications We use SQL statements that can be embedded in an application and embed SQL-related request statements in an ISAPI interaction program. ISAPI interactivity is written with visualc++, and the following is the main program segment to add records to the table CMT1 of the Chinese Medicine database: 　　 Establish a member function Filewrite1entry of Czy class, and realize adding records to the table CMT1 of Chinese Medicine database Voidczy::filewrite1entry (Chttpservercontext*pctxt,lptstrpstrfilename) { Constunsignedrec_num=1; Rec_num (number of records in the data file) constunsignedline=9; Line (number of data fields in the table) constunsignedcolumn=6000; COLUMN (the maximum number of characters in each data field) Charc,data[line][column]; FILE*FP; StartContent (pctxt); HTML Header Format WriteTitle (pctxt); HTML title Cstringstringsql; Cstringstrquery; Cstringstroutput; CSTRINGPSTRZY00,PSTRZY01,PSTRZY02,PSTRZY03,PSTRZY04,PSTRZY05, pstrzy06,pstrzy07,pstrzy08; Open a file named Pstrfilename in "read" mode if (!) ( Fp=fopen (Pstrfilename, "R")) { *pctxt << "<center><fontcolor= ' Red ' >Warning:</font>Cannotopenthisfile.</center>"; Return } Reading data from a file for (intk=0;k<rec_num;k++) { for (inti=0;i<line;i++) { Ignore previously descriptive characters for ' {' do{ C=GETC (FP); }while (c!= ' {'); Read character data between "{" and "}" for (intj=0;j<column;j++) { C=GETC (FP); if (c== '} ') { Data[i][j]= ' "; Break } ElseIf (c== ' \ r ') | | (c== ' \ n ') | | (c== ' {')) { j--; } Else { Data[i][j]=c; } } } } Fclose (FP) Assigns the data read from the file to the corresponding character type variable PSTRZY00=DATA[0]; PSTRZY01=DATA[1]; PSTRZY02=DATA[2]; PSTRZY03=DATA[3]; PSTRZY04=DATA[4]; PSTRZY05=DATA[5]; PSTRZY06=DATA[6]; PSTRZY07=DATA[7]; PSTRZY08=DATA[8]; To create a database object Cdatabasedb; Confirm Data if (!strcmp (pstrzy00,nullstring) | |! strcmp (pstrzy01,nullstring) | | !STRCMP (pstrzy03,nullstring) | |! strcmp (pstrzy05,nullstring) | | !STRCMP (pstrzy06,nullstring) | |! strcmp (pstrzy07,nullstring) | | !STRCMP (pstrzy08,nullstring)) { *pctxt << "<br><center>" << "pleasebecertaintoenteryourzy00,zy01,zy03,zy05,zy06,zy07,zy08.\r\n" << "Thankyou." << "</center>"; Return } formatting conditions for adding records Strquery.format ("zy00= '%-.20s '", pstrZY00); Open the database object and exit if this object does not exist if (!db. Open (ZYDB,//LPSZDSN False,//bexclusive False,//breadonly Connectstring,//lpszconnect FALSE))//busecursorlib { *pctxt<< "Couldnotopenthedatabase." Return } Create a database Table object for the Recoredset class Czycmt1rszy (&AMP;DB); Specify the formatted add-record condition to the Table object Rszy.m_strfilter=strquery; Open the Table object, and if it cannot be opened, catch exception handling Try { if (Rszy.open (Crecordset::d ynaset)) { If you want to add a record that already exists in the database table, give the feedback contained in the Ids_onfile if (!rszy.isbof ()) { Stroutput.format (Ids_onfile,pstrzy00,scriptpath); } Else { Construct Insert Record statement Stringsql.format ("InsertintoCMT1" (Zy00,zy01,zy02,zy03,zy04,zy05,zy06, " "Zy07,zy08" VALUES ('%-.20s ', '%-.50s ', '%s ', '%-.80s ', '%s '), " "'%-.100s ', '%-.9s ', '%-.5s ', '%-.2s '", Pstrzy00,pstrzy01,pstrzy02,pstrzy03,pstrzy04,pstrzy05,pstrzy06,pstrzy07, PSTRZY08); Executes the Insert Record command. If you successfully give the feedback information contained in the Ids_thankyou, Otherwise catch exception handling Try { Db. ExecuteSQL (Stringsql); Stroutput.format (Ids_thankyou,pstrzy00,scriptpath); } catch (Cdbexception*pex) { TCHARSZERROR[1024]; if (Pex->geterrormessage (szerror,sizeof (szerror)) Stroutput.format (Ids_exception,szerror,scriptpath); Else Stroutput.format (Ids_unknown,scriptpath); } } } Else If the database table object cannot be opened, give the feedback contained in the Ids_unknown Stroutput.format (Ids_unknown,scriptpath); } catch (Cdbexception*pex) { TCHARSZERROR[1024]; if (Pex->geterrormessage (szerror,sizeof (szerror)) Stroutput.format (Ids_exception,szerror,scriptpath); Else Stroutput.format (Ids_unknown,scriptpath); } Rszy.close (); Close Table Object Db.　　　　Close (); Close Database Display feedback to the user *pctxt<<stroutput; EndContent (pctxt); HTML footer Format } The compiled ISAPI dynamic Connection Library program places it in the/scripts directory. /scripts is the virtual directory of the interactive program. It can be set up through the WWW Service Manager. When WWW is first installed, the default ISAPI executable virtual directory is/scripts. 5, the input data file name interface compilation An HTML file inserts an HTML identifier in plain text, and the www browser displays the file in the format specified by the identifier in the HTML file by identifying the identifier in the HTML file. The following gives the HTML specific wording of the input data file name interface to add records to the Chinese Medicine database table CMT1, this file is named filewrite1.html. <bodybgcolor= "White" > <center> <formaction= "/scripts/zy.dll?" Filewrite1entry "method=" POST "> <p><inputname= "FileName" size=40></p> <inputtype= "Submit" value= "submitted" > <inputtype= "Reset" value= "resetting" > </form> </center> </body> Type http://webserver/in the URL of Netscape or InternetExplorer Filewrite1.html, where webserver is the server name or the domain name of the server, the screen will display the input data file name interface, according to the interface prompts in the table column input data file name, submitted feedback information in the client's display 6. Conclusion The way we've developed the use of ISAPI to add records to a database does not require any action at the DOS prompt. In addition, our data file can contain prompt characters that are added to the user who wrote the data file, the prompt character is not added to the database, and the Microsoftsqlserver bcp command cannot choose to ignore any characters in the data file, only copy all the data from the data file into the database. At the same time, the latter needs to operate at a DOS prompt. Using ISAPI as the interactive program can not only realize the network database query, but also can operate the records in the database (add, modify, delete, etc.). With the popularization of windowsnt usage, the application scope of ISAPI will be more and more extensive.