Table creation/Postal addresses: Difference between revisions
m (moved Table Creation - Address to Table creation/Postal addresses) |
(added perl) |
||
Line 187: | Line 187: | ||
{Sqlite.close DB} |
{Sqlite.close DB} |
||
end</lang> |
end</lang> |
||
=={{header|Perl}}== |
|||
<lang perl>use DBI; |
|||
my $db = DBI->connect('DBI:mysql:database:server','login','password'); |
|||
my $statment = <<EOF; |
|||
CREATE TABLE `Address` ( |
|||
`addrID` int(11) NOT NULL auto_increment, |
|||
`addrStreet` varchar(50) NOT NULL default '', |
|||
`addrCity` varchar(25) NOT NULL default '', |
|||
`addrState` char(2) NOT NULL default '', |
|||
`addrZIP` char(10) NOT NULL default '', |
|||
PRIMARY KEY (`addrID`) |
|||
); |
|||
EOF |
|||
my $exec = $db->prepare($statment); |
|||
$exec->execute;</lang> |
|||
This example uses mysql, but DBI supports a extensive list of database drivers. See [http://dbi.perl.org/ dbi.perl.org] for more info. |
|||
=={{header|PHP}}+{{header|SQLite}}== |
=={{header|PHP}}+{{header|SQLite}}== |
Revision as of 13:32, 19 February 2010
You are encouraged to solve this task according to the task description, using any language you may know.
In this task, the goal is to create a table to store addresses. You may assume that all the addresses to be stored will be located in the USA.
For non-database languages, show how you would open a connection to a database (your choice of which) and create an address table in it. You should follow the existing models here for how you would structure the table.
ALGOL 68
<lang algol68>MODE ADDRESS = STRUCT( INT page, FLEX[50]CHAR street, FLEX[25]CHAR city, FLEX[2]CHAR state, FLEX[10]CHAR zip ); FORMAT address repr = $"Page: "gl"Street: "gl"City: "gl"State: "gl"Zip: "gll$;
INT errno; FILE sequence; errno := open(sequence, "sequence.txt", stand back channel); SEMA sequence sema := LEVEL 1;
OP NEXTVAL = ([]CHAR table name)INT: (
INT out; # INT table page = 0; # # only one sequence implemented # # DOWN sequence sema; # # NO interprocess concurrency protection # on open error(sequence, (REF FILE f)BOOL: ( reset(sequence); #set(table page,1,1);# put(sequence, 0); try again; FALSE ) ); try again: reset(sequence); #set(table page,1,1);# get(sequence,out); out +:=1; reset(sequence); #set(table page,1,1);# put(sequence,out); # UP sequence sema; # out
);
OP INIT = (REF ADDRESS self)REF ADDRESS: ( page OF self := NEXTVAL "address"; self);
REF ADDRESS john brown = INIT LOC ADDRESS;
john brown := (page OF john brown, "10 Downing Street","London","England","SW1A 2AA");
printf((address repr, john brown));
FILE address table; errno := open(address table,"address.txt",stand back channel);
- set(address table, page OF john brown,1,1); - standard set page not available in a68g #
put bin(address table, john brown); close(address table)</lang> Output:
Page: +1 Street: 10 Downing Strreet City: London State: England Zip: SW1A 2AA
C
<lang c>#include <stdio.h>
- include <stdlib.h>
- include <sqlite3.h>
const char *code = "CREATE TABLE address (\n" " addrID INTEGER PRIMARY KEY AUTOINCREMENT,\n" " addrStreet TEXT NOT NULL,\n" " addrCity TEXT NOT NULL,\n" " addrState TEXT NOT NULL,\n" " addrZIP TEXT NOT NULL)\n" ;
int main() {
sqlite3 *db = NULL; char *errmsg; if ( sqlite3_open("address.db", &db) == SQLITE_OK ) { if ( sqlite3_exec(db, code, NULL, NULL, &errmsg) != SQLITE_OK ) { fprintf(stderr, errmsg); sqlite3_free(errmsg); sqlite3_close(db); exit(EXIT_FAILURE); } sqlite3_close(db); } else { fprintf(stderr, "cannot open db...\n"); sqlite3_close(db); exit(EXIT_FAILURE); } return EXIT_SUCCESS;
}</lang>
DB2 UDB
<lang sql>CREATE TABLE Address ( addrID Integer generated by default as identity, addrStreet Varchar(50) not null, addrCity Varchar(25) not null, addrState Char(2) not null, addrZIP Char(10) not null )</lang>
J
J is a programming language, not a database, but it ships with a database built in the programming language called JDB. Using that, assuming hd is your database, then:
<lang j>
Create__hd 'Address';noun define
addrID autoid; addrStreet varchar addrCity varchar addrState char addrZip char ) </lang>
Of course J can connect external databases too, using e.g. ODBC. See the list of J database topics.
MySQL
<lang mysql>CREATE TABLE `Address` (
`addrID` int(11) NOT NULL auto_increment, `addrStreet` varchar(50) NOT NULL default , `addrCity` varchar(25) NOT NULL default , `addrState` char(2) NOT NULL default , `addrZIP` char(10) NOT NULL default , PRIMARY KEY (`addrID`)
);</lang>
Transact-SQL (MSSQL)
<lang sql>CREATE TABLE #Address (
addrID int NOT NULL Identity(1,1) PRIMARY KEY, addrStreet varchar(50) NOT NULL , addrCity varchar(25) NOT NULL , addrState char(2) NOT NULL , addrZIP char(10) NOT NULL
) drop table #Address</lang>
Oracle
<lang sql>CREATE SEQUENCE seq_address_pk START BY 100 INCREMENT BY 1 / CREATE TABLE address (
addrID NUMBER DEFAULT seq_address_pk.nextval, street VARCHAR2( 50 ) NOT NULL, city VARCHAR2( 25 ) NOT NULL, state VARCHAR2( 2 ) NOT NULL, zip VARCHAR2( 20 ) NOT NULL, CONSTRAINT address_pk1 PRIMARY KEY ( addrID )
) /</lang>
Oz
The SQLite version that comes with Ozsqlite does not understand "AUTOINCREMENT". <lang oz>declare
[Sqlite] = {Module.link ['x-ozlib:/sqlite/Sqlite.ozf']}
DB = {Sqlite.open 'test.db'}
in
try
{Sqlite.exec DB "CREATE TABLE address (" #"addrID INTEGER PRIMARY KEY," #"addrStreet TEXT NOT NULL," #"addrCity TEXT NOT NULL," #"addrState TEXT NOT NULL," #"addrZIP TEXT NOT NULL" #")" _}
catch E then {Inspector.configure widgetShowStrings true} {Inspect E} finally {Sqlite.close DB} end</lang>
Perl
<lang perl>use DBI;
my $db = DBI->connect('DBI:mysql:database:server','login','password');
my $statment = <<EOF; CREATE TABLE `Address` (
`addrID` int(11) NOT NULL auto_increment, `addrStreet` varchar(50) NOT NULL default , `addrCity` varchar(25) NOT NULL default , `addrState` char(2) NOT NULL default , `addrZIP` char(10) NOT NULL default , PRIMARY KEY (`addrID`)
); EOF
my $exec = $db->prepare($statment); $exec->execute;</lang>
This example uses mysql, but DBI supports a extensive list of database drivers. See dbi.perl.org for more info.
PHP+SQLite
not tested <lang php><?php $db = new SQLite3(':memory:'); $db->exec("
CREATE TABLE address ( addrID INTEGER PRIMARY KEY AUTOINCREMENT, addrStreet TEXT NOT NULL, addrCity TEXT NOT NULL, addrState TEXT NOT NULL, addrZIP TEXT NOT NULL )
"); ?></lang>
PostgreSQL
<lang sql>CREATE SEQUENCE address_seq start 100; CREATE TABLE address (
addrID int4 PRIMARY KEY DEFAULT nextval('address_seq'), street varchar(50) not null, city varchar(25) not null, state varchar(2) not null, zip varchar(20) not null
);</lang>
Python+SQLite
<lang python>>>> import sqlite3 >>> conn = sqlite3.connect(':memory:') >>> conn.execute(CREATE TABLE address ( addrID INTEGER PRIMARY KEY AUTOINCREMENT, addrStreet TEXT NOT NULL, addrCity TEXT NOT NULL, addrState TEXT NOT NULL, addrZIP TEXT NOT NULL
))
<sqlite3.Cursor object at 0x013265C0> >>> </lang>
Ruby+SQLite
<lang ruby>require 'sqlite3'
db = SQLite3::Database.new(':memory:') db.execute("
CREATE TABLE address ( addrID INTEGER PRIMARY KEY AUTOINCREMENT, addrStreet TEXT NOT NULL, addrCity TEXT NOT NULL, addrState TEXT NOT NULL, addrZIP TEXT NOT NULL )
")</lang>
SAS
DATA address; LENGTH addrID 8. street 50$ city 25$ state 2$ zip 20$; STOP; RUN;
Tcl+SQLite
<lang tcl>package require sqlite3
sqlite3 db address.db db eval {
CREATE TABLE address (
addrID INTEGER PRIMARY KEY AUTOINCREMENT, addrStreet TEXT NOT NULL, addrCity TEXT NOT NULL, addrState TEXT NOT NULL, addrZIP TEXT NOT NULL
)
}</lang>