SQLite Data Reading Examples

The following uses the default database:

EsBrowser\Machine\MySQLite.db

as an example.

Customers can execute SQL queries directly using DB Browser for SQLite, C#, Python, or other programs that support SQLite.

Example 1: Read the Latest Measurement Records

SELECT
    num,
    元素名称,
    类型,
    add_time,
    工件名称,
    物料号,
    批号,
    产品SN,
    公差状态
FROM 测量主表
ORDER BY num DESC
LIMIT 20;

This can be used to read the latest 20 measurement elements completed.

For example, the result may be:

1003 Angle 1 Angle 2026-09-24 10:25:321002 Distance 1 Distance 2026-09-24 10:25:301001 Circle 1 Circle 2026-09-24 10:25:28

The num in the database increments with each new record, so it can also be used for incremental reading.

Example 2: Read Detailed Measurement Results of an Element

Suppose you need to read the main table number:

1001

The corresponding detail measurement data:

SELECT
    属性,
    测量值,
    标准值,
    上公差,
    下公差,
    误差,
    判定
FROM 测量从表
WHERE 主表num = 1001;

If 1001 is Circle 1, the result may be similar to:

Attribute Measured value Standard value Upper tolerance Lower tolerance Error JudgmentDiameter 10.002 10.000 0.010 -0.010 0.002 OKCenter X 25.123Center Y 18.456

A circle element usually corresponds to multiple measurement attributes, and these attributes are stored separately in the measurement detail table.

Example 3: Read the Main Table and Measurement Results in One Query

You can also join the main table and the detail table directly:

SELECT
    M.num,
    M.元素名称,
    M.类型,
    M.add_time,
    D.属性,
    D.测量值,
    D.标准值,
    D.上公差,
    D.下公差,
    D.误差,
    D.判定
FROM 测量主表 AS M
LEFT JOIN 测量从表 AS D
    ON D.主表num = M.num
ORDER BY M.num DESC;

This way, a third-party system can obtain, in one query:

What this element is + what it measured + what the measurement result is

For example:

1001 Circle 1 Circle Diameter 10.0021001 Circle 1 Circle Center X 25.1231001 Circle 1 Circle Center Y 18.4561002 Distance 1 Distance Distance 15.0041003 Angle 1 Angle Angle 89.998

Example 4: Query by Product SN

If a customer manages workpieces by product serial number, you can query by:

产品SN

SELECT
    num,
    元素名称,
    类型,
    add_time,
    产品SN
FROM 测量主表
WHERE 产品SN = 'SN202609240001'
ORDER BY num;

Then query the corresponding measurement detail table based on the returned num.

The main table actually stores product information such as the material number, batch number, and product SN.

Example 5: Query All Measurement Results by Batch Number

SELECT
    M.批号,
    M.产品SN,
    M.元素名称,
    D.属性,
    D.测量值,
    D.判定,
    M.add_time
FROM 测量主表 AS M
JOIN 测量从表 AS D
    ON D.主表num = M.num
WHERE M.批号 = 'BATCH-001'
ORDER BY M.add_time;

This approach is suitable for MES or QMS to query all measurement data of a production batch.

Example 6: Incrementally Read New Data

If a customer's system needs to continuously read the data newly generated by Easson2D, you can record the largest num already read last time.

For example, the last time you already read up to:

num = 1000

The next time, you only need to query:

SELECT *
FROM 测量主表
WHERE num > 1000
ORDER BY num;

After reading, record the new maximum num.

This avoids scanning the entire database every time.

num is the auto-increment number of the main table and can be used as an incremental read cursor for the local database. If the database is recreated, the numbers restart, so for long-term cross-database identification, you can also combine it with the record identifier GUID.

Example 7: Read Image Information

If measurement image saving is enabled, you can query:

SELECT
    主表num,
    结果图,
    编号,
    x轴,
    y轴,
    z轴,
    放大倍率
FROM 测量附表
WHERE 主表num = 1001;

The database does not directly store the image content, but stores the image file information.

The actual JPEG images are saved in the:

*2D_Data\EsBasic\Year\Month*

directory.

A More Complete Customer Integration Example

Suppose a customer needs to read all measurement results of the workpiece:

SN202609240001

You can first execute:

SELECT
    M.num,
    M.元素名称,
    M.类型,
    D.属性,
    D.MeasuredValue,
    D.标准值,
    D.上公差,
    D.下公差,
    D.误差,
    D.判定
FROM 测量主表 AS M
JOIN 测量从表 AS D
    ON D.主表num = M.num
WHERE M.产品SN = 'SN202609240001'
ORDER BY M.num;

This gives a result similar to:

Circle 1Diameter 10.002Center X 25.123Center Y 18.456

Distance 1Distance 15.004

Angle 1Angle 89.998

It is recommended that third-party systems give priority to the MeasuredValue numeric field in the database if further numeric calculation is needed, instead of relying only on the formatted "measured value" string in the interface. The source code indeed additionally saves the auxiliary fields MeasuredValue, AttributeId, LengthScale, and IsAngle.