Feeds:
Posts
Comments

Archive for the ‘Database’ Category

Aku mempunyai MAMP Pro sudah sudah jalan stabil di Mac Tiger, termasuk MySQL, PhpMyAdmin dan Apache. Aku mencoba memasang Navicat Premium sebagai MySQL GUI. Tetapi muncul masalah, setiap kali mencoba melakukan koneksi ke MySQL di localhost selalu gagal (Can’t connect bla bla…) tetapi kalau digunakan untuk melakukan koneksi ke MySQL ke Server Linux berhasil.

Setelah browsing dan mencoba-coba, solusi yang bisa dilakukan dengan cara menggunakan socket file untuk koneksi ke localhost.

Setting di Navicatnya sebagai berikut :

Masuk ke Connection Properties-nya Navicat, klik Tab Advanced, pilih option “Use socket file for localhost connection” dan arahkan Socket File Path = /Applications/MAMP/tmp/mysql/mysql.sock

Dan tentunya lengkapi juga setting User-Password di General Tab.

Read Full Post »

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Backup\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Read Full Post »

Internal SQL Functions

1. Functions Introduction

This section gives examples of all functions that are available in the Mckoi database software. Functions may be used within all expressions except aggregate functions that may only be used within the SELECT .... FROM clause.


2. Mathematical Functions

Follows are all the mathematical functions available in Mckoi Database. Note that some of these functions may lose precision because the number could be cast to a ‘double’. You can assume that all mathematical functions have at worst the precision of a ‘double’.

ABS(number)

Returns the absolute value of a number.

Examples:

     SELECT ABS(-0.94)
==>  0.94
     SELECT ABS(9 - 200)
==>  191

SIGN(number)

Returns 1 if the number is positive, -1 if the number is negative and 0 if the number is zero.

Examples:

     SELECT SIGN(40)
==>  1
     SELECT SIGN(-40)
==>  -1
     SELECT SIGN(40 - 40)
==>  0

MOD(number1, number2)

Returns the modulo of number1 and number2 (equivalent to {number1 % number2} in Java).

Examples:

     SELECT MOD(15, 5)
==>  0
     SELECT MOD(33, 10)
==>  3
     SELECT ROUND(552 / 10), MOD(552, 10)
==>  55, 2

ROUND(number, decimal_places)
ROUND(number)

Rounds the number to ‘n’ decimal places. When no ‘decimal_places’ argument is provided the number is rounded to the nearest whole number.

This will round up if the fraction to the right is >= .5 otherwise it rounds down. This uses the {BigDecimal.setScale(decimal_places, BigDecimal.ROUND_HALF_UP)} method for rounding.

Examples:

     SELECT ROUND((943 * 13) / 99, 3)
==>  123.828
     SELECT ROUND((943 * 13) / 99, 2)
==>  123.83
     SELECT ROUND((943 * 13) / 99)
==>  124

POW(number1, number2)

Raises number1 to the power of number2.

Examples:

     SELECT POW(9, 6)
==>  531441
     SELECT POW(2, 32)
==>  4294967296
     SELECT POW(2, 64)
==>  18446744073709551616
     SELECT POW(2, -3)
==>  0.125

SQRT(number)

Finds the square root of the number argument.

Examples:

     SELECT SQRT(65536)
==>  256
     SELECT SQRT(-1)
==>  NULL

LEAST(val1, val2, …)

This function accepts any number of arguments and returns the value that represents the least value of the set.

Examples:

     SELECT LEAST(4)
==>  4
     SELECT LEAST(90, 9.125, 3, 75)
==>  3
     SELECT LEAST('H', 'Z', 'B')
==>  B
     SELECT LEAST(10 / 3, 10 * 3,
                  POW(10, 3), MOD(10, 3))
==>  1

GREATEST(val1, val2, …)

This function accepts any number of arguments and returns the value that represents the greatest value of the set.

Examples:

     SELECT GREATEST(4)
==>  4
     SELECT GREATEST(90, 9.125, 3, 75)
==>  90
     SELECT GREATEST('H', 'Z', 'B')
==>  Z
     SELECT GREATEST(10 / 3, 10 * 3,
                     POW(10, 3), MOD(10, 3))
==>  1000


3. String Functions

String functions in Mckoi Database mostly map to equivalent functions found within java.lang.String.

LOWER(str)

Returns a lower case version of the string literal argument.

Examples:

     SELECT LOWER('THis is sOME TEXT')
==>  this is some text

UPPER(str)

Returns an upper case version of the string literal argument.

Examples:

     SELECT UPPER('THis is sOME TEXT')
==>  THIS IS SOME TEXT

CONCAT(str1, str2, …)

Returns the concatenation of the string arguments. This function can take any number of arguments.

Examples:

     SELECT CONCAT('This i', 's some text', '.')
==>  This is some text.
     SELECT CONCAT('-', 0.95)
==>  -0.95

LENGTH(str)

Returns the number of characters in the string argument.

NOTE: This may additionally be used on BLOB data to return the count of bytes in the BLOB.

Examples:

     SELECT LENGTH('This is some text')
==>  17
     SELECT LENGTH(0.544)
==>  5
     SELECT LENGTH('    Test')
==>  8

TRIM( [ [ LEADING | TRAILING | BOTH ] [ characters ] FROM ] str )
LTRIM(str)
RTRIM(str)

Trims characters from a string argument. The LTRIM and RTRIM form trim whitespace from the left and right of the string respectively.

Examples:

     SELECT TRIM(TRAILING 'a' FROM 'aaabcdaaa')
==>  aaabcd
     SELECT TRIM(LEADING 'a' FROM 'aaabcdaaa')
==>  bcdaaa
     SELECT TRIM('ab' FROM 'ababzzzzab')
==>  zzzz
     SELECT TRIM('  a string message ')
==>  a string message

SUBSTRING(str, start_index)
SUBSTRING(str, start_index, length)

Returns a substring of a string. The SUBSTRING function complies with the SQL specification. The start_index parameter is a value between 1 and the length of the string where 1 includes the first character, 2 includes the second character, etc. The length parameter represents the size of the substring.

Examples:

     SELECT SUBSTRING('Tobias Downer', 8)
==>  Downer
     SELECT SUBSTRING('abcd', 1, 2)
==>  ab
     SELECT SUBSTRING('abcd', 3, 4)
==>  cd
     SELECT SUBSTRING('abcd', 3, 5000)
==>  cd
     SELECT SUBSTRING('abcd', 0, 5000)
==>  abcd
     SELECT SUBSTRING('abcd', 1, 0)
==>  (string of 0 length)


4. Aggregate Functions

Aggregate functions can only operate within a group of a SELECT statement. They are used to compute statistics over a set of records.

COUNT(*)
COUNT(DISTINCT expression_list)
COUNT(column_name)
COUNT(expression)

The * version of this function returns the total number of rows in the group. If a column name is specified it returns the number of non-null values in the group. The ‘expression’ form of this function evaluates the expression for each row in the group and counts it only if it evaluates to NULL. COUNT(DISTINCT ... ) counts all distinct values of the expression list over the group.

Examples:

     SELECT COUNT(*)
       FROM Orders
     SELECT COUNT(*)
       FROM Orders
   GROUP BY division
     SELECT COUNT(id)
       FROM Orders
   GROUP BY division
     SELECT last_name, COUNT(DISTINCT last_name)
       FROM Customers
   GROUP BY age

SUM(column_name)
SUM(expression)

Calculates the sum of all values in a column/expression over a group. The expression form of this function is evaluated for each row in the group.

Examples:

     SELECT SUM(value) FROM Orders
     SELECT SUM(quantity * value)
       FROM Orders
     SELECT SUM(quantity * value) * 0.75
       FROM Orders
   GROUP BY division

AVG(column_name)
AVG(expression)

Calculates the average of the column/expression over the group. The expression form of this function is evaluated for each row in the group.

Examples:

     SELECT AVG(value) FROM Orders
     SELECT AVG(quantity * value)
       FROM Orders
     SELECT AVG(quantity * value) * 0.75
       FROM Orders
   GROUP BY division

MIN(column_name)
MIN(expression)

Finds the minimum value of a column/expression over a group.

Examples:

     SELECT MIN(value) FROM Orders
     SELECT MIN(quantity * value)
       FROM Orders
     SELECT MIN(quantity * value) * 0.75
       FROM Orders
   GROUP BY division

MAX(column_name)
MAX(expression)

Finds the maximum value of a column/expression over a group.

Examples:

     SELECT MAX(value) FROM Orders
     SELECT MAX(quantity * value)
       FROM Orders
     SELECT MAX(quantity * value) * 0.75
       FROM Orders
   GROUP BY division


5. Security Functions

Functions that provide security information about the session performing the query.

USER()

Returns the current user.

PRIVGROUPS()

Returns a comma deliminated list of priv groups the user belongs to. A user may belong to any number of groups which dictate the tables a user may access.


6. Branch Functions

IF(condition_expr, true_expr, false_expr)

If the first expression (condition_expr) evaluates to true this function returns the result of ‘true_expr’ otherwise returns the result of ‘false_exp’.

Examples:

     SELECT IF(true, 5, 8)
==>  5
     SELECT IF(false, 5, 8)
==>  8
     SELECT IF(NULL, 5, 8)
==>  NULL
     SELECT IF(true, IF(false, 1, 2), 3)
==>  2
     SELECT IF(col1 = 0, 'N/A', col1) FROM MyTable

COALESCE(expr1, expr2, expr3, ….)

Returns the first non null value from the parameters or null if the entire list contains null values.

Examples:

     SELECT COALESCE(NULL, 'a')
==>  a
     SELECT COALESCE(NULL, NULL, NULL)
==>  NULL
     SELECT COALESCE(col1, 'N/A') FROM MyTable


7. Date/Time Functions

DATEOB(date_string)

Parses a string to a Date object that can be used on queries against TIMESTAMP / DATE / TIME columns. DATEOB with no arguments returns the current time of the machine running the database.

Since version 0.92 this function has been deprecated. Use the standard DATE, TIME and TIMESTAMP literals specified in SQL-92 instead.

Examples:

     SELECT DATEOB()
==>  Wed Aug 09 11:49:31 EDT 2000
     SELECT DATEOB('Aug 1, 2000')
==>  Tue Aug 01 00:00:00 EDT 2000
     SELECT number FROM Orders
      WHERE date_made >= DATEOB('Jan 1, 2000')


8. Misc Functions

UNIQUEKEY(table_name)

Returns a unique key for the given table name. This is an atomic operation that is guaranteed to return a unique number each call. It should be used to generate unique identification numbers for records. It is similar to the AUTO_INCREMENT feature of other database systems.

Examples:

     SELECT UNIQUEKEY('Orders')
     INSERT INTO Orders
        ( id, number, division, date_made, quantity,
          value )
       VALUES
        ( UNIQUEKEY('Orders'), CONCAT('Order-', id),
          'Bio Engineering', DATEOB(), 25, 1900.00 )

TONUMBER(expression)

Attempts to cast the expression to a number. If the expression is a boolean then this function will return 1 for true or 0 for false. If the expression is a String then it attempts to parse the string into a number. If the expression is a Date then it returns the date as the number of milliseconds since Jan 1st, 1970.

Examples:

     SELECT TONUMBER(DATEOB('Aug 1, 2000'))
==>  965102400000

Read Full Post »

Microsoft Access 2000 and 2002 are similar. Smaller capacities for the older versions of Access are noted in the tables.

These figures are taken from Microsoft sources and published here for the convienience of my students and clients.

I no longer build full applications in Access, but it is excellent for prototypes, and under some circumstances it works well as a front end with SQL Server as a back end. In this situation the application should be an Access project.

Database specifications
Attribute Maximum
Database (.mdb) file size 2 gigabyte. However, because your database can include linked tables in other files, its total size is limited only by available storage capacity.
Number of objects in a database 32,768
Modules (including forms and reports with the HasModule property set to True) 1,000
Number of characters in an object name 64
Number of characters in a password 14
Number of characters in a user name or group name 20
Number of concurrent users 255
Table specifications
Attribute Maximum
Number of characters in a table name 64
Number of characters in a field name 64
Number of fields in a table 255
Number of open tables 2048. The actual number may be less because of tables opened internally by Microsoft Access.
Table size 2 gigabytes minus the space needed for the system objects; 1 for Access 2000 and 2002.
Number of characters in a Text field 255
Number of characters in a Memo field 65,535 when entering data through the user interface; 1 gigabyte when entering data programmatically.
Size of an OLE Object field 1 gigabyte
Number of indexes in a table 32
Number of fields in an index 10
Number of characters in a validation message 255
Number of characters in a validation rule 2048
Number of characters in a table or field description 255
Number of characters in a record (excluding Memo and OLE Object fields) 4,000; 2,000 for Access 2000 and 2002
Number of characters in a field property setting 255
Query specifications
Attribute Maximum
Number of enforced relationships 32 per table minus the number of indexes that are on the table for fields or combinations of fields that are not involved in relationships
Number of tables in a query 32
Number of fields in a recordset 255
Recordset size 1 gigabyte
Sort limit 255 characters in one or more fields
Number of levels of nested queries 50
Number of characters in a cell in the query design grid 1,024
Number of characters for a parameter in a parameter query 255
Number of ANDs in a WHERE or HAVING clause 99; 40 for Access 2000 and 2002
Number of characters in a SQL statement approximately 64,000
Form and report specifications
Attribute Maximum
Number of characters in a label 2,048
Number of characters in a text box 65,535
Form or report width 22 in. (55.87 cm)
Section height 22 in. (55.87 cm)
Height of all sections plus section headers (in Design view) 200 in. (508 cm)
Number of levels of nested forms or reports 7; 3 for Access 2000 and 2002
Number of fields or expressions you can sort or group on in a report 10
Number of headers and footers in a report 1 report header/footer; 1 page header/footer; 10 group headers/footers
Number of printed pages in a report 65,536
Number of controls and sections you can add over the lifetime of the form or report 754
Number of characters in an SQL statement that serves as the Recordsource or Rowsource property of a form, report, or control (both .mdb and .adp) 32,750
Macro specifications
Attribute Maximum
Number of actions in a macro 999
Number of characters in a condition 255
Number of characters in a comment 255
Number of characters in an action argument 255

Variable and Table Data Types

Data type Prefix Example Note
String (Text) str strCity Text to 255 characters
Date/Time dtm dtmCreated Date and Time
Boolean bln blnIsNotNull Yes/No or True/False, two values
Byte byt bytMonth One Byte, values from 0 to +255
Integer int intCount Two Bytes; values from -32,768 to +32,767; No fractions
Long
(long integer)
lng lngDistance Four Bytes, values from
-2,147,483,648 to 2,147,483,647
Single sng sngPopulation Four Bytes single precision, floating point
Currency cur curTraded Fifteen digits to the left, four to the right. Fixed decimal place.
Double dbl dblClientID Eight Bytes
Decimal dec decMicroseconds Twelve Bytes
Object obj objConnection
Variant vnt vntUserInput It can store numeric, string, date/time, Null, or Empty data
Error err errBadEmailAddress

source : http://www.databasezone.com/techdocs/acclimit.html

Read Full Post »