User Guide

SelimSql jdbc connection driver supports the following data definition language(DDL), data manipulation language(DML) and query language statements.

Table Of Contents

Connection Driver Class And Url

Jdbc Driver Class:

selimsql.jdbc.DbDriver

Jdbc Url:

jdbc:selimsql:<DbTypeOrOperation>:<DbPathName>

--DbTypeOrOperation:
file: Connect to a file based database.

//ServerIp:ServerPort(9933):
Connect to host based database.

fileNewDatabase: Create a new database.

fileDropDatabase: Drop the database.

--DbPathName:
Database location and name.

--Example for connect:
jdbc:selimsql:file:/SSqlData/Stok
jdbc:selimsql://192.168.2.3:9933/MyDb

--Example for new database:
jdbc:selimsql:fileNewDatabase:D:/SSqlData/MyDb

Data Definition Statements

-Create Table

CREATE TABLE <TableName> ( <ColumnDefinition> [, ...])

 --ColumnDefinition:
 <ColumnName> <DataType> [(<ColumnSize>[, <Precision>]) [NOT NULL]

Create Table Example:

CREATE TABLE Product(
 Id INTEGER NOT NULL,
 Name VARCHAR(20) NOT NULL,
 Description VARCHAR(50),
 Price DECIMAL(15, 2) NOT NULL,
 ProductDate DATE NOT NULL,
 Status CHAR NOT NULL,
 Comment LONGTEXT,
 Photo BLOB)

-Drop Table

DROP TABLE <TableName>

-Create Index

CREATE [UNIQUE] INDEX <IndexName> ON <TableName>(<ColumnName> [, ...])

--Example1:
CREATE UNIQUE INDEX ProductPk ON Product(ProductId)

--Example2:
CREATE INDEX ProductDateIdx ON Product(ProductDate, Status)

-Drop Index

DROP INDEX <IndexName>

-Rename Table

RENAME <TableName> TO <NewTableName>

-Alter Table

CREATE TABLE YourNewTableName
(Id INTEGER NOT NULL,
 Column2 type,
 Column3 type,
 ...
)

CREATE UNIQUE INDEX YourNewTableNamePk ON YourNewTableName(Id)

insert into YourNewTable
select id, column2, column3, ... , 'NewData' as NewColumn from YourOldTable

Drop table YourOldTable

Rename YourNewTable to YourOldTable

-Create Sequence

Create SEQUENCE <SEQ_NAME>
 [STARTVALUE 1] [MINVALUE 1] [MAXVALUE 9999999999999999999] [INCREMENT 1] [CYCLE]

--Example:
Create SEQUENCE Seq_Order_Id;

-Drop Sequence

Drop SEQUENCE <SEQ_NAME>

--Example:
Drop SEQUENCE Seq_Order_Id;

-Create User

CREATE USER <UserName> BY PASSWORD <UserPassword>

-Alter User Pass

ALTER USER <UserName> BY PASSWORD <UserPassword>

-Drop User

DROP USER <UserName>

-Database Grants

GRANT grant1[,grant2,..] TO <UserName>

--Database Grants:
ALL, NONE, BUILDTABLE, DROPTABLE, RENAMETABLE, BUILDUSER, DROPUSER, ALTERUSER

--Example:
GRANT ALL TO admin

-Database Table Grants

GRANT grant1[,grant2,..] ON <TableName> TO <UserName>

--Database Table Grants:
ALL, NONE, ALTERTABLE, BUILDINDEX, DROPINDEX, SELECT, INSERT, UPDATE, DELETE

--Example:
GRANT Select,Update,Insert ON Customer TO admin

Data Manipulation Statements

-Insert Record

INSERT INTO <Table>[(<Column> [,...] )]
{ VALUES(<Expression> [,...]) | <SelectStatement> };

--Example1:
INSERT INTO Product(ProductId, Name, Description, Price, ProductDate, Status)
VALUES(1, 'Product1', null, 12.35, Today(), 'A');

--Example2:
INSERT INTO Product
VALUES(2, 'Product2', null, 23.68, Today(-1), 'P');

--Example3:
INSERT INTO Product
select * from ProductOther where ProductId <= 10;

-Update Record

UPDATE <Table> SET <Column> = <Expression> [, ...] [WHERE <Expression>];

--Example:
UPDATE Product
SET Description = Name + ' Desc.',
    Price = Price * 0.95
WHERE Description = NULL

-Delete Record

DELETE FROM <Table> [WHERE <Expression>];

--Example:
DELETE FROM Product
WHERE Status = 'P'

Select Statement

SELECT { <SelectExpression> | <Table>.* | * } [, ...]
FROM <TableList>
[WHERE <Expression>]
[GROUP BY <Expression> [, ...]]
[ORDER BY <OrderExpression> [, ...]]

--Expression:
<Condition> [ { OR | AND } <Condition>]

--Condition:
{ <Value> [|| <Value>]
 | <Value> { = | < | <= | > | >= | <> | != } <Value>
 | <Value> IS [NOT] NULL
 | <Value> [NOT] LIKE <Value> }

 --Value:
[ + | - ] { <Term> [ { + | - | * | / | || } <Term> ]
 | ( <Condition> )
 | <function>([<parameter>] [,...])

--Term:
{ 'string' | number | floatingpoint
 | [<Table>.]<Column> | TRUE | FALSE | NULL }

Select Sql Examples

--Example1:
Select * from Product
where ProductId <= 2;

ProductId Name     Description    Price  ProductDate  Status
========= ======== ============== ====== ============ ======
        1 Product1 Product1 Desc. 11,73  07.11.2011	  A
        2 Product2 Product2 Desc. 22,50	 08.11.2011	  P
...

--Example2:
SELECT ProductDate, count(ProductId) AS Number
FROM Product
GROUP BY ProductDate
ORDER BY 1 DESC;

--Example3:
select o.OrderId, o.OrderDate, p.Name as Product, o.OrderNumber,
 (o.OrderNumber * o.UnitPrice) as Price,
 (c.Name + ' ' + c.Surname) as Customer
from Order o, Customer c, Product p
where o.OrderDate >= DateFromStr('01.06.2010','dd.MM.yyyy')
  and o.OrderDate <= DateFromStr('27.10.2011','dd.MM.yyyy')
  and c.CustomerId = o.CustomerId
  and p.ProductId = o.ProductId
order by 1, 2;

Sql Functions

Function Name
Function Parameters
Decription
Aggregate Functions
Count
Scanned column or object of count
Aggregate function. Example: Count(Id)
Sum
Scanned column or object of sum
Aggregate function. Example: Sum(Price)
Min
Scanned column or object of min
Aggregate function. Example: Min(OrderDate)
Max
Scanned column or object of max
Aggregate function. Example: Max(Number)
Datetime Functions
Today, CurDate
Prm1 optional.
Date or DifDay
Current or different date.
Example1: Today()
Example2: CurDate(); --Today
Example3: CurDate(-1); --Yesterday
CurDateTime
Prm1 optional.
Date or DifDay
Current or different datetime.
Example1: CurDateTime()
Example2: CurDateTime(-1); --Yesterday time
Example3: CurDateTime(ATimeStamp);
 --ATimeStamp time by no milisecond
Now, CurDateTimeStamp
Prm1 optional.
DifDay
Current or different datetime stamp.
Example1: Now()
Example2: CurDateTimeStamp(); --Now
Example3: CurDateTimeStamp(-5/24);
 --5 hour before
Year, Month, Day,
Hour, Minute, Second
-
Current Year, Month, Day,
Hour, Minute, Second
Example1: Year()
Example2: Hour()
WeekDay
Prm1:Number,
Prm2:Mod
Day of Week.
1:Monday, 2:Tuesday,.. 7:Sunday
Example: WeekDay()
DateFromStr
Prm1:DateStr,
Prm2:DateFormat optional
Date from string
Example:
DateFromStr('01.06.2014 10:48:23','dd.MM.yyyy HH:mm:ss')
DateDifMonth
Prm:DifMonth
Different month date.
Example: DateDifMonth(-3)
StrFromDate
Prm1:Date,
Prm2:DateFormat optional
String from date
Example:
StrFromDate(ProductDate,'dd/MM/yyyy'),
StrFromDate(Now(-7),'yyyyMMdd HH:mm:ss')
StrFromNumber
Prm1:Number,
Prm2:Format is optional
String from number
Example:
StrFromNumber(23419.2865, '#0.00');
 --Out: 23419.29
StrFromNumber(23419.2865, '#,##0.00');
 --Out: 23,419.29
Text Functions
StrLen
Prm:Text
Length of text. Example: StrLen(Name)
Trim
Prm:Text
Trim the text. Example: Trim(Description)
SubStr
Prm1:Text,
Prm2:StartPos,
Prm3:Length optional
Sub string from text.
Example: SubStr(DESCRIPTION, 1, 5)
Upper
Prm:Text
Upper the text. Example: Upper(Surname)
Lower
Prm:Text
Lower the text. Example: Lower(Address)
InStr
Prm1:stSource,
Prm2:stSource, stLook
InStr search the stLook in stSource
InStrSet
Prm1:stSourceSet,
Prm2:stLook
InStrSet search the stLook in stSourceSet
Example:
InStrSet('1,3,5,7-9,11', '8')
IsNull
Prm1:Object,
Prm2:NewValue
If Object is null, use NewValue.
Example: IsNull(Status, 1)
IfValue
Prm1:leftValue,
Prm2:condition,
Prm3:rightValue,
Prm4:thenValue,
Prm5:elseValue
IfValue check the condition,
get thenValue or elseValue
Example:
IfValue(OperationType, '=', '+', 'INCOME', 'EXPENSE')
CaseValue
Prm1:Value,
Prm2:EqualValue1,
Prm3:ThenValue1,
[EqualValue2,ThenValue2,..]
PrmN:OtherValue
CaseValue check the equal values
and get the then values or other value.
Example:
CaseValue(MaleFemale, 'M', 'Male', 'F', 'Female', 'Unknown')
Various Functions
Mod
Prm1:Number,
Prm2:Mod
Mod of the number.
Example: Mod(Id, 3)
Round
Prm1:Number,
Prm2:Precision is optional
Round the number.
Example:
Round(413.2865); --413.29
Floor
Prm1:Number,
Prm2:Precision is optional
Floor the number.
Example:
Floor(413.2865, 3); --413.286
BinaryFromFile
Prm:Url or FileName.
Note: FileName is valid
for local database.
Read the data bytes from file or url.
Example:
update Product
set Photo =
    BinaryFromFile('http://www.mysite.com/Note1.png')
where id = 1
Sequence Functions
SequenceNextValue
Prm: SequenceName
Next Value of SequenceName.
Example1:
Select SequenceNextValue(SequenceName)
  From _DB_Sequence
 Where SequenceName = 'SEQ_ORDER_ID';

Example2:
Insert Into Order(Id, Desc)
Values(SequenceNextValue('SEQ_ORDER_ID'), 'Desc1');
SequenceCurrentValue
Prm: SequenceName
Current Value of SequenceName.
Example:
Select SequenceCurrentValue(SequenceName)
  From _DB_Sequence
 Where SequenceName = 'SEQ_ORDER_ID'
DateTime Generated Number Functions
CurDateNumber
-
Current date number.
Example: CurDateNumber(); --20150930
CurDateTimeNumber
-
Current date time number.
Example: CurDateTimeNumber();
 --20150930115213
CurTimeNumber
-
Current time number.
Example: CurTimeNumber(); --115306
CurDateTimeStampNumber
-
Current date time stamp number.
Example: CurDateTimeStampNumber();
 --20151001141304673
CurTimeStampNumber
-
Current time stamp number.
Example: CurTimeStampNumber();
 --140330215