Table creation: Difference between revisions
Added BASIC256
(Added BASIC256) |
|||
| (5 intermediate revisions by 4 users not shown) | |||
Line 11:
=={{header|Arturo}}==
<
createTable: function [][
Line 49:
print ["getting user with name: JohnDoe =>" findUser "JohnDoe"]
close db</
{{out}}
Line 62:
AWK is just a glue language. Simply pipe the creation command into SQLite and capture the output.
<
awk '
BEGIN {
Line 69:
exit;
}
'</
=={{header|BASIC256}}==
<syntaxhighlight lang="vbnet"># create a new database file or open it
dbopen "mydbtest.sqlite3"
# delete the existing table in Personas - If it is a new database, the error is captured
onerror errortrap
dbexecute "drop table Personas;"
offerror
# create the table and enter data into it
dbexecute "create table Personas (id integer, nombre text, apellido text, edad integer, direccion string(50), salario decimal);"
dbexecute "insert into Personas values (1, 'Juan', 'Hdez', 52, '123 Calle Principal', 50000.00);"
# open a recordset and loop through the rows of data
print "Contents of the Personas table:"
dbopenset "select * from Personas order by nombre;"
while dbrow()
print dbint(0) + " " + dbstring(1) + " " + dbstring(2) + " " + dbint(3) + " " + dbstring(4) + " " + dbfloat(5)
end while
dbcloseset
# close all
dbclose
end
errortrap:
# accept the error - show nothing - return to the next statement
return</syntaxhighlight>
{{out}}
<pre>Contents of the Personas table:
1 Juan Hdez 52 123 Calle Principal 50000.0</pre>
=={{header|C}}==
Line 75 ⟶ 108:
{{libheader|SQLite}}
<syntaxhighlight lang="c">
#include <sqlite3.h>
#include <stdlib.h>
Line 103 ⟶ 136:
return 0;
}
</syntaxhighlight>
=={{header|FunL}}==
<
import util.*
Line 132 ⟶ 165:
statement.execute( "SELECT * FROM Persons ORDER BY LastName" )
print( TextTable.apply(statement.getResultSet()) )
conn.close()</
{{out}}
Line 147 ⟶ 180:
+----------+-----------+----------+------------------------------+-------------+----------+
</pre>
=={{header|FreeBASIC}}==
{{libheader|SQLite}}
<syntaxhighlight lang="vbnet">#include once "sqlite3.bi"
Const NULL As Any Ptr = 0
Dim As sqlite3 Ptr db
Dim As zstring Ptr errMsg
Dim As sqlite3_stmt Ptr stmt
If sqlite3_open(":memory:", @db) <> SQLITE_OK Then
Print "Could not open database: "; sqlite3_errmsg(db)
sqlite3_close(db)
Sleep
End 1
End If
' Create the table
Dim As String sql = "CREATE TABLE Persons(" _
& "ID INT PRIMARY KEY NOT NULL," _
& "NAME TEXT NOT NULL," _
& "SURNAME TEXT NOT NULL," _
& "AGE INT NOT NULL," _
& "ADDRESS CHAR(50)," _
& "SALARY REAL );"
If sqlite3_exec(db, sql, NULL, NULL, @errMsg) <> SQLITE_OK Then
Print "Error creating table: "; *errMsg
sqlite3_free(errMsg)
Else
Print "Table created successfully"
End If
' Insert some data
sql = "INSERT INTO Persons (ID, NAME, SURNAME, AGE, ADDRESS, SALARY) " _
& "VALUES (1, 'John', 'Doe', 30, '123 Main St', 50000.00);"
If sqlite3_exec(db, sql, NULL, NULL, @errMsg) <> SQLITE_OK Then
Print "Error inserting data: "; *errMsg
sqlite3_free(errMsg)
End If
' Display the contents of the table
sql = "SELECT * FROM Persons;"
If sqlite3_prepare_v2(db, sql, -1, @stmt, NULL) <> SQLITE_OK Then
Print "Error preparing statement: "; sqlite3_errmsg(db)
Else
While sqlite3_step(stmt) = SQLITE_ROW
Print "ID: "; sqlite3_column_int(stmt, 0)
Print "Name: "; *cast(zstring ptr, sqlite3_column_text(stmt, 1))
Print "Surname: "; *cast(zstring ptr, sqlite3_column_text(stmt, 2))
Print "Age: "; sqlite3_column_int(stmt, 3)
Print "Address: "; *cast(zstring ptr, sqlite3_column_text(stmt, 4))
Print "Salary: "; sqlite3_column_double(stmt, 5)
Print
Wend
End If
sqlite3_finalize(stmt)
sqlite3_close(db)
Sleep</syntaxhighlight>
{{out}}
<pre>Table created successfully
ID: 1
Name: Juan
Surname: Hdez
Age: 52
Address: 123 Main St
Salary: 50000</pre>
=={{header|Go}}==
Line 152 ⟶ 257:
<br>
This uses a key/value store rather than a relational database to create the table.
<
import (
Line 241 ⟶ 346:
return nil
})
}</
{{out}}
Line 257 ⟶ 362:
If we define a <code>table</code> as a named collection of columns, and we define a <code>type</code> as a mechanism for the representation of some kind of data, then:
<
insertStock=: 3 :'0#stocks=: stocks,.y'
insertStock@".;._2]0 :0
Line 264 ⟶ 369:
'2006-04-05'; 'BUY'; 'MSOFT'; 1000; 72.00
'2006-04-06'; 'SELL'; 'IBM'; 500; 53.00
)</
declares a table and some data within that table.
Line 270 ⟶ 375:
And, here's an example of sorting:
<
sortBy=: [ /:"1 2 (<__)|:@,. [ }.@{~ cols
from=: cols~ {"0 _ ]
Line 286 ⟶ 391:
├──────────┼─────┼──────┼────┼─────┤
│2006-04-05│BUY │MSOFT │1000│72 │
└──────────┴─────┴──────┴────┴─────┘</
Note that this particular example is both overly general in some senses (for example, named column handling has features not demonstrated here) and overly specific in others (for example, I did not implement sort in descending order).
Line 295 ⟶ 400:
{{works with|Julia|0.6}}
<
conn = SQLite.DB() # in-memory
Line 317 ⟶ 422:
df = SQLite.query(conn, "select * from stocks order by price")
println(df)</
{{out}}
Line 329 ⟶ 434:
=={{header|Lua}}==
<
Columns.ID = {};
Columns.FName = {};
Line 384 ⟶ 489:
--[[ ]]--
listTables();</
{{out}}
Line 403 ⟶ 508:
We can use text from UTF16LE set. Here we have Greek letters in Memo.
<syntaxhighlight lang="m2000 interpreter">
MODULE SIMPLEBASE {
BASE "ALFA" ' ERASED IF FOUND THE NAME OF "ALFA.MDB"
Line 419 ⟶ 524:
}
SIMPLEBASE
</syntaxhighlight>
=={{header|Mathematica}}/{{header|Wolfram Language}}==
<
"databases:1234/conn_test"], "Username" -> "test"]
SQLCreateTable[conn, SQLTable["TEST"],If[Length[#] == 0,SQLColumn[StringJoin[#,"COL"],"DataTypeName" -> #],SQLColumn[StringJoin[#[[1]], "COL"], "DataTypeName" -> #[[1]],"DataLength" -> #[[2]]]] & /@ {"TINYINT", "SMALLINT", "INTEGER","BIGINT", "NUMERIC", "DECIMAL", "FLOAT", "REAL", "DOUBLE", "BIT","LONGVARBINARY", "VARBINARY", "BINARY","LONGVARCHAR",{"VARCHAR", 5},{"CHAR", 3},"DATE","TIME","TIMESTAMP","OBJECT"}]</
=={{header|Nim}}==
Line 430 ⟶ 535:
Nim standard library provides two modules to work with SQL databases. We use here the high level one for SQLite.
<
let dbc = open(":memory:", "", "", "")
Line 445 ⟶ 550:
# Data retrieval.
for row in dbc.fastRows(sql"select * from stocks order by price"):
echo row</
{{out}}
Line 455 ⟶ 560:
=={{header|Oracle}}==
Great SCOTT! from utlsampl.sql
<
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
Line 465 ⟶ 570:
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
</syntaxhighlight>
=={{header|Oz}}==
Line 473 ⟶ 578:
{{libheader|Ozsqlite}}
<
[Sqlite] = {Module.link ['x-ozlib:/sqlite/Sqlite.ozf']}
Line 510 ⟶ 615:
{Sqlite.close DB}
end
</syntaxhighlight>
=={{header|PARI/GP}}==
The most natural way to store tabular data in GP is in a matrix:
<
m[1,] = ["Barack", "Obama", 20500];
\\ ...</
=={{header|Perl}}==
{{trans|Julia}}
<syntaxhighlight lang="perl"># 20211218 Perl programming solution
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect("dbi:SQLite:dbname=:memory:");
$dbh->do("CREATE TABLE stocks (
date TEXT, trans TEXT, symbol TEXT, qty REAL, price REAL
)");
my $sth = $dbh->prepare( "INSERT INTO stocks VALUES (?,?,?,?,?)" );
my @DATA = ( '"2006-01-05","BUY", "RHAT", 100, 35.14',
'"2006-03-28","BUY", "IBM", 1000, 45.00',
'"2006-04-05","BUY","MSOFT", 1000, 72.00',
'"2006-04-06","SELL", "IBM", 500, 53.00' );
for ( @DATA ) { $sth->execute( split /,/ ) or die }
$sth = $dbh->prepare("SELECT * FROM stocks ORDER BY price") or die;
$sth->execute();
my $format = "%-15s %-15s %-15s %-15s %-15s\n";
printf $format, $sth->{NAME}->@* ;
print '=' x 75 , "\n";
while ( my @row = $sth->fetchrow_array ) { printf $format, @row }</syntaxhighlight>
{{out}}
<pre>
date trans symbol qty price
===========================================================================
"2006-01-05" "BUY" "RHAT" 100 35.14
"2006-03-28" "BUY" "IBM" 1000 45
"2006-04-06" "SELL" "IBM" 500 53
"2006-04-05" "BUY" "MSOFT" 1000 72
</pre>
=={{header|Phix}}==
{{libheader|SQLite}}
<!--<
<span style="color: #008080;">include</span> <span style="color: #000000;">pSQLite</span><span style="color: #0000FF;">.</span><span style="color: #000000;">e</span>
<span style="color: #008080;">constant</span> <span style="color: #000000;">sqlcode</span> <span style="color: #0000FF;">=</span> <span style="color: #008000;">"""
Line 537 ⟶ 687:
<span style="color: #7060A8;">printf</span><span style="color: #0000FF;">(</span><span style="color: #000000;">1</span><span style="color: #0000FF;">,</span><span style="color: #008000;">"sqlite3_exec error: %d [%s]\n"</span><span style="color: #0000FF;">,{</span><span style="color: #000000;">res</span><span style="color: #0000FF;">,</span><span style="color: #000000;">sqlite_last_exec_err</span><span style="color: #0000FF;">})</span>
<span style="color: #008080;">end</span> <span style="color: #008080;">if</span>
<!--</
=={{header|PicoLisp}}==
<
(class +Account +Entity)
Line 574 ⟶ 724:
(if (: active) "Yes" "No")
(: username)
(money (: balance)) ) ) )</
Output:
<pre>account_id created active username balance
Line 581 ⟶ 731:
=={{header|PL/I}}==
<
2 name character (20) varying,
2 address,
Line 590 ⟶ 740:
2 transaction_date date,
2 sex character (1),
2 suppress_junk_mail bit (1);</
=={{header|PostgreSQL}}==
Line 596 ⟶ 746:
Postgres developers, please feel free to add additional data-types you commonly use to this example.
<
CREATE SEQUENCE account_seq start 100;
Line 623 ⟶ 773:
-- char(#): space padded text field with length of #
-- varchar(#): variable length text field up to #
-- text: not limited</
=={{header|Python}}==
{{libheader|SQLite}}
The sqlite3 database is a part of the Python standard library. It does not associate type with table columns, any cell can be of any type.
<
>>> conn = sqlite3.connect(':memory:')
>>> c = conn.cursor()
Line 662 ⟶ 812:
(u'2006-04-06', u'SELL', u'IBM', 500.0, 53.0)
(u'2006-04-05', u'BUY', u'MSOFT', 1000.0, 72.0)
>>> </
=={{header|Racket}}==
This is the relevant part of [[Table creation/Postal addresses#Racket]] that creates the DB table:
<
#lang racket
(require db)
(define postal (sqlite3-connect #:database "/tmp/postal.db" #:mode 'create))
</syntaxhighlight>
=={{header|Raku}}==
Line 708 ⟶ 858:
However, tables (or structures) can be constructed by using stemmed arrays; the index would (should) be
<br>a unique identifier, something akin to a SSN (Social Security Number) or something similar.
<
table.id.!firstname = 'Robert'
Line 719 ⟶ 869:
table.id.!town = 'Gotham City'
table.id.!state = 'NY'
table.id.!zip = '12345-6789'</
=={{header|Ring}}==
<
# Project : Table creation
Line 761 ⟶ 911:
next
sqlite_close(oSQLite)
</syntaxhighlight>
Output:
<pre>
Line 794 ⟶ 944:
This code is enough to create a PStore (or open an existing PStore).
<
db = PStore.new "filename.pstore"</
The example at [[Table creation/Postal addresses#Ruby]] puts Ruby objects into the PStore.
Line 802 ⟶ 952:
Run Basic supports all features of SQLite.
This is a sample of a item master
<
#sql execute("
CREATE TABLE item (
Line 841 ⟶ 991:
CREATE UNIQUE INDEX item_descr ON item( descr, itemNum);
CREATE UNIQUE INDEX item_itemNum ON item(itemNum);"
</syntaxhighlight>
=={{header|Scala}}==
===using SLICK FRM===
<
import slick.jdbc.H2Profile.api._
Line 880 ⟶ 1,030:
// Create the tables, including primary and foreign keys
(suppliers.schema ++ coffees.schema).create
)}</
=={{header|SQL PL}}==
{{works with|Db2 LUW}}
<
CREATE TABLE dept (
deptno NUMERIC(2)
Line 918 ⟶ 1,068:
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);
</syntaxhighlight>
Output:
<pre>
Line 1,018 ⟶ 1,168:
=={{header|Tcl}}==
Tables, as used in relational databases, seem far away conceptually from Tcl. However, the following code demonstrates how a table (implemented as a list of lists, the first being the header line) can be type-checked and rendered:
<
upvar $_tbl tbl
set heads [lindex $tbl 0]
Line 1,092 ⟶ 1,242:
balance 0.0 \
created 2009-05-14
puts [table_format $mytbl]</
Output:
<pre>
Line 1,104 ⟶ 1,254:
=={{header|Wren}}==
===Version 1===
{{libheader|Wren-dynamic}}
{{libheader|Wren-fmt}}
Line 1,110 ⟶ 1,261:
In practice, a binary search would be needed to find records quickly by key given that the records are being maintained in sorted order. However, for now we use a sequential search instead.
<
import "./fmt" for Fmt
import "./sort" for Cmp, Sort
var FieldType = Enum.create("FieldType", ["text", "num", "int", "bool"])
Line 1,217 ⟶ 1,368:
table.removeRecord(3)
System.print("\nThe record with an id of 3 will be deleted, leaving:\n")
table.showRecords()</
{{out}}
Line 1,254 ⟶ 1,405:
2 2006-03-28 BUY IBM 1000 45.00 true
4 2006-04-05 BUY MSOFT 1000 72.00 false
</pre>
<br>
===Version 2===
{{libheader|Wren-table}}
The above module provides a more generic way to create simple databases and was not available when the first version was written.
<syntaxhighlight lang="wren">import "./table" for FieldInfo, Table, Records
var fields = [
FieldInfo.new("id", Num),
FieldInfo.new("date", String),
FieldInfo.new("trans", String),
FieldInfo.new("sym", String),
FieldInfo.new("qty", Num),
FieldInfo.new("price", Num),
FieldInfo.new("settled", Bool)
]
// create table
var table = Table.new("Stock_transactions", fields)
// add records
table.add([3, "2006-04-06", "SELL", "IBM" , 500, 53.00, true ])
table.add([1, "2006-01-05", "BUY" , "RHAT" , 100, 35.14, true ])
table.add([4, "2006-04-05", "BUY" , "MSOFT", 1000, 72.00, false])
table.add([2, "2006-03-28", "BUY" , "IBM" , 1000, 45.00, true ])
var colWidths = [2, 10, 4, 5, 4, 5.2, 7] // for listings
// show the table's fields
table.listFields()
System.print()
// sort the records by 'id' and show them
var sortFn = Fn.new { |s, t| s[0] < t[0] }
var records = table.sortedRecords(sortFn)
Records.list(table.fields, records, "Records for %(table.name) table:\n", colWidths)
// find a record by key
System.print("\nThe record with an id of 2 is:")
System.print(table.find(2))
// delete a record by key
table.remove(3)
System.print("\nThe record with an id of 3 will be deleted, leaving:\n")
records = table.sortedRecords(sortFn)
Records.list(table.fields, records, "Records for %(table.name) table:\n", colWidths)</syntaxhighlight>
{{out}}
<pre>
Fields for Stock_transactions table:
name kind
------- ------
id Num
date String
trans String
sym String
qty Num
price Num
settled Bool
Records for Stock_transactions table:
id date tran sym qty price settled
-- ---------- ---- ----- ---- ----- -------
1 2006-01-05 BUY RHAT 100 35.14 true
2 2006-03-28 BUY IBM 1000 45.00 true
3 2006-04-06 SELL IBM 500 53.00 true
4 2006-04-05 BUY MSOFT 1000 72.00 false
The record with an id of 2 is:
[2, 2006-03-28, BUY, IBM, 1000, 45, true]
The record with an id of 3 will be deleted, leaving:
Records for Stock_transactions table:
id date tran sym qty price settled
-- ---------- ---- ----- ---- ----- -------
1 2006-01-05 BUY RHAT 100 35.14 true
2 2006-03-28 BUY IBM 1000 45.00 true
4 2006-04-05 BUY MSOFT 1000 72.00 false
</pre>
| |||