SQL Update, Insert, Delete

Looking for code examples for update, delete and insert for MS SQL from instant SQL.

 

Any help is appreciated.

Hi Jimk,

Here is a very basic code that does CRUD on MS SQL from RM/COBOL using Instant SQL:

*******************************
** Requires an ODBC DataSource called MSLEGAJOS
** pointing to a valid database
** containing a table CLIENTES with three fields
** Cliente numeric
** Nombre text
** Email text
*******************************
** ProRM www.prorm.com
*******************************
ID DIVISION.
PROGRAM-ID. ABMSQLA.
DATA DIVISION.
WORKING-STORAGE SECTION.
COPY "lisqlall.cpy".
01 CLIENTE PIC 9(5).
01 NOMBRE PIC X(30).
01 EMAIL PIC X(30).
01 SENTENCIA PIC X(200).
01 EX PIC 99.
01 CONFIRMA PIC X.
88 CONFIRMA-SI VALUES ARE "S", "s".
01 BASE PIC X(30) VALUE "MSLEGAJOS".
01 TABLA PIC X(30) VALUE "dbo.CLIENTES".
01 EXISTEONO PIC X.
88 EXISTE VALUE "S" WHEN FALSE "N".
PROCEDURE DIVISION.
PRORM.
CONFIRMADO.
DISPLAY " " ERASE.
SQL CONNECT DATASOURCE sql-ConnectionHandle
BASE.

IF NOT sql-OK
DISPLAY "Could not connect to the database"
line 17 position 10 reverse
ACCEPT CONFIRMA LINE 17 POSITION 75
GO TO PRORM
END-IF.


FORMULARIO.
display "Customer" line 5 position 10 erase
"Name" line 7 position 10
"Email" line 9 position 10.
a-cliente.
accept cliente line 5 position 30
on exception ex go to fin.
MOVE SPACES TO SENTENCIA.
STRING "SELECT CLIENTE, NOMBRE, EMAIL FROM "
DELIMITED BY SIZE,
TABLA DELIMITED BY " ",
" ", " " DELIMITED BY SIZE,
"WHERE CLIENTE = "
DELIMITED BY SIZE
CLIENTE DELIMITED BY size
INTO SENTENCIA.
SQL PREPARE QUERY sql-QueryHandle
sql-ConnectionHandle SENTENCIA.
SQL START QUERY sql-QueryHandle.
SQL FETCH ROW sql-QueryHandle.
IF sql-OK
SQL GET DATA sql-QueryHandle
"NOMBRE" NOMBRE OMITTED
"EMAIL" EMAIL OMITTED
set existe to true
ELSE MOVE SPACES TO NOMBRE
MOVE SPACES TO EMAIL
set existe to false
END-IF.
SQL END QUERY sql-QueryHandle.
DISPLAY NOMBRE LINE 7 POSITION 30.
DISPLAY EMAIL LINE 9 POSITION 30.

a-nombre.
accept nombre CONTROL "UPDATE" line 7 position 30
accept email CONTROL "UPDATE" line 9 position 30
on exception ex go to a-cliente.
display "Write <F2> Delete <F3>"
line 10 position 10 reverse.
accept confirma line 10 position 70
on exception ex
evaluate ex
when 2 perform grabo thru fin-grabo
when 3 perform borro thru fin-borro
end-evaluate.
go to formulario.

grabo.
move spaces to sentencia.

if not existe
STRING "INSERT INTO " DELIMITED BY SIZE,
TABLA DELIMITED BY " ",
" VALUES(" DELIMITED BY SIZE,
CLIENTE DELIMITED BY size,
", '" DELIMITED BY SIZE,
NOMBRE DELIMITED BY SIZE,
"', '" DELIMITED BY SIZE,
EMAIL DELIMITED BY SIZE,
" ')"
DELIMITED BY SIZE
INTO SENTENCIA
else
STRING "UPDATE " DELIMITED BY SIZE,
TABLA DELIMITED BY " ",
" SET NOMBRE = '" DELIMITED BY SIZE,
NOMBRE DELIMITED BY SIZE,
"' , EMAIL = '" DELIMITED BY SIZE,
EMAIL DELIMITED BY SIZE,
"' WHERE CLIENTE = "
DELIMITED BY SIZE
CLIENTE DELIMITED BY size
INTO SENTENCIA
END-IF.

SQL PREPARE QUERY sql-QueryHandle
sql-ConnectionHandle SENTENCIA.
IF NOT sql-OK
DISPLAY "Mal PREPARE"
line 17 position 10 reverse
DISPLAY SENTENCIA
ACCEPT CONFIRMA LINE 17 POSITION 75
END-IF.

SQL START QUERY sql-QueryHandle.
IF NOT sql-OK
DISPLAY "Mal START QUERY"
line 17 position 10 reverse
ACCEPT CONFIRMA LINE 17 POSITION 75
END-IF.

SQL END QUERY sql-QueryHandle.
fin-grabo.
exit.

borro.
move spaces to sentencia.
STRING "DELETE * FROM " DELIMITED BY SIZE,
TABLA DELIMITED BY " ",
" WHERE CLIENTE = "
DELIMITED BY SIZE
CLIENTE DELIMITED BY size
INTO SENTENCIA.
SQL PREPARE QUERY sql-QueryHandle
sql-ConnectionHandle SENTENCIA.
SQL START QUERY sql-QueryHandle.
SQL END QUERY sql-QueryHandle.
fin-borro.
exit.

FIN.
SQL SHUTDOWN.
STOP RUN.


Regards,