About ODBC Data Source Connection Text
In the "Connection Principle of External Database", we said that ODBC provides support for multiple databases, such as DBASE, Access, MS SQL Server and Oracle, that is, the connection code provided in ODBC data sources, we A connection to a variety of databases can be implemented. Take the Access database as an example, the format of the ODBC data source connection text is: "driver = {database driver}; DBQ = database file;"
In the above connection text, if the database follower is in the same directory, or use the variable defaultdir to specify the directory where the database is located, the database file can do not have the full path name, as follows:
"ODBC; DBQ = msaccess.mdb; driver = {microsoft access driver (* .mdb)};"
As follows:
"ODBC; DBQ = msaccess.mdb; defaultdir = d: / downloads / e21; driver = {Microsoft Access Driver (* .mdb)};"
If the database follower is not in the same directory, or if you do not use the variable defaultdir to specify the directory where the database is located, the database file needs to use the full path name, as follows:
"ODBC; DBQ = E: / Quake III Arena / msaccess.mdb; driver = {Microsoft Access Driver (* .mdb)};
What is mentioned above is the format of connecting the Access database, then what is the ODBC data source connection text that connects other databases? Connecting different types of databases To use different corresponding drivers, don't forget it! Different drivers are of course their parameter combinations, each different driver has its specific parameters:
(1) MS Access ODBC DSNless:
☆, parameter: driver setting value: {Microsoft Access driver (* .mdb)} ☆, parameter: DBQ set value: actual path file name ☆, example sentence: "driver = {Microsoft Access Driver (* .mdb)}; DBQ = C: /SOMEPath/dbname.mdb; uid = admin; pwd = pass; "
(2) DBASE ODBC DSNLESS connection:
☆, parameter: driver setting value: {microsoft dbase driver (* .dbf)} ☆, parameter: DBQ set value: actual path file name ☆, example sentence: "driver = {microsoft dbase driver (* .dbf)}; driverID = 277; DBQ = c: /somePath/dbname.dbf; "
(3), Oracle ODBC DSNless connection:
☆, parameter: driver setting value: {microsoft odbc for oracle} ☆, parameter: DBQ set value: actual path file name ☆, example sentence: "driver = {microsoft odbc for oracle}; server = oracleserver.world; uid = admin; PWD = pass; "
⑷, MS SQL Server DSNless connection:
☆, parameter: driver setting value: {SQL Server}; ☆, parameter: server setting value: server name ☆, parameter: Database set value: data table name ☆, parameter: UID setting value: User name ☆, parameter: PWD settings Value: Password ☆, example sentence: "driver = {SQL Server}; server = servername; database = dbname; uid = sa; pwd = pass;" ⑸, MS Text Driver DSNless connection:
☆, parameter: driver setting value: {microsoft text driver (* .txt; * .csv)} ☆, parameter: DBQ set value: actual path file name ☆, example sentence: "driver = {Microsoft Text driver) * .csv)}; dbq = c: / somepath /; extensions = ASC, CSV, Tab, TXT; PERSIST security info = false; "
⑹, Visual FoxPro DSNless connection:
☆, parameter: driver setting value: {Microsoft Visual FoxPro Driver} ☆, parameter: SourceType Settings: DBC ☆, parameter: SOURCEDB Settings Value: Actual Path File Name ☆, Example: "Driver = {Microsoft Visual FoxPro Driver}; SourceType = DBC; SOURCEDB = C: /SOMEPath/dbname.dbc; Exclusive = NO; "
⑺, mysql dsnless connection:
☆, parameter: driver setting value: {mysql} ☆, parameter: Database set value: data table name ☆, parameter: UID set value: user name ☆, parameter: PWD set value: password ☆, example sentence: "driver = {mysql }; Database = YourDatabase; uid = username; pwd = password; option = 16386
*********************************************************** ******************
SQL language profile
In the previous lecture, we introduced the way to connect the external database. How do you read, display, add, add, update, queries, etc. after connectivity? These operations need to call SQL instructions through an external database. (1) What is SQL language
SQL (Structure Query Languge, Structured Query) is a database-specific computer language, whether it is Oracle, MS SQL, Access, MySQL, MS SQL, Access, MySQL, or other company's database, or no matter whether the database is built on large hosts or personal computers. Use the SQL language to access and modify the contents of the database. Although there will be less database software to increase some exclusive SQL syntax, in general, they still follow the SQL standards developed by ASNI (National Standards Association). Since the SQL language has easy learning and reading, SQL gradually be adopted by various database vendors, and become a common standard query language. As long as you learn SQL, you can operate a variety of databases such as Visual FoxPro, Access, DBASE, and more. In summary, the SQL language is a database query language that can be used by various databases. The SQL language does not only have the function of querying the database, but also perform various operations such as selecting, adding, adding, updating, and jumps. (Ii) SQL language
The SQL language is composed of commands (functions), clauses, operators, plus total functions, and wildcard characters, and is described below:
1. Command SQL commands can be divided into data definition languages and data operation languages, and data-defined languages can be used to establish new databases, data sheets, fields, and indexes. This tutorial is not introduced; another data operation language, can be used to establish Query table, sort, filter data, modification, adding or other action. Data Definition Language Commands Commonly have selected, add, delete, and modify these four types:
(1), command: Select: Select: Use to find out the conditions record (2), command: insert meaning: Insert Description: Used to add a record or merge two data tables (3), command: update meaning in Chinese Update Description: Used to correct the critic record ⑷, command: delete meaning: deletion Description: Used to delete a log record
2, the clause clause is the object (ie, parameters) used to set the command, and the claps used by SQL are as follows:
(1), clause: from Chinese Meaning: Data Sheet Description: Used to specify data table (2), clause: WHER meaning: WHER meaning: Condition Description: For Settings Conditions (3) Set the group ⑷, order by meaning: Sort Description: Used to set the order and field of the output
3. Operators in the operator clause parameters make clauses constitute different syntax formats, such as "Field 1 = ''" "," field 1> '' 100 '' ", etc.. The operator is also divided into logical operators and comparison operators. ◇ The logical operator is as follows:
(1), operators: And meaning: logic and (2), operator: or meaning: or explanation: logic Non-(3), operator: Not meaning in Chinese meaning: Reverse instructions: logic is not logical
◇ The comparison operator is as follows:
(1): Operator:
5, wildcard
(1), wildcard:% meaning: any length string (including 0) (2), wildcard: _ Significance: Underline representing any character (3), wildcard: [] meaning: China bracket indicates a range of characters within a range
In the next lecture, how the SQL language is combined with command (functions), clause, operator, and total function, and the like.
*********************************************************** **********************
Application of embedded SQL
The SQL statement can be performed separately in the database system itself, but if used in the program prepared in other programming tools, it is generally not performed separately, and the SQL statement is used to use in advanced languages (such as easy language), through the advanced language Commands and methods are called, and SQL is called embedded SQL. The program that calls the SQL statement is called the host program. In the easy language, the SQL statement is generally processed directly as the unique parameter of the host program. Embedded SQL has some provisions on the use of the current version of the Easy Languages as follows: (1) Sproducts to SQL statements and host languages in the program. It is good to distinguish between Easy Languages, because the SQL statement is in English, and easy-to-language is Chinese, but it may still be confusing in actual applications, so easy language to convert SQL statements into text types to call, ie embedded The SQL statement is used to indicate two sides.
(2), allow the SQL statement to use the host program variable, but when using the field name of the host program with the field name of the table in the external database, the difference is as follows: 1, in the easy language to convert the variable type into text type Variables can be used by SQL text, such as an integer type variable called "Number 1" in the example below, which is inserted into SQL text:
External Database 1. Query ("Select * from chj where" combination box 1. Content "=" to text (number 1))
2, including the SQL text of the field name plus double quotes, the variable name cannot be in double quotes, such as the above. (3) To distinguish the field name with the field value, the difference method is as follows: 1, for the text type field, on both sides of its field value indicate its text value, representing the syntax: field name = 'Text Value'. as follows:
External Database 1. Inquiry ("SELECT * from chj where name = 'mountain king' '")
The field value in the following "Find the edit box. Content" is text type, the embedded SQL statement is as follows:
External Database 1. Query ("Select * from chj where name ==" "''" lookup box. Content "'')
2, for the field of the digital type, indicate its field value in the SQL statement, and no symbols on both sides indicate that the syntax is: field name = digital value. The following two cases:
External Database 1. Query ("Select * from chj where" combination box 1. Content "=" lookup edit box. Content)
External Database 1. Inquiry ("SELECT * from Chj Where Learn = 17")
3, for the "#" of the "#" mark on both sides of the date time, representing the syntax: field name = # time value #. The following two cases:
External Database 1. Query ("SELECT * from chj Where enrollment time betWeen # 2001-01-01 # and # 2002-01-01 #")
External Database 1. Query ("Select * from chj where" combination box 1. Content "=" "#" lookup edit box. Content "#")
4, you can also cause the field name (especially Chinese name) in the SQL statement, such as: [field name].
⑷, SQL statement should be entered with a half angle input method, otherwise it may be wrong.
So how to call the SQL statement in the easy language? Generally, calls in the external database object (control) method, summarizing the following: (1) Method for querying external databases. The query for the external database is to display, query, filter, sort, and record sets of the database only by the recordset, in the premise of the external database. The method of all query classes originated from the statement below, and other query classes are calls to this statement (the statement as a unique parameter), which is as follows:
External database. Query (query class SQL statement)
It can also be expressly expressed:
External database. Inquiry ("Select ... from ... [where] ... [group by] ... [Order By] ...")
The method is to query the currently opened database, the result of the return is called "Record Set Handle" (ie the logo of the recordset). Note When you no longer use this recordset, you must use the "Close Recorder" to turn it off, if it fails, return 0. In easy-to-language, the above statement is equivalent to the recording set handle as a parameter for other query class statements. In order to make this parameter can be applied in all subroutines, we generally set it to an integer type global variable, and set its value as follows:
Record the set footer = external database. Query (query class SQL statement)
Since the language is to convert the SQL statement into text type, the embedded SQL statement must have a double quotation number. Example: Record the set sessile = external database 1. Query ("Select * from cha)) The name of a table in the database is, as if you want to get a recordset, you should assign a value below:
Record the set footer = external database 1. Query ("SELECT * from chj order by language DESC")
Other query classes in external database controls are now listed below:
1, external database. Resemble (Record Set Handle): External Database. Renew (external database. Query (Query class SQL statement))
Example: External database 1. Re-query (external database 1. Query ("SELECT * from cha))))
2, external database. Before the record (record set sessile), the external database. Before the record (external database. Query (query class SQL statement))
Example: External database 1. Before the record (record set sessions)
3, external database. After the tail record (Record Set Handle) 4, external database. Go to the first record (recordset) 5, external database. To the tail record (record set handle) 6, external database. Go to the previous record (record set handle) 7, external database. Go to the latter record (Record Set Handle) 8, external database. Read (record the set handle, field name or location)
Example: Language editing box. Content = to text (external database 1. Read (Record Set Handle, "Language"))
(2) The method of editing the external database. The so-called editing of external databases is to change the exterior database itself, including adding, updating, deleting, etc., editing the database does not have to pass records. All non-inquiry SQL statements are embedded below this statement:
External database. Execution (non-query class SQL statement)
1, add a record, its syntax is as follows:
External database. Execute ("INSERT INTO Table Name (Field 1, Field 2 ...) VALUES (field value 1, field value 2 ...)")
Example: External Database 1. Execution ("INSERT INTO CHJ" "(student number, name, language, mathematics, English)" "value" "(" school number editing box. Content ", '" Name editing box. Content "''" language editing box. Content "'" Mathematical Edit box. Content "'" English Edit box. Content "' ') ")
2, update records, the syntax is as follows:
External database. Execution ("Update Table Name SET Field 1 = Field Value 1, Field 2 = Field Value 2 ... Where Condition")
Example: External Database 1. Execute ("Update Chj Set Learn =" "'' Learn Editing Box. Content " '', " " Name = " " '" Name Edit box. Content "''," "Chinese =" "'" Chinese editing box. Content "' '," "Mathematics =" "' '" Mathematical Edit box. Content "' '," "English =" "''" English edit box. Content "'" "Where Name =" "'" Name 1 "" "and language =" language 1 "And Mathematics =" Mathematics 1 "And English =" English 1 "And Learn =" Learn No. 1) 3, delete record, its syntax is as follows:
External database. Execution ("delete * from table name Where condition")
Example: External database. Execute ("External Database 1. Execution (" Delete * from chj " " Where Name = " " '" Name 1 " " " and language = " language 1 " and mathematics = " Mathematics 1 "and English =" English 1 "and learn =" school number 1) ")
