Simple database: Difference between revisions
Content added Content deleted
Line 1,475: | Line 1,475: | ||
</pre> |
</pre> |
||
=={{header|Run BASIC}}== |
=={{header|Run BASIC}}== |
||
<lang runbasic> |
<lang runbasic>sqliteconnect #sql, "f:\client.db" ' Connect to the DB |
||
sqliteconnect #sql, clientDb$ ' Connect to the DB |
|||
' |
' ------------------------------- |
||
' show user options |
|||
' Create Table structure for: client 4 fields |
|||
' |
' ------------------------------- |
||
[sho] |
|||
sql$ = " |
|||
cls ' clear screen |
|||
CREATE TABLE client ( |
|||
button #acd, "Add a new entry", [add] |
|||
clientNum INT(5) NULL, |
|||
button #acd, "Print the latest entry", [last] |
|||
name VARCHAR(30) NULL, |
|||
button #acd, "Print the latest entry for each category", [lastCat] |
|||
clientDate DATETIME NULL, |
|||
button #acd, "Print all entries sorted by a date", [date] |
|||
category VARCHAR(10) NULL)" |
|||
# |
button #ex, "Exit", [exit] |
||
wait |
|||
' |
' ------------------------------------ |
||
' add a new entry |
|||
' Make 5 records for client |
|||
' |
' ------------------------------------ |
||
[add] |
|||
sql$ = " |
|||
cls ' clear the screen |
|||
INSERT INTO client VALUES ('1','Home Sales','2012-01-01 10;20','broker'); |
|||
html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0 bgcolor=wheat>" |
|||
INSERT INTO client VALUES ('2','Best Foods','2011-02-02 12;33','food'); |
|||
html "<TR align=center BGCOLOR=tan><TD colspan=2>Client Maintenance</TD></TR><TR>" |
|||
INSERT INTO client VALUES ('3','Floral Designs','2010-10-14 09:16','flowers'); |
|||
html "<TD bgcolor=tan align=right>Client Num</TD><TD>" |
|||
INSERT INTO client VALUES ('4','Back 40 Equipment','2009-09-18 20:18','farm'); |
|||
textbox #clientNum,clientNum$,5 |
|||
INSERT INTO client VALUES ('5','Dawnridge Winery','2008-06-18 22;16','wine');" |
|||
html "</TD></TR><TR><TD bgcolor=tan align=right>Name</TD><TD>" |
|||
textbox #name,name$,30 |
|||
html "</TD></TR><TR><TD bgcolor=tan align=right>Client Date</TD><TD>" |
|||
textbox #clientDate,clientDate$,19 |
|||
html "</TD></TR><TR><TD bgcolor=tan align=right>Category</TD><TD>" |
|||
textbox #category,category$,10 |
|||
html "</TD></TR><TR><TR bgcolor=tan><TD colspan=2 ALIGN=CENTER>" |
|||
button #acd, "Add", [addIt] |
|||
button #ex, "Exit", [sho] |
|||
html "</TD></TR></TABLE>" |
|||
wait |
|||
' --------------------------------------------- |
|||
' Get data from the screen |
|||
' --------------------------------------------- |
|||
[addIt] |
|||
clientNum = val(clientNum$) |
|||
name$ = trim$(#name contents$()) |
|||
clientDate$ = trim$(#clientDate contents$()) |
|||
category$ = trim$(#category contents$()) |
|||
dbVals$ = clientNum;",'";name$;"','";clientDate$;"','";category$;"'" |
|||
sql$ = "INSERT into client ("; dbFields$; ") VALUES ("; dbVals$ ; ")" |
|||
#sql execute(sql$) |
#sql execute(sql$) |
||
[sho] |
goto [sho] |
||
' ------------------------------------ |
' ------------------------------------ |
||
' Select last entry |
' Select last entry |
||
' ------------------------------------ |
' ------------------------------------ |
||
[last] |
|||
sql$ = "SELECT *,client.rowid as rowid FROM client ORDER BY rowid desc LIMIT 1" |
sql$ = "SELECT *,client.rowid as rowid FROM client ORDER BY rowid desc LIMIT 1" |
||
what$ = "---- Last Entry ----" |
|||
goto [shoQuery] |
|||
' ------------------------------------ |
' ------------------------------------ |
||
' Select by category |
' Select by category |
||
' ------------------------------------ |
' ------------------------------------ |
||
[lastCat] |
|||
sql$ = "SELECT * FROM client ORDER BY category" |
|||
sql$ = "SELECT *,client.rowid FROM client |
|||
print "---- category Sequence ----" |
|||
WHERE client.rowid = (SELECT max(c.rowid) |
|||
gosub [shoQuery] |
|||
FROM client as c WHERE c.category = client.category) |
|||
ORDER BY category" |
|||
' ------------------------------------ |
|||
what$ = "---- Last Category Sequence ----" |
|||
' Select by name |
|||
goto [shoQuery] |
|||
' ------------------------------------ |
|||
sql$ = "SELECT * FROM client ORDER BY name" |
|||
print "---- Name Sequence ----" |
|||
gosub [shoQuery] |
|||
' ------------------------------------ |
' ------------------------------------ |
||
' Select by date |
' Select by date |
||
' ------------------------------------ |
' ------------------------------------ |
||
[date] |
|||
sql$ = "SELECT * FROM client ORDER BY clientDate" |
sql$ = "SELECT * FROM client ORDER BY clientDate" |
||
what$ = "---- By Date ----" |
|||
gosub [shoQuery] |
|||
end |
|||
[shoQuery] |
[shoQuery] |
||
cls |
|||
html "TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>" |
|||
print what$ |
|||
html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>" |
|||
html "<TR align=center>" |
html "<TR align=center>" |
||
html "<TD>Client<br>Num</TD>" |
html "<TD>Client<br>Num</TD>" |
||
Line 1,545: | Line 1,571: | ||
clientDate$ = #row clientDate$() |
clientDate$ = #row clientDate$() |
||
category$ = #row category$() |
category$ = #row category$() |
||
rowid$ = #row rowid$() |
|||
html "</TD>" |
html "</TD>" |
||
Line 1,555: | Line 1,580: | ||
WEND |
WEND |
||
html "</TABLE>" |
html "</TABLE>" |
||
button #c, "Continue", [sho] |
|||
RETURN</lang> |
|||
wait |
|||
' ------ the end ------- |
|||
[exit] |
|||
end</lang> |
|||
Output: |
Output: |
||
---- User Input ----<br /> |
|||
<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0 bgcolor=wheat> |
|||
<TR align=center BGCOLOR=tan><TD colspan=2>Client Maintenance</TD></TR> |
|||
<TR><TD bgcolor=tan align=right>Client Num</TD><TD>5</TD></TR> |
|||
<TR><TD bgcolor=tan align=right>Name</TD><TD>Dawnridge Winery</TD></TR> |
|||
<TR><TD bgcolor=tan align=right>Client Date</TD><TD><2008-06-18 22;16</TD></TR> |
|||
<TR><TD bgcolor=tan align=right>Category</TD><TD>wine></TD></TR> |
|||
<TR><TR bgcolor=tan><TD colspan=2 ALIGN=CENTER>[Add] [Exit]</TD></TR></TABLE> |
|||
---- Last Entry ----<br /> |
---- Last Entry ----<br /> |
||
Line 1,562: | Line 1,601: | ||
<TR align=center><TD>Client<br>Num</TD><TD>Name</TD><TD>Client<br>Date</TD><TD>Category</TD></TR> |
<TR align=center><TD>Client<br>Num</TD><TD>Name</TD><TD>Client<br>Date</TD><TD>Category</TD></TR> |
||
<TR><TD align=right>5</TD><TD>Dawnridge Winery</TD><TD>2008-06-18 22;16</TD><TD>wine</TD></TR></TABLE> |
<TR><TD align=right>5</TD><TD>Dawnridge Winery</TD><TD>2008-06-18 22;16</TD><TD>wine</TD></TR></TABLE> |
||
---- category Sequence ----<br /> |
|||
---- Last category Sequence ----<br /> |
|||
<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0> |
<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0> |
||
<TR align=center><TD>Client<br>Num</TD><TD>Name</TD><TD>Client<br>Date</TD><TD>Category</TD></TR> |
<TR align=center><TD>Client<br>Num</TD><TD>Name</TD><TD>Client<br>Date</TD><TD>Category</TD></TR> |
||
Line 1,570: | Line 1,610: | ||
<TR><TD align=right>2</TD><TD>Best Foods</TD><TD>2011-02-02 12;33</TD><TD>food</TD></TR> |
<TR><TD align=right>2</TD><TD>Best Foods</TD><TD>2011-02-02 12;33</TD><TD>food</TD></TR> |
||
<TR><TD align=right>5</TD><TD>Dawnridge Winery</TD><TD>2008-06-18 22;16</TD><TD>wine</TD></TR></TABLE> |
<TR><TD align=right>5</TD><TD>Dawnridge Winery</TD><TD>2008-06-18 22;16</TD><TD>wine</TD></TR></TABLE> |
||
---- Name Sequence ----<br /> |
|||
<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0> |
|||
<TR align=center><TD>Client<br>Num</TD><TD>Name</TD><TD>Client<br>Date</TD><TD>Category</TD></TR> |
|||
<TR><TD align=right>4</TD><TD>Back 40 Equipment</TD><TD>2009-09-18 20:18</TD><TD>farm</TD></TR> |
|||
<TR><TD align=right>2</TD><TD>Best Foods</TD><TD>2011-02-02 12;33</TD><TD>food</TD></TR> |
|||
<TR><TD align=right>5</TD><TD>Dawnridge Winery</TD><TD>2008-06-18 22;16</TD><TD>wine</TD></TR> |
|||
<TR><TD align=right>3</TD><TD>Floral Designs</TD><TD>2010-10-14 09:16</TD><TD>flowers</TD></TR> |
|||
<TR><TD align=right>1</TD><TD>Home Sales</TD><TD>2012-01-01 10;20</TD><TD>broker</TD></TR></TABLE> |
|||
---- Date Sequence ----<br /> |
---- Date Sequence ----<br /> |
||
<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0> |
<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0> |