Table creation/Postal addresses

From Rosetta Code
Task
Table creation/Postal addresses
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. As such, you will need (in addition to a field holding a unique identifier) a field holding the street address, a field holding the city, a field holding the state code, and a field holding the zipcode. Choose appropriate types for each field.

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

Works with: ALGOL 68 version Standard - no extensions to language used
Works with: ALGOL 68G version Any - tested with release mk15-0.8b.fc9.i386

<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);

  1. 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

Apache Derby

<lang SQL>create table Address ( 

 addrID     integer primary key generated by default as identity,
 addrStreet varchar(50) not null,
 addrCity   varchar(50) not null,
 addrState  char(2)     not null,
 addrZip    char(10)    not null

); </lang> Interactive session: 

$ ij
ij version 10.8
ij> connect 'jdbc:derby:postal_addresses;create=true';
ij> create table Address (
>   addrID integer primary key generated by default as identity,
>   addrStreet varchar(50) not null,
>   addrCity   varchar(50) not null,
>   addrState  char(2)     not null,
>   addrZip    char(10)    not null
> );
0 rows inserted/updated/deleted
ij> show connections;
CONNECTION0* - 	jdbc:derby:postal_addresses
* = current connection
ij> describe address;
COLUMN_NAME         |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
ADDRID              |INTEGER  |0   |10  |10    |GENERATED&|NULL      |NO      
ADDRSTREET          |VARCHAR  |NULL|NULL|50    |NULL      |100       |NO      
ADDRCITY            |VARCHAR  |NULL|NULL|50    |NULL      |100       |NO      
ADDRSTATE           |CHAR     |NULL|NULL|2     |NULL      |4         |NO      
ADDRZIP             |CHAR     |NULL|NULL|10    |NULL      |20        |NO      

5 rows selected
ij> exit;
$ dblook -d jdbc:derby:postal_addresses
-- Timestamp: 2012-07-17 14:27:02.822
-- Source database is: postal_addresses
-- Connection URL is: jdbc:derby:postal_addresses
-- appendLogs: false

-- ----------------------------------------------
-- DDL Statements for tables
-- ----------------------------------------------

CREATE TABLE "APP"."ADDRESS" ("ADDRID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "ADDRSTREET" VARCHAR(50) NOT NULL, "ADDRCITY" VARCHAR(50) NOT NULL, "ADDRSTATE" CHAR(2) NOT NULL, "ADDRZIP" CHAR(10) NOT NULL);

-- ----------------------------------------------
-- DDL Statements for keys
-- ----------------------------------------------

-- primary/unique
ALTER TABLE "APP"."ADDRESS" ADD CONSTRAINT "SQL120717142048690" PRIMARY KEY ("ADDRID");

AWK

SQLite3

This version uses the AWK pipe, 'getline' function, and the sqlite3 command line program.

<lang awk>#!/bin/sh -f awk ' BEGIN { 

   print "Creating table..."
   dbExec("address.db", "create table address (street, city, state, zip);")
   print "Done."
   exit

}

function dbExec(db, qry, result) { 

   dbMakeQuery(db, qry) | getline result
   dbErrorCheck(result)

}

function dbMakeQuery(db, qry, q) { 

   q = dbEscapeQuery(qry) ";"
   return "echo \"" q "\" | sqlite3 " db

}

function dbEscapeQuery(qry, q) { 

   q = qry
   gsub(/"/, "\\\"", q)
   return q

}

function dbErrorCheck(res) { 

   if (res ~ "SQL error") {
       print res
       exit
   }

}

'</lang>

C

Library: SQLite

<lang c>#include <stdio.h>

  1. include <stdlib.h>
  2. 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>

Clojure

<lang clojure>(require '[clojure.java.jdbc :as sql])

Using h2database for this simple example.

(def db {:classname "org.h2.Driver" 

        :subprotocol "h2:file"
        :subname "db/my-dbname"})

(sql/db-do-commands db 

 (sql/create-table-ddl :address
   [:id "bigint primary key auto_increment"]
   [:street "varchar"]
   [:city "varchar"]
   [:state "varchar"]
   [:zip "varchar"]))

</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>

EchoLisp

<lang scheme> (lib 'struct) (lib 'sql)

(define Postal (make-table 

   (struct postal (auto: id name street city state zip))))

Postal 

   → #table:#struct:postal [id name street city state zip]:[0]

(table-insert Postal '(0 Gallubert "29 rue de l'Ermitage" Paris Seine 75020)) (table-insert Postal '(0 Brougnard "666 rue des Cascades " Paris Seine 75042)) (table-make-index Postal 'postal.id) (table-print Postal)

[0] 15 Gallubert 29 rue de l'Ermitage Paris Seine 75020 [1] 16 Brougnard 666 rue des Cascades Paris Seine 75042 </lang>


Erlang

Erlang has built in databases. This is the the one with most features: Mnesia. There are database connectors to other databases, too. <lang Erlang> -module( table_creation ).

-export( [task/0] ).

-record( address, {id, street, city, zip} ).

task() -> mnesia:start(), mnesia:create_table( address, [{attributes, record_info(fields, address)}] ). </lang>

Output:
3>  table_creation:task().
{atomic,ok}

FunL

FunL has built-in support for H2 and comes bundled with the H2 database engine. <lang funl>import db.* import util.*

Class.forName( 'org.h2.Driver' ) conn = DriverManager.getConnection( 'jdbc:h2:mem:test', 'sa', ) statement = conn.createStatement()

statement.execute( 

 CREATE TABLE `user_data` (
   `id` identity,
   `name` varchar(255) NOT NULL,
   `street` varchar(255) NOT NULL,
   `city` varchar(255) NOT NULL,
   `region` char(2) NOT NULL,
   `country` char(2) NOT NULL,
   `code` varchar(20) NOT NULL,
   `phone` varchar(20) NOT NULL,
   PRIMARY KEY (`id`)
 ) )

statement.execute( 

 INSERT INTO `user_data` (`name`, `street`, `city`, `region`, `code`, `country`, `phone`) VALUES
   ('Jacinthe Steinert', '8540 Fallen Pony Villas', 'Searights', 'IA', '51584-4315', 'US', '(641) 883-4342'),
   ('Keeley Pinkham', '1363 Easy Downs', 'Mileta', 'TX', '77667-7376', 'US', '(469) 527-4784'),
   ('Rimon Cleveland', '8052 Blue Pond Dale', 'The Willows', 'UT', '84630-2674', 'US', '(385) 305-7261'),
   ('Berenice Benda', '2688 Merry Pines', 'Dacono', 'HI', '96766-7398', 'US', '(808) 451-2732'),
   ('Mehetabel Marcano', '109 Sleepy Goose Crescent', 'Plains', 'UT', '84727-7254', 'US', '(385) 733-8404'),
   ('Ambria Schiller', '7100 Tawny Robin Highway', 'Barlowes', 'ID', '83792-2043', 'US', '(208) 227-8887'),
   ('Carne Cancino', '3842 Broad Pioneer Cape', 'Bardstown', 'IA', '51571-6473', 'US', '(563) 060-8352'),
   ('Ince Leite', '7876 Stony Fawn Boulevard', 'Easton', 'ID', '83651-9235', 'US', '(208) 951-3024'),
   ('Britney Odell', '3386 Lazy Shadow Thicket', 'Kimberly', 'OK', '73539-6632', 'US', '(539) 848-4448'),
   ('Suprabha Penton', '9311 Dusty Leaf Alley', 'Niumalu', 'GA', '39927-8332', 'US', '(404) 589-0183') )

result = statement.executeQuery( SELECT * FROM user_data WHERE region = 'ID' ORDER BY code ) print( TextTable.apply(result) )

conn.close()</lang>

Output:
+----+-----------------+---------------------------+----------+--------+---------+------------+----------------+
| ID |      NAME       |          STREET           |   CITY   | REGION | COUNTRY |    CODE    |     PHONE      |
+----+-----------------+---------------------------+----------+--------+---------+------------+----------------+
|  8 | Ince Leite      | 7876 Stony Fawn Boulevard | Easton   | ID     | US      | 83651-9235 | (208) 951-3024 |
|  6 | Ambria Schiller | 7100 Tawny Robin Highway  | Barlowes | ID     | US      | 83792-2043 | (208) 227-8887 |
+----+-----------------+---------------------------+----------+--------+---------+------------+----------------+

Go

<lang go>package main

import ( 

   "database/sql"
   "fmt"
   "log"

   _ "github.com/mattn/go-sqlite3"

)

func main() { 

   // task req: show database connection
   db, err := sql.Open("sqlite3", "rc.db")
   if err != nil {
       log.Print(err)
       return
   }
   defer db.Close()
   // task req: create table with typed fields, including a unique id
   _, err = db.Exec(`create table addr (
       id     int unique,
       street text,
       city   text,
       state  text,
       zip    text
   )`)
   if err != nil {
       log.Print(err)
       return
   }
   // show output:  query the created field names and types
   rows, err := db.Query(`pragma table_info(addr)`)
   if err != nil {
       log.Print(err)
       return
   }
   var field, storage string
   var ignore sql.RawBytes
   for rows.Next() {
       err = rows.Scan(&ignore, &field, &storage, &ignore, &ignore, &ignore)
       if err != nil {
           log.Print(err)
           return
       }
       fmt.Println(field, storage)
   }

}</lang>

Output:
id int
street text
city text
state text
zip text

Haskell

Translation of: Python
Library: SQLite
Library: sqlite-simple

<lang haskell>{-# LANGUAGE OverloadedStrings #-}

import Database.SQLite.Simple

main = do 

    db <- open "postal.db"
    execute_ db "\ 
    \CREATE TABLE address (\
       \addrID     INTEGER PRIMARY KEY AUTOINCREMENT, \
       \addrStreet TEXT NOT NULL, \
       \addrCity   TEXT NOT NULL, \
       \addrState  TEXT NOT NULL, \
       \addrZIP    TEXT NOT NULL  \
    \)"
    close db</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.

Lasso

Lasso has excellent support for connecting to and handling databases.

<lang Lasso>// connect to a Mysql database inline(-database = 'rosettatest', -sql = "CREATE TABLE `address` ( 

   `id`       int(11)     NOT NULL   auto_increment,
   `street`   varchar(50) NOT NULL   default ,
   `city`     varchar(25) NOT NULL   default ,
   `state`    char(2)     NOT NULL   default ,
   `zip`      char(10)    NOT NULL   default ,
   PRIMARY KEY (`id`)

); ") => {^ error_msg ^}</lang> Output: 

No error

Mathematica

<lang Mathematica>TableCreation="CREATE TABLE address ( addrID INTEGER PRIMARY KEY AUTOINCREMENT, addrStreet TEXT NOT NULL, addrCity TEXT NOT NULL, addrState TEXT NOT NULL, addrZIP TEXT NOT NULL )";

Needs["DatabaseLink`"] conn=OpenSQLConnection[ JDBC[ "mysql","databases:1234/conn_test"], "Username" -> "test"] SQLExecute[ conn, TableCreation]</lang>

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>

NetRexx

As NetRexx targets the Java Virtual Machine it has access to a wealth of database tools many of which can be accessed through JDBC.

Apache Derby

Library: Apache Derby

This sample creates a table in an embedded Apache Derby database. <lang NetRexx>/* NetRexx */ options replace format comments java crossref symbols binary

import java.sql.Connection import java.sql.Statement import java.sql.SQLException import java.sql.DriverManager

class RTableCreate01 public 

 properties private constant
   addressDDL = String  -
   ' create table Address' -
   ' (' -
   '   addrID     integer     primary key generated by default as identity,' -
   '   addrStreet varchar(50) not null,' -
   '   addrCity   varchar(50) not null,' -
   '   addrState  char(2)     not null,' -
   '   addrZip    char(10)    not null' -
   ' )'
   driver = String 'org.apache.derby.jdbc.EmbeddedDriver'
   dbName = String 'db/rosetta_code'

 method createTable() public static
   connectionURL = String 
   conn = java.sql.Connection
   sqlStatement = java.sql.Statement
   do
     Class.forName(driver)
     connectionURL = 'jdbc:derby:' || dbName || ';' || 'create=true'
     conn = DriverManager.getConnection(connectionURL)
     sqlStatement = conn.createStatement()
     say 'Creating table'
     sqlStatement.execute(addressDDL)
     say 'Table creation complete'
     sqlStatement.close()
     conn.close()
     do
       -- In embedded mode, an application should shut down Derby.
       -- Shutdown throws the XJ015 exception to confirm success.
       connectionURL = 'jdbc:derby:' || ';' || 'shutdown=true'
       DriverManager.getConnection(connectionURL)
     catch sex = SQLException
       if sex.getSQLState().equals("XJ015") then do
         say 'Database shut down normally'
         end
       else do
         say 'Database did not shut down normally'
         signal sex
         end
     end
   catch sex = SQLException
     sex.printStackTrace()
   catch ex = ClassNotFoundException
     ex.printStackTrace()
   end
   return

 method main(args = String[]) public static
   createTable()
   return

</lang>

Nim

<lang nim>import db_sqlite as db

  1. import db_mysql as db
  2. import db_postgres as db

const 

 connection = ":memory:"
 user = "foo"
 pass = "bar"
 database = "db"

var c = open(connection, user, pass, database) c.exec sql"""CREATE TABLE address ( 

 addrID     INTEGER PRIMARY KEY AUTOINCREMENT,
 addrStreet TEXT NOT NULL,
 addrCity   TEXT NOT NULL,
 addrState  TEXT NOT NULL,
 addrZIP    TEXT NOT NULL)"""

c.close()</lang>

ooRexx

<lang oorexx>/* REXX ***************************************************************

  • 17.05.2013 Walter Pachl translated from REXX version 2
  • nice try? improvements are welcome as I am rather unexperienced
  • 18.05.2013 the array may contain a variety of objects!
                                                                                                                                            • /

alist=.array~new alist[1]=.addr~new('Boston','MA','51 Franklin Street',,'FSF Inc.',, 

                                                         '02110-1301')

alist[2]='not an address at all' alist[3]=.addr~new('Washington','DC','The Oval Office',, 

                '1600 Pennsylvania Avenue NW','The White House',20500)

Do i=1 To alist~items 

 a=alist[i]
 If a~isinstanceof(.addr) Then
   a~show
 End
class addr
 ::attribute city
 ::attribute state
 ::attribute addr
 ::attribute addr2
 ::attribute name
 ::attribute zip
method init
 Parse Arg self~city,,
           self~state,,
           self~addr,,
           self~addr2,,
           self~name,,
           self~zip
method show
                        Say '  name -->' self~name
                        Say '  addr -->' self~addr
 If self~addr2<> Then Say ' addr2 -->' self~addr2
                        Say '  city -->' self~city
                        Say ' state -->' self~state
                        Say '   zip -->' self~zip
 Say copies('-',40)</lang>

Output is as for REXX version 2

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

Translation of: Python
Library: SQLite
Library: Ozsqlite

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

Translation of: Python

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>

PicoLisp

PicoLisp has built-in database functionality, in the form of (non-relational) entity/relations, built on top of persistent objects (so-called external symbols)

Define an "address" entity, and create the database: <lang PicoLisp>(class +Adr +Entity) (rel nm (+Sn +Idx +String)) # Name [Soundex index] (rel str (+String)) # Street (rel zip (+Ref +String)) # ZIP [Non-unique index] (rel cit (+Fold +Idx +String)) # City [Folded substring index] (rel st (+String)) # State (rel tel (+Fold +Ref +String)) # Phone [Folded non-unique index] (rel em (+Ref +String)) # EMail [Non-unique index] (rel txt (+Blob)) # Memo (rel jpg (+Blob)) # Photo

(pool "address.db") # Create database</lang> Create a first entry, and show it: <lang PicoLisp>(show 

  (new! '(+Adr)  # Create a record
     'nm "FSF Inc."
     'str "51 Franklin St"
     'st "Boston, MA"
     'zip "02110-1301" ) )</lang>

Output: 

{2} (+Adr)
   zip "02110-1301"
   st "Boston, MA"
   str "51 Franklin St"
   nm "FSF Inc."

Interactive "select": <lang PicoLisp>(select nm zip +Adr nm "FSF") # Select name, zip from Adr where name = FSF*</lang> Output: 

"FSF Inc." "02110-1301" {2}

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>


PureBasic+SQLite

Easiest approach with sqlite. Further possible: PostgresQL or each other over ODBC. <lang Purebasic> UseSQLiteDatabase() Procedure CheckDatabaseUpdate(Database, Query$) 

  Result = DatabaseUpdate(Database, Query$)
  If Result = 0
     Print(DatabaseError())
  EndIf
  ProcedureReturn Result

EndProcedure openconsole() DatabaseFile$ = GetCurrentDirectory()+"/rosettadb.sdb" If CreateFile(0, DatabaseFile$) 

  CloseFile(0)
   If OpenDatabase(0, DatabaseFile$, "", "")
     CheckDatabaseUpdate(0,"CREATE TABLE address ( addrID INTEGER PRIMARY KEY AUTOINCREMENT,	addrStreet TEXT Not NULL, addrCity TEXT Not NULL, addrState TEXT Not NULL, addrZIP TEXT Not NULL)")
     CloseDatabase(0)
  Else
     print("Can't open database !")
  EndIf

Else 

  print("Can't create the database file !")

EndIf closeconsole() </lang>

PowerShell+SQLite

Library: SQLite

<lang PowerShell> Import-Module -Name PSSQLite


    1. Create a database and a table

$dataSource = ".\Addresses.db" $query = "CREATE TABLE SSADDRESS (Id INTEGER PRIMARY KEY AUTOINCREMENT, 

                                 LastName  TEXT     NOT NULL,
                                 FirstName TEXT     NOT NULL,
                                 Address   TEXT     NOT NULL,
                                 City      TEXT     NOT NULL,
                                 State     CHAR(2)  NOT NULL,
                                 Zip       CHAR(5)  NOT NULL

)"

Invoke-SqliteQuery -Query $Query -DataSource $DataSource


    1. Insert some data

$query = "INSERT INTO SSADDRESS ( FirstName, LastName, Address, City, State, Zip) 

                        VALUES (@FirstName, @LastName, @Address, @City, @State, @Zip)"

Invoke-SqliteQuery -DataSource $DataSource -Query $query -SqlParameters @{ 

       LastName  = "Monster"
       FirstName = "Cookie"
       Address   = "666 Sesame St"
       City      = "Holywood"
       State     = "CA"
       Zip       = "90013"

}


    1. View the data

Invoke-SqliteQuery -DataSource $DataSource -Query "SELECT * FROM SSADDRESS" </lang>

Output:

Id  : 1 LastName  : Monster FirstName : Cookie Address  : 666 Sesame St City  : Holywood State  : CA Zip  : 90013


Python+SQLite

Library: 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>

Racket

Racket supports a bunch of DBs, this is using sqlite, which is almost always available. Also included some further demonstrations beyond just the table creation:

<lang Racket>

  1. lang at-exp racket

(require db) (define postal (sqlite3-connect #:database "/tmp/postal.db" #:mode 'create))

(define (add! name street city state zip) 

 (query-exec postal
   @~a{INSERT INTO addresses (name, street, city, state, zip)
       VALUES (?, ?, ?, ?, ?)}
   name street city state zip))

(unless (table-exists? postal "addresses") 

 (query-exec postal
   @~a{CREATE TABLE addresses(
         id INTEGER PRIMARY KEY,
         name   TEXT NOT NULL,
         street TEXT NOT NULL,
         city   TEXT NOT NULL,
         state  TEXT NOT NULL,
         zip    TEXT NOT NULL)}))

(add! "FSF Inc." 

     "51 Franklin St"
     "Boston"
     "MA"
     "02110-1301")

(add! "The White House" 

     "1600 Pennsylvania Avenue NW"
     "Washington"
     "DC"
     "20500")

(add! "National Security Council" 

     "1700 Pennsylvania Avenue NW"
     "Washington"
     "DC"
     "20500")

(printf "Addresses:\n") (for ([r (query-rows postal "SELECT * FROM addresses")]) 

 (printf "  ~a.\n" (string-join (cdr (vector->list r)) ", ")))

(newline)

(printf "By State+ZIP:\n") (for ([z (query-rows postal "SELECT * FROM addresses" 

                    #:group #("state" "zip"))])
 (printf "  ~a, ~a:\n" (vector-ref z 0) (vector-ref z 1))
 (for ([r (vector-ref z 2)])
   (printf "    ~a.\n" (string-join (cdr (vector->list r)) ", "))))

(disconnect postal) </lang>

Output: 

Addresses:
  FSF Inc., 51 Franklin St, Boston, MA, 02110-1301.
  The White House, 1600 Pennsylvania Avenue NW, Washington, DC, 20500.
  National Security Council, 1700 Pennsylvania Avenue NW, Washington, DC, 20500.

By State+ZIP:
  MA, 02110-1301:
    FSF Inc., 51 Franklin St, Boston.
  DC, 20500:
    The White House, 1600 Pennsylvania Avenue NW, Washington.
    National Security Council, 1700 Pennsylvania Avenue NW, Washington.

REXX

version 1

A REXX program can call SQL or any other database system, but the version shown here is a RYO (roll your own).
Practically no error checking (for invalid fields, etc.) has been coded.
The fields are for the most part, USA specific, but could be expanded for other countries.
In addition to "state", fields such as province, municipality, ward, parish, country, etc) could be added without exclusion.
A logging facility is included which tracks who (by userID) did what update (or change), along with a timestamp. 

╔═════════════════════════════════════════════════════════════════════════════════╗
╟───── Format of an entry in the USA address/city/state/zip code structure: ──────╢
║                                                                                 ║
║ The "structure" name can be any legal variable name, but here the name will be  ║
║ shortened to make these comments (and program) easier to read;  its name will   ║
║ be    @USA     (in any letter case).  In addition, the following variable names ║
║ (stemmed array tails)  will need to be kept uninitialized  (that is, not used   ║
║ for any variable name).  To that end, each of these variable names will have an ║
║ underscore in the beginning of each name.  Other possibilities are to have a    ║
║ trailing underscore (or both leading and trailing), or some other special eye─  ║
║ catching character such as:   !  @  #  $  ?                                     ║
║                                                                                 ║
║ Any field not specified will have a value of "null"  (which has a length of 0). ║
║                                                                                 ║
║ Any field can contain any number of characters,  this can be limited by the     ║
║ restrictions imposed by the standards  or  the USA legal definitions.           ║
║ Any number of fields could be added  (with testing for invalid fields).         ║
╟─────────────────────────────────────────────────────────────────────────────────╢
║  @USA.0             the number of entries in the   @USA  stemmed array.         ║
║                                                                                 ║
║       nnn           is some positive integer of any length (no leading zeroes). ║
╟─────────────────────────────────────────────────────────────────────────────────╢
║  @USA.nnn._name     is the name of person, business,  or a lot description.     ║
╟─────────────────────────────────────────────────────────────────────────────────╢
║  @USA.nnn._addr     is the 1st street address                                   ║
║  @USA.nnn._addr2    is the 2nd street address                                   ║
║  @USA.nnn._addr3    is the 3rd street address                                   ║
║  @USA.nnn._addrNN      ···  (any number,  but in sequential order) .            ║
╟─────────────────────────────────────────────────────────────────────────────────╢
║  @USA.nnn._state    is the USA postal code for the state, territory, etc.       ║
╟─────────────────────────────────────────────────────────────────────────────────╢
║  @USA.nnn._city     is the official city name,  it may include any character.   ║
╟─────────────────────────────────────────────────────────────────────────────────╢
║  @USA.nnn._zip      is the USA postal zip code  (five or ten digit format).     ║
╟─────────────────────────────────────────────────────────────────────────────────╢
║  @USA.nnn._upHist   is the update history   (who,  date,  and  timestamp).      ║
╚═════════════════════════════════════════════════════════════════════════════════╝

<lang rexx>/*REXX program creates, builds, and displays a table of given U.S.A. postal addresses.*/ @usa.=; @usa.0=0 /*initialize stemmed array & 1st value.*/ @usa.0=@usa.0+1 /*bump the unique number for usage. */ 

               call USA '_city'  ,  'Boston'
               call USA '_state' ,  'MA'
               call USA '_addr'  ,  "51 Franklin Street"
               call USA '_name'  ,  "FSF Inc."
               call USA '_zip'   ,  '02110-1301'

@usa.0=@usa.0+1 /*bump the unique number for usage. */ 

               call USA '_city'  ,  'Washington'
               call USA '_state' ,  'DC'
               call USA '_addr'  ,  "The Oval Office"
               call USA '_addr2' ,  "1600 Pennsylvania Avenue NW"
               call USA '_name'  ,  "The White House"
               call USA '_zip'   ,  20500
               call USA 'list'

exit /*stick a fork in it, we're all done. */ /*──────────────────────────────────────────────────────────────────────────────────────*/ list: call tell '_name' 

     call tell '_addr'
                          do j=2  until $==;    call tell  "_addr"j;    end  /*j*/
     call tell '_city'
     call tell '_state'
     call tell '_zip'
     say copies('─', 40)
     return

/*──────────────────────────────────────────────────────────────────────────────────────*/ tell: $=value('@USA.'#"."arg(1));if $\= then say right(translate(arg(1),,'_'),6) "──►" $ 

     return

/*──────────────────────────────────────────────────────────────────────────────────────*/ USA: procedure expose @USA.; parse arg what,txt; arg ?; @='@USA.' 

       if ?=='LIST'  then do #=1  for @usa.0;   call list;   end  /*#*/
                     else do
                          call value @ || @usa.0 || . || what    , txt
                          call value @ || @usa.0 || . || 'upHist', userid() date() time()
                          end
       return</lang>

output   (data used is within the REXX program): 

  name ──► FSF Inc.
  addr ──► 51 Franklin Street
  city ──► Boston
 state ──► MA
   zip ──► 02110-1301
────────────────────────────────────────
  name ──► The White House
  addr ──► The Oval Office
 addr2 ──► 1600 Pennsylvania Avenue NW
  city ──► Washington
 state ──► DC
   zip ──► 20500
────────────────────────────────────────

version 2

<lang rexx>/* REXX ***************************************************************

  • 17.05.2013 Walter Pachl
  • should work with every REXX.
  • I use 0xxx for the tail because this can't be modified
                                                                                                                                            • /

USA.=; USA.0=0 Call add_usa 'Boston','MA','51 Franklin Street',,'FSF Inc.',, 

                                                          '02110-1301'

Call add_usa 'Washington','DC','The Oval Office',, 

                 '1600 Pennsylvania Avenue NW','The White House',20500

call list_usa Exit

add_usa: z=usa.0+1 Parse Arg usa.z.0city,, 

         usa.z.0state,,
         usa.z.0addr,,
         usa.z.0addr2,,
         usa.z.0name,,
         usa.z.0zip

usa.0=z Return

list_usa: Do z=1 To usa.0 

                          Say '  name -->' usa.z.0name
                          Say '  addr -->' usa.z.0addr
 If usa.z.0addr2<> Then Say ' addr2 -->' usa.z.0addr2
                          Say '  city -->' usa.z.0city
                          Say ' state -->' usa.z.0state
                          Say '   zip -->' usa.z.0zip
 Say copies('-',40)
 End

Return</lang> 

  name --> FSF Inc.
  addr --> 51 Franklin Street
  city --> Boston
 state --> MA
   zip --> 02110-1301
----------------------------------------
  name --> The White House
  addr --> The Oval Office
 addr2 --> 1600 Pennsylvania Avenue NW
  city --> Washington
 state --> DC
   zip --> 20500
----------------------------------------

Ruby

With PStore

PStore implements a persistent key store with transactions. This is a NoSQL database. Each transaction reads the entire database into memory, and then writes it again, so PStore is not good for large databases.

<lang ruby>require 'pstore' require 'set'

Address = Struct.new :id, :street, :city, :state, :zip

db = PStore.new("addresses.pstore") db.transaction do 

 db[:next] ||= 0       # Next available Address#id
 db[:ids] ||= Set[]    # Set of all ids in db

end</lang>

To put an Address inside this PStore:

<lang ruby>db.transaction do 

 id = (db[:next] += 1)
 db[id] = Address.new(id,
                      "1600 Pennsylvania Avenue NW",
                      "Washington", "DC", 20500)
 db[:ids].add id

end</lang>

With SQLite

Translation of: Python
Library: sqlite3-ruby

<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>

Run BASIC

AQLite <lang runbasic>sqliteconnect #mem, ":memory:" ' make handle #mem mem$ = " CREATE TABLE address ( 

 addrID     INTEGER PRIMARY KEY AUTOINCREMENT,
 addrStreet TEXT NOT NULL,
 addrCity   TEXT NOT NULL,
 addrState  TEXT NOT NULL,
 addrZIP    TEXT NOT NULL

)"

  1. mem execute(mem$)</lang>

SAS

<lang sql>

PROC SQL; CREATE TABLE ADDRESS ( ADDRID CHAR(8) ,STREET CHAR(50) ,CITY CHAR(25) ,STATE CHAR(2) ,ZIP CHAR(20) )

QUIT;

</lang>

Scheme

Library: SQLite

This example works with Chicken Scheme, using its sql-de-lite library:

<lang scheme> (use sql-de-lite)

(define *db* (open-database "addresses"))

(exec ; create and run the SQL statement 

 (sql *db*
      "CREATE TABLE address (
       addrID     INTEGER PRIMARY KEY AUTOINCREMENT,
       addrStreet TEXT NOT NULL,
       addrCity   TEXT NOT NULL,
       addrState  TEXT NOT NULL,
       addrZIP    TEXT NOT NULL
   )"

))

(close-database *db*) ; finally, close database </lang>

Sidef

Translation of: Perl

<lang ruby>require('DBI');

var db = %s'DBI'.connect('DBI:mysql:database:server','login','password');

var 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

var exec = db.prepare(statment); exec.execute;</lang>

SQLite

Purely in Sqlite3. <lang sqlite3> CREATE TABLE address_USA ( 

   address_ID INTEGER PRIMARY KEY,
   address_Street TEXT,
   address_City TEXT,
   address_State TEXT,
   address_Zip INTEGER

); </lang>

Tcl+SQLite

Library: 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>

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>

VBScript

<lang vb> Option Explicit

Dim objFSO, DBSource

Set objFSO = CreateObject("Scripting.FileSystemObject")

DBSource = objFSO.GetParentFolderName(WScript.ScriptFullName) & "\postal_address.accdb"

With CreateObject("ADODB.Connection") .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBSource .Execute "CREATE TABLE ADDRESS (STREET VARCHAR(30) NOT NULL," &_ "CITY VARCHAR(30) NOT NULL, STATE CHAR(2) NOT NULL,ZIP CHAR(5) NOT NULL)" .Close End With </lang>

Visual FoxPro

<lang vfp> CLOSE DATABASES ALL CREATE DATABASE usdata.dbc SET NULL OFF CREATE TABLE address.dbf ; (id I AUTOINC NEXTVALUE 1 STEP 1 PRIMARY KEY COLLATE "Machine", ; 

street V(50), city V(25), state C(2), zipcode C(10))

CLOSE DATABASES ALL

  • !* To use

CLOSE DATABASES ALL OPEN DATABASE usdata.dbc USE address.dbf SHARED </lang>

Retrieved from "https://rosettacode.org/wiki/Table_creation/Postal_addresses?oldid=11560"