odbc| Data | Data source about ODBC data source connection text
In the connection principle of external databases we said that ODBC provides support for a variety of databases, such as dBASE, Access, MS SQL Server, and Oracle, which means that we can connect to multiple databases using the connection code provided in the ODBC data source. For example, to connect to an Access database, the format of the ODBC data source connection text is:
"driver={database driver};D bq= database file;"
In the above connection text, if the database is in the same directory as the program, or if the database is in the same directory as the variable DefaultDir, the database file can be used without the full pathname, as follows:
If the database is not in the same directory as the program, or if the directory of the database is not specified with the variable defaultdir, the database file needs to be in full pathname, as follows:
"Odbc;dbq=e:\quake III arena\msaccess.mdb;driver={microsoft Access Driver (*.mdb)};"
The above refers to the format of connecting to an Access database, so what is the connection text of an ODBC data source connected to another database? Connect different types of databases to use a different corresponding driver, remember it! Different drivers of course their parameter combinations are different, and each of the different drivers has a specific parameter form:
⑴, MS Access ODBC dsnless Connection:
☆, Parameters: Driver Set Value: {Microsoft Access Driver (*.mdb)}
☆, Parameters: DBQ Set Value: actual path file name
☆, Examples:
"Driver={microsoft Access Driver (*.mdb)};D Bq=c:omepath\dbname.mdb; Uid=admin; Pwd=pass; ”
⑵, dBase ODBC dsnless Connection:
☆, Parameters: Driver Set Value: {Microsoft DBASE Driver (*.dbf)}
☆, Parameters: DBQ Set Value: actual path file name
☆, Examples:
"Driver={microsoft DBASE Driver (*.dbf)};D riverid=277;dbq=c:omepath\dbname.dbf;"
⑶, Oracle ODBC dsnless connections:
☆, Parameters: Driver Set Value: {Microsoft ODBC for Oracle}
☆, Parameters: DBQ Set Value: actual path file name
☆, Examples:
"Driver={microsoft ODBC for Oracle}; Server=oracleserver.world; Uid=admin; Pwd=pass; ”
⑷, MS SQL Server dsnless Connection:
☆, Parameters: Driver Set value: {SQL Server};
☆, Parameters: Server Settings value: Name
☆, Parameters: Database Set Value: Data table name
☆, Parameters: Uid Set Value: User name
☆, Parameters: Pwd Set Value: Password
☆, Examples:
"Driver={sql Server}; Server=servername;database=dbname; Uid=sa; Pwd=pass; ”
⑸, MS Text Driver dsnless Connection:
☆, Parameters: Driver Set Value: {Microsoft Text Driver (*.txt; *.csv)}
☆, Parameters: DBQ Set Value: actual path file name
☆, Examples:
"Driver={microsoft Text Driver (*.txt; *.csv)};D bq=c:omepath\; Extensions=asc,csv,tab,txt; Persist Security Info=false; ”
⑹, Visual Foxpro dsnless Connection:
☆, Parameters: Driver Set Value: {Microsoft Visual FoxPro Driver}
☆, Parameters: SourceType Set Value: DBC
☆, Parameters: Sourcedb Set Value: actual path file name
☆, Examples:
"Driver={microsoft Visual FoxPro Driver}; SOURCETYPE=DBC; SOURCEDB=C:OMEPATH\DBNAME.DBC; Exclusive=no; "
⑺, MySQL dsnless Connection:
☆, Parameters: Driver Set value: {MySQL}
☆, Parameters: Database Set Value: Data table name
☆, Parameters: Uid Set value: User name
☆, Parameters: Pwd Set Value: Password
☆, Examples:
"Driver={mysql}; Database=yourdatabase;uid=username;pwd=password;option=16386 "
In the last lecture, we introduced the method of connecting the external database, then how to read the external database after the connection, display, additions and deletions, updates, inquiries and other operations? These operations need to invoke an SQL instruction through an object such as an external database to complete.
㈠, what is SQL language
SQL (Structure query languge) is a database-specific computer language, whether Oracle, MS SQL, Access, MySQL, or another company's database, regardless of whether the database is built on a large host or a personal computer. You can use the SQL language to access and modify the contents of the database. Although database software from different companies may add some proprietary SQL syntax, in general they follow the SQL standard set by the ASNI (American National Standards Association). Because SQL language is easy to learn and read and so on, SQL is gradually adopted by various database vendors, and become a common standard query language. As long as you learn SQL, you can manipulate various databases such as visual Foxpro, Access, dBASE, and so on. In short, the SQL language is a database query language that can be used by various databases.
SQL language not only has the function of querying database, but also can select, delete, update and jump to the database.
㈡, the composition of the SQL language
The SQL language is composed of commands (functions), clauses, operators, plus total functions and wildcard characters, which are described as follows:
1. Order
SQL commands can be divided into data definition language and data manipulation language, data definition language can be used to build new databases, data tables, fields and indexes, etc., this tutorial is not introduced; another for the data manipulation language, can be used to establish query table, sorting, screening data, modification, additions and deletions and other actions. Data definition language commands are frequently selected, added, deleted, and modified in these four ways:
⑴, command: SELECT
Chinese meaning: Choose
Description: Used to find a qualified record
⑵, command: INSERT
Insert in Chinese meaning:
Description: Used to add a record or merge two data tables
⑶, command: UPDATE
Update of the Chinese meaning:
Description: Used to correct a qualified record
⑷, command: DELETE
Delete
Description: Used to delete eligible records
2. Clause
The clause is used to set the object (that is, the argument) to be manipulated by the command, and the following clauses are used for SQL:
⑴, clauses: From
Data sheet (Chinese meaning)
Description: Used to specify data tables
⑵, clause: WHERE
Terms of the Chinese language:
Description: Used to set conditions
⑶, GROUP by
Grouping (merging)
Description: Used to set up groups
⑷, ORDER BY
Chinese meaning: Sort
Description: The order and fields used to set the output
3, operator
The operators in the clause argument make clauses in different syntactic formats, such as field 1 = ' 100 ', ' field 1> ' 100 ', and so on. Operators are also divided into logical operators and comparison operators.
◇ logical operators are as follows:
⑴, operators: and
Chinese meaning: and
Description: Logical AND
⑵, Operator: or
Chinese meaning: or
Description: Logical Non-
⑶, Operator: not
Anti-Chinese meaning:
Description: Logical non-or logical anti-
◇ comparison operators are as follows:
⑴, operator:< Description: less than
⑵, Operator: ≤ description: less than or equal
⑶, Operator: ≥ description: Greater than or equal to
⑷, operator:> Description: greater than
⑸, Operator: = Description: Equals
⑹, operator:<> Description: Not equal to
⑺, Operator: BETWEEN description: Used to set the scope of the Chinese meaning: in ... Between
⑻, Operator: Like description: Used to set the Chinese meaning of the match: as
⑼, Operator: In Description: For set set Chinese meaning: in ... Within
4, add the total function
The addition of the total function is often used in the parameters of the command, such as: "SELECT SUM (Math), AVG (math) from the transcript".
⑴, add Total function: AVG
Mean: Average
Description: Used to find the average of the specified condition
⑵, add Total function: COUNT
Number of Chinese:
Description: Used to find the specified quantity
⑶, add Total function: SUM
Chinese meaning: and
Description: Used to find the specified criteria and
⑷, add Total function: MAX
Maximum value:
Description: Used to find the maximum value for a specified condition
⑸, add the total function: MIN
Minimum value:
Description: Used to find the minimum value for a specified condition
5. Wildcard characters
⑴, wildcard characters:% meaning: strings of any length (including 0)
⑵, wildcard characters: _ Meaning: Underline to indicate any one character
⑶, wildcard: [] meaning: brackets represent a character within a range
The next lecture will explain how the SQL language combines commands (functions), clauses, operators, and the addition of total functions.
SQL statements can be executed separately in the database system itself. However, if used in programs compiled by other programming tools, it is not generally possible to execute them separately, but to embed SQL statements into high-level languages (such as easy language) and invoke them through commands and methods in high-level languages, at which point SQL is called Embedded SQL. The program that invokes the SQL statement is called the host program, and in easy language it is generally handled directly by the SQL statement as the only parameter of the host program. Embedded SQL in the use of a number of provisions, in the language of the current version of the following provisions:
⑴, in the program to distinguish between SQL statements and host language statements. In the easy language to distinguish, because the SQL statement form is in English, but the easy language is Chinese, but in the actual application may still be confusing, so easy language to convert SQL statements to text type can be invoked, that is, embedded SQL statements on both sides of the double quotes to mark.
⑵, allows SQL statements to use variables of the host program, but uses the variables of the host program to distinguish it from the field names of the tables in the external database, using the following distinction method:
①, in an easy language to convert a variable type to a literal variable to be added to the SQL text, for example, in the following example, there is an integer type variable called "Number 1," which is inserted into the SQL text:
External database 1. Query ("SELECT * from CHJ where" + combo box 1. Content + "=" + to text (number 1))
②, the SQL text containing the field name is enclosed in double quotes, and the variable name cannot be enclosed in double quotes, as in the previous example.
⑶, to distinguish a field name from a field value, the method is as follows:
①, for Text type fields, on both sides of the field value to add "'" number to indicate its text value, representing the syntax is: field name = ' literal value '. As follows:
External database 1. Query ("SELECT * from CHJ where name = ' Mountain King '")
Also like the following "Find edit box." The field value in the content is literal, and the embedded SQL statement is as follows:
External database 1. Query ("SELECT * from chj where name = =" + "") + Find edit box. Content + "'")
②, for numeric types of fields, in the SQL statement to represent their field values, both sides are not marked with symbols, representing the syntax is: field name = numeric value. The following two cases:
External database 1. Query ("SELECT * from CHJ where to learn = 17")
③, for Date-time type fields, with the "#" sign on both sides of its field value, the time value is marked with the syntax: field name = #时间值 #. The following two cases:
External database 1. Query ("SELECT * from CHJ where admission time BETWEEN #2001 -01-01# and #2002 -01-01#")
④, you can also enclose the field names in SQL statements (especially Chinese names) in brackets, such as [Field names].
⑷, SQL statements should be entered with half-width input methods, or you may get an error.
So how do you invoke an SQL statement in an easy language? Typically called in the method of an external database object (control), try to summarize as follows:
⑴, a method of querying an external database.
The query to the external database is to display, query, filter, sort, and record the merging of the database only through the recordset, without editing and altering the external database.
All methods of querying a class originate from the following statement, and other query class statements are calls to this statement (the statement as a unique parameter), as follows:
External database. Queries (query class SQL statements)
This method is a data query on the currently open database, and the result returned is called the Recordset handle (that is, the recordset's markup). Note When you no longer use this recordset, you must close it by using close Recordset, and if it fails, return 0. In an easy language, equate the above statement with a recordset handle as an argument to another query class statement. In order for this parameter to be applied in all subroutines, we typically set it to an integer global variable and set its value as follows:
Recordset handle = external database. Queries (query class SQL statements)
Because the easy language to convert SQL statements into text can be invoked, so embedded SQL statements have double quotes on both sides, example:
Recordset handle = external database 1. Query ("SELECT * from Chj")
※ "Chj" is the name of a table in the external database
For example, if you want to get a sorted recordset, you should assign it as follows:
Recordset handle = external database 1. Query ("SELECT * from Chj ORDER by Language DESC")
The other query class methods in the external database control are listed below:
①, external database. Requery (recordset handle) that is:
External database. Requery (external database. Query (query class SQL statement))
②, external database. Before the first record (the Recordset handle) is:
External database. First record (external database. Query (query class SQL statement))
Example: External database 1 before first record (Recordset handle)
③, external database. After tail record (recordset handle)
④, external database. To the first record (Recordset handle)
⑤, external database. To tail record (Recordset handle)
⑥, external database. To previous record (record set handle)
⑦, external database. To the next record (Recordset handle)
⑧, external database. Read (recordset handle, field name, or location)
Example: Chinese edit box. Content = To text (external database 1. Read (recordset handle, "language")
⑵, a method of editing an external database.
The so-called external database editing, is the change of the external database itself, including add, update, delete, and so on, to edit the database does not have to pass the recordset. All non-query class SQL statements are embedded in the following statement to execute:
External database. Execute (non-query class SQL statement)
①, add a record, and its syntax is as follows:
External database. Execute ("INSERT into table name (Field 1, field 2 ...) VALUES (field value 1, field value 2 ...) ”）
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
