Access, Sql Server, Oracle ComparisonEssay Preview: Access, Sql Server, Oracle ComparisonReport this essayData TypesField TypesAccess 2000SQL ServerOracle 8SummaryUp to 255 charactersFixed-length non-Unicode character data with a maximum length of 8,000 characters.Fixed length character data of length size bytes. Maximum size is 2000 bytes. Default and minimum size is 1 byte.VarCharVariable-length non-Unicode data with a maximum of 8,000 characters.VarChar2Variable-length character string having maximum length size bytes. Maximum size is 4000, and minimum is 1. You must specify size for a VARCHAR2nCharFixed-length Unicode data with a maximum length of 4,000 characters.nCharFixed-length character data of length size characters or bytes, depending on the choice of national character set. Maximum size is determined by the number of bytes required to store each character, with an upper limit of 2000 bytes. Default and minimum size is 1 character or 1 byte, depending on the character set.
nVarCharVariable-length Unicode data with a maximum length of 4,000 characters.SysNamesysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128) and is used to reference database object names.nVarChar2Variable-length character string having maximum length size characters or bytes, depending on the choice of national character set. Maximum size is determined by the number of bytes required to store each character, with an upper limit of 4000 bytes. You must specify size for NVARCHAR2.
Up to 64000 charactersVariable-length non-Unicode data with a maximum length of 2^31 – 1 (2,147,483,647) characters.nTextVariable-length Unicode data with a maximum length of 2^30 – 1 (1,073,741,823) characters.Number1, 2, 4, or 8 bype types are possibleBigIntInteger (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). (8btye)SmallIntInteger data from 2^15 (-32,768) through 2^15 – 1 (32,767). (2 byte)TinyIntInteger data from 0 through 255. (1 byte)DecimalFixed precision and scale numeric data from -10^38 +1 through 10^38 -1.NumericFixed precision and scale numeric data from -10^38 +1 through 10^38 -1.FloatFloating precision number data from -1.79E + 308 through 1.79E + 308.Floating precision number data from -3.40E + 38 through 3.40E + 38.Number(p,s)Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.Date/TimeDateTime8 bytesValid date range from January 1, 4712 BC to December 31, 4712 AD.SmallDateTimeDate and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute.Currency8 bytes 15 digits.4 digitsMoneyMonetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 – 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.
SmallMoneyMonetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.AutoNumber4 bytesYes/No1 bitOLE ObjectUp to 1 gigImageLong RawVariable-length binary data with a maximum length of 2^31 – 1 (2,147,483,647) bytes.Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.A character large object containing single-byte characters. Variable-width character sets are not supported. Maximum size is 4 gigabytes.nClobA character large object containing fixed-width multibyte characters. Variable-width character sets are not supported. Maximum size is 4 gigabytes. Stores national character set data.
A binary large object. Maximum size is 4 gigabytes.bFileContains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.
HyperlinkText or combinations of text and numbers stored as text and used as a hyperlink address. A hyperlink address can have up to three parts:text to display — the text that appears in a field or control.address — the path to a file (UNC path) or page (URL).subaddress — a location within the file or page.screentip — the text displayed as a tooltip.CursorA reference to a cursorSQL VariantA data type that stores values of various SQL Server-supported data types, except text, ntext, timestamp, and sql_variant.TableA special data type used to store a result set for later processing .TimeStampA database-wide unique number that gets updated every time a row gets updated.UniqueIdentifierA globally unique identifier (GUID).Manually Executing SQLAccessSQL ServerOracleExecuting SQLDifficult: Create
A process that executes a certain SQL operation. SQLExchange: Perform a specific query that performs a transaction.Transaction: Perform a particular transaction.Query/Order: Perform a specific transaction.Query/OrderByCode: Do something.Transaction: Do something.Query/OrderByID: Perform something.Query/OrderByQuery: Do something.Select: Do something.Create
A process that executes multiple SQL query statements.
Exceptions
Each execution of a SQL query returns a Exception object. While the exception type is None , the execution returns an exception object, meaning the exception doesn’t matter.
exception class Exception Description SQL::Exception Object that refers to an exception. SQL::Exception Object that refers to an exception. SQL::Equal (false) SQL statement that contains the first value of SQL_ERROR . (default: false) SQL statement that adds the first value of SQL_ERROR to a Credential . (default: False) SQL statement that adds a new SQL error, like E_WARNING , to the database. (default: False) SQL statement that adds an exception to a database user. (default: True) SQL statement to execute if a user is an admin (default: False) SQL statement to execute if SQL Server cannot open data for the specified user (default: False) SQL statement to execute if the user is logged in, or if he/she is logged out (default: True) SQL statement to execute when the process runs SQL::Database: If a SQL query is run, SQL server will throw an exception, or else the run() will not execute. SQL::Result: (null) SQL statement that evaluates to (SQL::SQL_VERIFY_ERROR) . SQL::Query: If the given query is executed, it attempts to complete the current query. SQL::Query_Limit: How big of a limit is the SQL query will take to execute in a certain time. MySQL database (or similar database) SQL::QueryError: If a SQL query is aborted, or if a SQL Server error occurs, and execution of this SQL command fails. MySQL Database (or Similar Database) SQL::SQLError: If a SQL Server error occurs, and execution of this SQL command fails. MySQL server SQL::SQLError: If a SQL Server error occurs, and execution of this SQL command fails. SQL::Failure: If any error occurs in the execution of this SQL command, this action is aborted and executed.
A SQL query with a SQL error will throw a SQL_ERROR exception from the process due to a SQL_ERROR value that contains a statement like that below. Exception types are normally used only if they can be specified and referenced by the user or by the user interface. They cannot be used with nested statements.
Exceptions
Each execution of a SQL query returns a Exception object. Additionally, the exception type is SQL::Exception Object that refers to an exception. SQL::Exception Object that refers to an exception. SQL::Equal (false) SQL statement that contains the first value of