Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Fall 2006 Columbia University ... different database management systems. ... This section deals with some exemplary codes that can illustrates usages of ...
Typology: Study notes
1 / 12
This page cannot be seen from the preview
Don't miss anything!
Character constants are one or more members of the source character set, the character set in which a program is written, surrounded by single quotation marks ('). They are used to represent characters in the execution character set, the character set on the machine where the program executes. Reserved Characters: 1 New line \n 2 Horizontal tab \t 3 Vertical tab \v 4 BEL \ 5 Question mark ? 6 Single quotation mark ' 7 Double quotation mark " 8 Null character \ 0
Real constants specify values that may have a fractional part. These values contain decimal points (.) and can contain exponents.
A string literal consists of zero or more characters from the source character set surrounded by double quotation marks ("). A string literal represents a sequence of characters that, taken together, form a null- terminated string.
Embedded SQL blocks begin with the character sequence ‘<$’ and end with the character sequence ‘$>’. The embedded code will be inserted into the generated query set. The compiler may inspect the SQL code and issue errors or warnings, but it is not required to do so. SQL block: <$ SQL statement $>
Like the “varchar” type of SQL, this creates a character string of variable length, the maximum length in bytes restricted by “size”. Unlike the char type, there is no blank padding when fewer than “size” characters are supplied. However, inserting a value greater than “size” characters is not permitted. Their declaration syntax is: varchar(size) identifier_name ;
It maps on to the SQL type “INTEGER” and represents a 32-bit (signed) integer value. The value will range between integers - 2147483648 and 2147483647. Their declaration syntax is: int identifier_name;
This type corresponds to the SQL type “REAL”. In EasyQL, we define the float type to contain 15 bits of mantissa. Their declaration syntax is: float identifier_name;
The date type in EasyQL represents a date comprising a date, month and year as well as for time. This will correspond to “DATE” in SQL. Their declaration syntax is: date identifier_name ;
The table type in EasyQL is a collection of the attributes of a table. The attributes may be of different types and attributes within a table must have unique names, in order to distinguish between them. Their declaration syntax is: table identifier; Table variable can be initialized by retrieving existing table in the connection or by creating new one. An attribute name of a table tableA could be accessed thus : tableA[id]
The connection type specifies a connection to a particular database. A connection instance can be created by passing the right connection parameters when creating a connection type. Their declaration syntax is: connection identifier; Assignment to connection can be made by calling connect () built-in function Ex. connection conn = connect (localhost, 1521, testDB, mysql, msyqlpasswd); Since the connection is to a particular database, an attribute id of table table1 in a databsase connected to conn can be assigned to a variable attr1 by: Ex. attr1 = conn:table1[id]
3.2.1.1. Postfix expressions These are two postfix operators for incrementing and decrementing objects for number types. These will be placed after the operand and change the value of operand. The type won’t be changed after operation. Number type of expressions can be operand for these expressions. These are right-to-left operation. postfix-expression: number-expression ++ | number-expression -- 3.2.1.2. Unary operations + , - , ++, --, These are unary operators that will be placed in front of operand and change the value of operand. The type won’t be changed after operation. Number type of expressions can be operand for these expressions. These are left-to-right operations. prefix-expression: + number-expression | - number-expression prefix-expression: ++ number-expression prefix-expression: -- number-expression prefix-expression: !number-expression 3.2.1.3. Binary operations + - * / % Theses are binary operations and which means addition, subtraction, multiplication, division and modular operation respectively. Number type of expression can be operands for these expressions and these will return the same type expressions. These are left-to-right operations. binary-expression: number-expression * number-expression binary-expression: number-expression / number-expression binary-expression: number-expression % number-expression binary-expression: number-expression + number-expression binary-expression: number-expression - number-expression 3.2.1.4. Comparison operators == != >= > < <= , like These operators will compare operands and evaluate relationship between operands. These will return 0 if the relationship in the expression doesn’t hold, 1 otherwise. Operand for this operation can be number type expression, and date type expressions. These operations are for left-to-right evaluation. compare-expression: compare-expression == compare-expression compare-expression: compare-expression != compare-expression compare-expression: compare-expression > compare-expression compare-expression: compare-expression >= compare-expression compare-expression: compare-expression < compare-expression compare-expression: compare-expression <= compare-expression
3.2.1.5. Logical operators && || These operators will yield 1 (in case of TRUE) and 0 (in case of FALSE) otherwise. These operations are for left-to-right evaluation. This will return 1 if both operands are non-zero, 0 otherwise. logical-expression: logical-expression && logical-expression This will return 1 if any of operands is non-zero, 0 otherwise. logical-expression: logical-expression || logical-expression 3.2.1.6. Assignment expression The language provides one assignment operator as bellows. This will be used to assign a value of right expression to the modifiable identifiers of the left. assignment-operation: identifier-expression = expression
In this section, we will cover operations between database objects. We will mostly deal with the operations of table data types and a couple of operators for connection data types will be mentioned briefly. 3.2.2.1. Sub-Table Operators [ ], () Programmers can retrieve data from a table type variable with these operators. These operators are to specify appropriate attributes and conditions. The output of sub-table operation is originally meant to be another type of table object. But in case of having just one element as a result of query, this can be treated as basic data types such as numbers, characters and date data types. sub-table-expression: table-expression [attr-expression-list] sub-table-expression: table-expression ( cond-expression-list) attr-expression-list(opt): attr-expression, attr-expression-list(opt) cond-expression-list: cond-expression, cond-expression-list Ex. tableA[ssn, name].display() This example operation will retrieve sub-table of ssn attribute and name attribute from tableA. display () function will print the records in sub-table. Ex. tableA(age>20).display(); This example operation will retrieve sub-table of tableA which satisfies the condition that is specified in the parenthesis. display () function will print the records in sub-table Ex. tableAname.display(); Programmer can specify attributes and conditions at the same time. Ex. table tableB; tableB =tableA.name.display(); Result of functions can be assigned to another table type variable, instead of displaying output to standard output.
3.2.2.6. Drop Table Expression This operation will cancel definition of table type object and drop the table schema in the table space. drop-expression: table-name-expression.drop() Ex. tableA.drop(); This will drop the existing table from the table space 3.2.2.7. Metadata Expression This operation .desc() and .list() can work on table or connection type and will retrieve the schema of the table and tables and connection related information (such as user name, address of database, database instance name …) that belongs to the connection respectively. Output of operations can be stored as table data-type or printed to the user screen by calling a built-in function .display (). meta-expression: table_expression.desc (); | connection_expression.desc(); Ex. table tableB tableA.desc(); tableB.display(); The next operation .list() can be applied only to connection data type to list table names which belongs to the connection. meta-expression:connection_expression.list(); Ex. connection conn = connect (localhost,1521,testDB, mysql,mysql123); conn.list().display();
The “::” operation will retrieve a table data type from a connection. connection-expression: connection-expressio::.table-name Ex. connection A = connect (localhost,1541,tablespaceA, mysql,passwd) table tableA = A::ProjectMemberTable; The .store operation will store a table data type into a connection. connection-expression: connection-expression.store(table-name); Ex. connection A = connect (localhost,1541,tablespaceA, mysql,passwd) connection B = connect (remotehost,1541,tablespaceB,mysql,passwd) table tableA = A::ProjectMemberTable; B.store(A);
Most statements are expression statements, which have the form expression ; Usually expression statements are assignments or function calls.
So that several statements can be used where one is expected, the compound statement is provided compoundstatement: { statementlist } statementlist: statement statement statementlist
The two forms of the conditional statement are if ( expression ) statement if ( expression ) statement else statement In both cases the expression is evaluated and if it is nonzero, the first sub-statement is executed. In the second case the second sub-statement is executed if the expression is 0. As usual the ‘‘else’’ ambiguity is resolved by connecting an else with the last encountered else-less if.
The while statement has the form while ( expression ) statement The sub-statement is executed repeatedly so long as the value of the expression remains nonzero. The test takes place before each execution of the statement.
This is a predefined function that is used to display the results of a query on the screen. database-expressions.display()
This is a predefined function that creates the connection to the database. connecton-type connection (ipaddress , port, instance_name, db_name, user, password);
This program is to merge a table from one database to the table of the other. connection A = connect(localhost, 1541, tablespaceA, root, passwd); connection B = connect(remotehost, 1541, tablespaceA, root, passwd); table emp1 = A::employee; table emp2 = B::employee; emp2.insert (emp1);
