import TabItem from "@theme/TabItem"; import Tabs from "@theme/Tabs";

Simple SQL

SQL is easy to learn. Our SQL engine is mostly based on MySQL 5.7, albeit some changes may exist. For precise syntax and differences, see SQL compatibility. When a transaction happens, a transaction_id is generated for each data.

Following are some instances of typical development scenarios:Data Using

Data Querying Using SDK

You can use the query() method to receive a list of results right away. For example, if you want to look up books by the author "Shermin Voshmgir.

<Tabs defaultValue="Python" values={[ { label: "Python", value: "Python" }, { label: "JavaScript", value: "JavaScript" }, { label: "Golang", value: "Golang" }, ]}

cURL

curl --location 'https://api.xian.glitter.link/blockved/glitterchain/index/sql/query' \
--header 'Content-Type: application/json' \
--data '{
    "sql":"SELECT * FROM  bookshop.book WHERE author = \"Shermin Voshmgir\""
}'

Python

sql = prepare_sql("SELECT * FROM bookshop.book WHERE author=%s", 'Shermin Voshmgir')
db = client.db(mk)
db.query(sql)

const sql = "SELECT * FROM bookshop.book WHERE author=?";
const keyWords = ["Shermin Voshmgir"]
const db = client.db(mk)
await db.query(sql, keyWords)

sql, _ := sqlutil.EscapeSQL("select * from bookshop.book  WHERE author=%?", "Shermin Voshmgir")
resp, err := glitterClient.Query(ctx, sql)

Example of the SDK query() method's return structure.

Querying data with the SDK's query() method in Python or JavaScript returns a list of map (dictionary) structures.

[
    {
        "_tx_id":"",
        "author":"Springer",
        "category":"science",
        "id":1,
        "name":"Theoretische Physik 2- Nichtrelativistische Quantentheorie",
        "price":100,
        "public_at":2005,
        "stock":10
    }
]

Recommended Practices

Simple Query

You can use the SELECT... command. FROM .... To retrieve all data from the bookshop. book table, use the following statement.

<Tabs defaultValue="Python" values={[ { label: "Python", value: "Python" }, { label: "JavaScript", value: "JavaScript" }, { label: "Golang", value: "Golang" }, ]}

cURL

curl --location 'http://api.xian.glitter.link/blockved/glitterchain/index/sql/query' \
--header 'Content-Type: application/json' \
--data '{
    "sql":"SELECT * FROM  bookshop.book"
}'

Python

sql = "SELECT * FROM bookshop.book"
db = client.db(mk)
db.query(sql)

const sql = "SELECT * FROM bookshop.book";
const db = client.db(mk)
await db.query(sql)

sql = "SELECT * FROM bookshop.book"
resp, err := glitterClient.Query(ctx, sql)

Filtering Results

If the number of returned results is excessive and not all of them are relevant, you can use the WHERE statement to filter the results and identify the appropriate portion.

To find books with the category "web3" among several books, for example.

<Tabs defaultValue="Python" values={[ { label: "Python", value: "Python" }, { label: "JavaScript", value: "JavaScript" }, { label: "Golang", value: "Golang" }, ]}

cURL

curl --location 'http://api.xian.glitter.link/blockved/glitterchain/index/sql/query' \
--header 'Content-Type: application/json' \
--data '{
    "sql":"SELECT * FROM  bookshop.book WHERE category = \"web3\""
}'

Python

sql = prepare_sql("SELECT * FROM bookshop.book WHERE category=%s", 'web3')
db = client.db(mk)
db.query(sql)

``` js const sql = "SELECT * FROM bookshop.book WHERE category=?"; const keyWords = ["web3"] const db = client.db(mk) await db.query(sql, keyWords) ``` ```go sql, _ := sqlutil.EscapeSQL("SELECT * FROM bookshop.book WHERE category=%?", "web3") resp, err := glitterClient.Query(ctx, sql) ``` To locate books with the author "Shermin Voshmgir" and the category "web3" among many books, for example.

<Tabs defaultValue="Python" values={[ { label: "Python", value: "Python" }, { label: "JavaScript", value: "JavaScript" }, { label: "Golang", value: "Golang" }, ]}

cURL

curl --location 'http://api.xian.glitter.link/blockved/glitterchain/index/sql/query' \
--header 'Content-Type: application/json' \
--data '{
    "sql":"SELECT * FROM  bookshop.book WHERE author = \"Shermin Voshmgir\" AND category = \"web3\""
}'

Python

sql = "SELECT * FROM  bookshop.book WHERE author = 'Shermin Voshmgir' AND category = 'web3'"
db = client.db(mk)
db.query(sql)

``` js const sql = "SELECT * FROM bookshop.book WHERE author = 'Shermin Voshmgir' AND category = 'web3'"; const db = client.db(mk) await db.query(sql) ``` ```go sql = "SELECT * FROM bookshop.book WHERE author = 'Shermin Voshmgir' AND category = 'web3'" resp, err := glitterClient.Query(ctx, sql) ``` For example, to identify books with prices between $10 and $20 among a large number of books, use `BETWEEN AND`.

<Tabs defaultValue="Python" values={[ { label: "Python", value: "Python" }, { label: "JavaScript", value: "JavaScript" }, { label: "Golang", value: "Golang" }, ]}

cURL

curl --location 'http://api.xian.glitter.link/blockved/glitterchain/index/sql/query' \
--header 'Content-Type: application/json' \
--data '{
    "sql":"SELECT * FROM  bookshop.book WHERE price BETWEEN 10 AND 20"
}'

Python

sql = "SELECT * FROM  bookshop.book WHERE price BETWEEN 10 AND 20"
db = client.db(mk)
db.query(sql)

``` js const sql = "SELECT * FROM bookshop.book WHERE price BETWEEN 10 AND 20"; const db = client.db(mk) await db.query(sql) ``` ```go sql = "SELECT * FROM bookshop.book WHERE price BETWEEN 10 AND 20" resp, err := glitterClient.Query(ctx, sql) ```

Sorting Results

The ORDER BY statement can be used to sort the query results in the appropriate order.

To find the most recently published books, for example, use the following SQL statement to sort the data in the book table by the public_at column in descending (DESC) order.

<Tabs defaultValue="Python" values={[ { label: "Python", value: "Python" }, { label: "JavaScript", value: "JavaScript" }, { label: "Golang", value: "Golang" }, ]}

cURL

curl --location 'http://api.xian.glitter.link/blockved/glitterchain/index/sql/query' \
--header 'Content-Type: application/json' \
--data '{
    "sql":"SELECT * FROM  bookshop.book ORDER BY public_at DESC LIMIT 10"
}'

Python

sql = "SELECT * FROM bookshop.book ORDER BY public_at DESC LIMIT 10"
db = client.db(mk)
db.query(sql)

const sql = "SELECT * FROM bookshop.book ORDER BY public_at DESC LIMIT 10"
const db = client.db(mk)
await db.query(sql)

sql = "SELECT * FROM bookshop.book ORDER BY public_at DESC LIMIT 10"
resp, err := glitterClient.Query(ctx, sql)

Pagination

LIMIT... OFFSET... can be used to return only a subset of the results. Note the arrangement of LIMIT and OFFSET for pagination.

<Tabs defaultValue="Python" values={[ { label: "Python", value: "Python" }, { label: "JavaScript", value: "JavaScript" }, { label: "Golang", value: "Golang" }, ]}

cURL

curl --location 'http://api.xian.glitter.link/blockved/glitterchain/index/sql/query' \
--header 'Content-Type: application/json' \
--data '{
    "sql":"SELECT * FROM  bookshop.book LIMIT 10 OFFSET 10"
}'

Python

sql = "SELECT * FROM bookshop.book LIMIT 10 OFFSET 10"
db = client.db(mk)
db.query(sql)

const sql = "SELECT * FROM bookshop.book LIMIT 10 OFFSET 10"
const db = client.db(mk)
await db.query(sql)

sql = "SELECT * FROM bookshop.book LIMIT 10 OFFSET 10"
resp, err := glitterClient.Query(ctx, sql)

Function Usage

You can use count() function to calculate the number of results returned.

<Tabs defaultValue="Python" values={[ { label: "Python", value: "Python" }, { label: "JavaScript", value: "JavaScript" }, { label: "Golang", value: "Golang" }, ]}

cURL

curl --location 'http://api.xian.glitter.link/blockved/glitterchain/index/sql/query' \
--header 'Content-Type: application/json' \
--data '{
    "sql":"SELECT COUNT(*) AS counts FROM  bookshop.book"
}'

Python

sql = "SELECT COUNT(*) AS counts FROM bookshop.book"
db = client.db(mk)
db.query(sql)

const sql = "SELECT COUNT(*) AS counts FROM bookshop.book"
const db = client.db(mk)
await db.query(sql)

sql = "SELECT COUNT(*) AS counts FROM bookshop.book"
resp, err := glitterClient.Query(ctx, sql)

You can use Concat() function to concatenate multiple columns into a single value.

<Tabs defaultValue="Python" values={[ { label: "Python", value: "Python" }, { label: "JavaScript", value: "JavaScript" }, { label: "Golang", value: "Golang" }, ]}

cURL

curl --location 'http://api.xian.glitter.link/blockved/glitterchain/index/sql/query' \
--header 'Content-Type: application/json' \
--data '{
    "sql":"SELECT Concat(name, author) AS name_author FROM  bookshop.book"
}'

Python

sql = "SELECT Concat(name, author) AS name_author FROM bookshop.book"
db = client.db(mk)
db.query(sql)

const sql = "SELECT Concat(name, author) AS name_author FROM bookshop.book"
const db = client.db(mk)
await db.query(sql)

sql = "SELECT Concat(name, author) AS name_author FROM bookshop.book"
resp, err := glitterClient.Query(ctx, sql)

Data Querying Using SDK​

cURL​

Python​

Example of the SDK query() method's return structure.​

Recommended Practices​

Simple Query​

cURL​

Python​

Filtering Results​

cURL​

Python​

cURL​

Python​

cURL​

Python​

Sorting Results​

cURL​

Python​

Pagination​

cURL​

Python​

Function Usage​

cURL​

Python​

cURL​

Python​

Data Querying Using SDK

cURL

Python

Example of the SDK query() method's return structure.

Recommended Practices

Simple Query

cURL

Python

Filtering Results

cURL

Python

cURL

Python

cURL

Python

Sorting Results

cURL

Python

Pagination

cURL

Python

Function Usage

cURL

Python

cURL

Python