xdl-database 0.1.1

Database connectivity module for XDL - supports PostgreSQL, MySQL, DuckDB, SQLite, ODBC, Redis, and more
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124

; ODBC SQL Server Example
; Demonstrates connecting to SQL Server via ODBC and querying data

PRO odbc_sqlserver_example
    ; Create a database object
    objdb = OBJ_NEW('XDLdbDatabase')

    ; ODBC Connection string for SQL Server
    ; Format: DRIVER={driver_name};SERVER=server;DATABASE=dbname;UID=user;PWD=password
    ;
    ; Common ODBC drivers:
    ; - SQL Server: "ODBC Driver 17 for SQL Server" or "SQL Server"
    ; - PostgreSQL: "PostgreSQL Unicode"
    ; - MySQL: "MySQL ODBC 8.0 Driver"
    ; - Oracle: "Oracle in OraClient19Home1"
    ; - SQLite: "SQLite3 ODBC Driver"

    conn_str = 'DRIVER={ODBC Driver 17 for SQL Server};' + $
               'SERVER=localhost;' + $
               'DATABASE=TestDB;' + $
               'UID=sa;' + $
               'PWD=YourPassword123;' + $
               'TrustServerCertificate=yes'

    PRINT, 'Connecting to SQL Server via ODBC...'
    objdb->Connect, CONNECTION=conn_str

    ; Check connection
    IF objdb->IsConnected() THEN BEGIN
        PRINT, 'Successfully connected!'
    ENDIF ELSE BEGIN
        PRINT, 'Connection failed!'
        RETURN
    ENDELSE

    ; Create a test table
    PRINT, ''
    PRINT, 'Creating test table...'
    create_sql = 'CREATE TABLE Products (' + $
                 'ProductID INT PRIMARY KEY, ' + $
                 'ProductName NVARCHAR(100), ' + $
                 'Category NVARCHAR(50), ' + $
                 'Price DECIMAL(10,2))'

    ; Drop table if exists first
    objdb->ExecuteCommand, 'DROP TABLE IF EXISTS Products'
    objdb->ExecuteCommand, create_sql

    ; Insert sample data
    PRINT, 'Inserting sample data...'
    objdb->ExecuteCommand, "INSERT INTO Products VALUES (1, 'Laptop', 'Electronics', 1299.99)"
    objdb->ExecuteCommand, "INSERT INTO Products VALUES (2, 'Mouse', 'Accessories', 29.99)"
    objdb->ExecuteCommand, "INSERT INTO Products VALUES (3, 'Keyboard', 'Accessories', 79.99)"
    objdb->ExecuteCommand, "INSERT INTO Products VALUES (4, 'Monitor', 'Electronics', 349.99)"

    ; Query all products
    PRINT, ''
    PRINT, 'All Products:'
    PRINT, '-------------------------------------------'
    recordset = objdb->ExecuteSQL('SELECT * FROM Products ORDER BY Price DESC')

    ; Display results
    n_rows = recordset->RowCount()
    PRINT, 'Found ' + STRTRIM(n_rows,2) + ' products'

    data = recordset->GetData()
    PRINT, data

    ; Query by category
    PRINT, ''
    PRINT, 'Electronics Products:'
    PRINT, '-------------------------------------------'
    electronics_query = "SELECT ProductName, Price FROM Products WHERE Category = 'Electronics'"
    elec_recordset = objdb->ExecuteSQL(electronics_query)
    elec_data = elec_recordset->GetData()
    PRINT, elec_data

    ; Calculate statistics
    PRINT, ''
    PRINT, 'Price Statistics:'
    PRINT, '-------------------------------------------'
    stats_query = 'SELECT ' + $
                  'COUNT(*) as ProductCount, ' + $
                  'AVG(Price) as AvgPrice, ' + $
                  'MIN(Price) as MinPrice, ' + $
                  'MAX(Price) as MaxPrice ' + $
                  'FROM Products'
    stats_recordset = objdb->ExecuteSQL(stats_query)
    stats_data = stats_recordset->GetData()
    PRINT, stats_data

    ; Update a product
    PRINT, ''
    PRINT, 'Updating product price...'
    update_sql = "UPDATE Products SET Price = 1199.99 WHERE ProductID = 1"
    rows_affected = objdb->ExecuteCommand(update_sql)
    PRINT, 'Rows updated:', rows_affected

    ; Verify update
    verify_query = "SELECT ProductName, Price FROM Products WHERE ProductID = 1"
    verify_recordset = objdb->ExecuteSQL(verify_query)
    verify_data = verify_recordset->GetData()
    PRINT, 'Updated product:', verify_data

    ; Cleanup
    verify_recordset->Destroy()
    stats_recordset->Destroy()
    elec_recordset->Destroy()
    recordset->Destroy()

    ; Clean up table
    PRINT, ''
    PRINT, 'Cleaning up...'
    objdb->ExecuteCommand, 'DROP TABLE Products'

    objdb->Disconnect()
    OBJ_DESTROY, objdb

    PRINT, ''
    PRINT, 'ODBC example completed successfully!'
ENDPRO

; Run the example
odbc_sqlserver_example