The Project | About Us | Contribute | Donations | License

HOME

In this module, we will explore how to interface MUMPS with SQL databases. This is a crucial skill for integrating MUMPS applications with modern relational database systems, allowing for more robust data management and interoperability.

Objectives

Understand the basics of SQL and relational databases.
Learn how to connect MUMPS to an SQL database.
Perform basic SQL operations (CRUD) from MUMPS.
Handle data conversion between MUMPS and SQL.

Introduction to SQL and Relational Databases

What is SQL?

SQL (Structured Query Language) is a standard language for managing and manipulating relational databases. It allows you to perform various operations such as querying data, updating records, and managing database structures.

Key Concepts

Tables: Organized collections of data in rows and columns.
Rows: Individual records in a table.
Columns: Attributes or fields of the records.
Primary Key: A unique identifier for each record in a table.
Foreign Key: A field in one table that uniquely identifies a row in another table.

Basic SQL Commands

SELECT: Retrieve data from a database.
INSERT: Add new records to a table.
UPDATE: Modify existing records.
DELETE: Remove records from a table.

Connecting MUMPS to an SQL Database

Prerequisites

An SQL database (e.g., MySQL, PostgreSQL, SQL Server).
MUMPS environment set up with necessary libraries for SQL connectivity.

Steps to Connect

Install SQL Client Library: Ensure you have the appropriate SQL client library installed for your MUMPS environment.
Configure Connection Parameters: Set up the connection parameters such as hostname, port, database name, username, and password.
Establish Connection: Use MUMPS commands to establish a connection to the SQL database.

Example: Connecting to MySQL

; Define connection parameters
SET hostname = "localhost"
SET port = 3306
SET dbname = "testdb"
SET username = "root"
SET password = "password"

; Establish connection
SET conn = $$CONNECT^%SQL(hostname, port, dbname, username, password)
IF conn=0 WRITE "Connection failed",! QUIT
WRITE "Connected to MySQL database",!

Performing SQL Operations from MUMPS

SELECT Operation

Retrieve data from an SQL table and display it in MUMPS.

; Define SQL query
SET query = "SELECT * FROM users"

; Execute query
SET result = $$EXECUTE^%SQL(conn, query)

; Process result
FOR  SET row=$$FETCH^%SQL(result) QUIT:row=""  DO
. WRITE "User ID: ", $PIECE(row, "^", 1), " - Name: ", $PIECE(row, "^", 2), !

INSERT Operation

Add a new record to an SQL table from MUMPS.

; Define SQL query
SET query = "INSERT INTO users (id, name) VALUES (1, 'John Doe')"

; Execute query
SET status = $$EXECUTE^%SQL(conn, query)
IF status=0 WRITE "Insert failed",! QUIT
WRITE "Record inserted successfully",!

UPDATE Operation

Modify an existing record in an SQL table from MUMPS.

; Define SQL query
SET query = "UPDATE users SET name='Jane Doe' WHERE id=1"

; Execute query
SET status = $$EXECUTE^%SQL(conn, query)
IF status=0 WRITE "Update failed",! QUIT
WRITE "Record updated successfully",!

DELETE Operation

Remove a record from an SQL table from MUMPS.

; Define SQL query
SET query = "DELETE FROM users WHERE id=1"

; Execute query
SET status = $$EXECUTE^%SQL(conn, query)
IF status=0 WRITE "Delete failed",! QUIT
WRITE "Record deleted successfully",!

Handling Data Conversion

Data Types Mapping

When interfacing MUMPS with SQL databases, it's important to handle data type conversions properly. Below is a table mapping common MUMPS data types to SQL data types:

MUMPS Data Type	SQL Data Type
String	VARCHAR, TEXT
Integer	INT, BIGINT
Float	FLOAT, DOUBLE
Date/Time	DATETIME, TIMESTAMP

Example: Converting Data Types

; Convert MUMPS string to SQL VARCHAR
SET mumpsString = "Hello, World!"
SET sqlString = $$CONVERT^%SQL(mumpsString, "VARCHAR")

; Convert MUMPS integer to SQL INT
SET mumpsInt = 123
SET sqlInt = $$CONVERT^%SQL(mumpsInt, "INT")

Practical Exercise

Task

Connect to an SQL database.
Create a table named students with columns id (INT), name (VARCHAR), and age (INT).
Insert a few records into the students table.
Retrieve and display the records in MUMPS.
Update a record and then delete it.

Solution

; Step 1: Connect to SQL database
SET hostname = "localhost"
SET port = 3306
SET dbname = "school"
SET username = "root"
SET password = "password"
SET conn = $$CONNECT^%SQL(hostname, port, dbname, username, password)
IF conn=0 WRITE "Connection failed",! QUIT
WRITE "Connected to MySQL database",!

; Step 2: Create table
SET query = "CREATE TABLE students (id INT, name VARCHAR(50), age INT)"
SET status = $$EXECUTE^%SQL(conn, query)
IF status=0 WRITE "Table creation failed",! QUIT
WRITE "Table created successfully",!

; Step 3: Insert records
SET query = "INSERT INTO students (id, name, age) VALUES (1, 'Alice', 20)"
SET status = $$EXECUTE^%SQL(conn, query)
IF status=0 WRITE "Insert failed",! QUIT
SET query = "INSERT INTO students (id, name, age) VALUES (2, 'Bob', 22)"
SET status = $$EXECUTE^%SQL(conn, query)
IF status=0 WRITE "Insert failed",! QUIT
WRITE "Records inserted successfully",!

; Step 4: Retrieve and display records
SET query = "SELECT * FROM students"
SET result = $$EXECUTE^%SQL(conn, query)
FOR  SET row=$$FETCH^%SQL(result) QUIT:row=""  DO
. WRITE "ID: ", $PIECE(row, "^", 1), " - Name: ", $PIECE(row, "^", 2), " - Age: ", $PIECE(row, "^", 3), !

; Step 5: Update a record
SET query = "UPDATE students SET age=21 WHERE id=1"
SET status = $$EXECUTE^%SQL(conn, query)
IF status=0 WRITE "Update failed",! QUIT
WRITE "Record updated successfully",!

; Step 6: Delete a record
SET query = "DELETE FROM students WHERE id=2"
SET status = $$EXECUTE^%SQL(conn, query)
IF status=0 WRITE "Delete failed",! QUIT
WRITE "Record deleted successfully",!

Conclusion

In this module, you learned how to interface MUMPS with SQL databases. You now understand the basics of SQL, how to connect MUMPS to an SQL database, and how to perform basic SQL operations from MUMPS. This knowledge will enable you to integrate MUMPS applications with modern relational databases, enhancing data management capabilities and interoperability.

Interfacing with SQL Databases

Objectives

Introduction to SQL and Relational Databases

What is SQL?

Key Concepts

Basic SQL Commands

Connecting MUMPS to an SQL Database

Prerequisites

Steps to Connect

Example: Connecting to MySQL

Performing SQL Operations from MUMPS

SELECT Operation

INSERT Operation

UPDATE Operation

DELETE Operation

Handling Data Conversion

Data Types Mapping

Example: Converting Data Types

Practical Exercise

Task

Solution

Conclusion

MUMPS (M) Programming Course

Module 1: Introduction to MUMPS

Module 2: Basic Programming Concepts

Module 3: Working with Data

Module 4: Advanced Programming Concepts

Module 5: Database Management

Module 6: Interfacing and Integration

Module 7: Performance and Optimization

Module 8: Advanced Topics

Module 9: Project and Assessment