In previous blog posts (see links at the end), I described data integration scenarios where business users can access data in an ABAP system from an external consumer, using the ABAP SQL service and the ODBC driver for ABAP. In this scenario, can query data in exposed CDS view entities. So far, this was a read-only scenario, where SELECT statements were used.
Now, with the availability of writable CDS view entities, the SQL service can also be used to change data in an ABAP system. The first supported SQL DML statements that allow to change data in an SAP system using the ABAP SQL service are INSERT and DELETE.
In this blog post, I will describe an example where a writable ABAP CDS view entity is exposed, I will describe the supported INSERT and DELETE statement types and their restrictions, and provide a code sample that uses those statements on an exposed entity.
Prerequisites:
ABAP platform cloud 2508 or ABAP on-premise 2025 or higherODBC driver for ABAP version 01, or higher
The steps to expose a writable view entity are very similar to the steps described in this blog post for read-only entities. I will try to keep the description in this blog post similar and only outline the differences for writable CDS view entities.
Create a Writable CDS View Entity on Top of a Table
First, I create a test table for this blog post and a writable CDS view entity on top of it. In my case, the writable view entity is a simple projection of a database test table containing all table columns.
@EndUserText.label : ‘test table for SQL Service DMLs’
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zsql_write_tab {
key client : abap.clnt not null;
key itemnr : abap.int4 not null;
key idate : abap.datn not null;
@AbapCatalog.decfloat.outputStyle : #NORMAL
amount : abap.decfloat34;
}
@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: ‘writable view entity for SQL service’
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
serviceQuality: #X,
sizeCategory: #S,
dataClass: #MIXED
}
define writable view entity ZSQL_WRITE_VIEW
as select from zsql_write_tab as TestTable
{
key itemnr as ItemNr,
key idate as IDate,
amount as Amount
}
Create a service definition and binding containing the writable CDS view entity
As a second step, define a service definition exposing the writable view entity:
@EndUserText.label: ‘service def with writable view entity’
define service ZWRITE
provider contracts sql {
expose ZSQL_WRITE_VIEW as WriteView;
}
I create a service binding with the same name as the service definition and enable operations. Please note that I enabled operations SELECT, INSERT, and DELETE for this service binding:
Configure Privileged User Access with INSERT and DELETE Operations
Next, I define the user access to the writable CDS view entities. Again, the configuration of privileged user access has already been described in this blog post for a cloud system, or you can refer to the onprem documentation. INSERT and DELETE statements are currently only allowed for privileged user access.
So, essentially, you can follow the previous blog post. The only difference is that you now need to add INSERT and DELETE as allowed SQL view operations to the S_SQL_VIEW authorization object, for example, with the following properties.
SQL_SCHEMA -> ZWRITE
SQL_VIEW -> *
SQL_VIEWOP -> SELECT, INSERT, DELETE
Now, everything is set up to execute queries and INSERT and DELETE statements on our test entity.
I won’t repeat the description of how to install and configure the ODBC driver for ABAP here and for the next steps I will just assume that this is done and that you configured an ODBC DSN for your SAP system. For all details about installing and configuring the ODBC driver for ABAP, check my previous blog posts.
Now let’s turn to the SQL statements INSERT and DELETE and how they are supported in the SQL service.
The INSERT Statement in the SQL Dialect of the ABAP SQL Service
Compared to ABAP SQL, the SQL dialect of the SQL service has a few restrictions and specialties that you must consider.
INSERT … VALUES and INSERT … SELECT statements are supported.
The supported INSERT … VALUES statements have the following form:
INSERT INTO <schema>.<view> VALUES ( <expression1>, <expression2>, …)
Compared to other databases, currently there is no column list clause supported. The maximum number of expressions in the VALUES clause is the number of columns in the exposed entity. If you provide fewer expressions, the last columns in the writable entity will be set to their default database values during INSERT.
Expressions can be simple literals, expressions containing functions, or parameter markers ( ‘?’ ) for prepared statements.
For literals or expressions, the types need to be compatible with the DDIC data types of exposed entity. This requires CASTs in some cases.
INSERT … SELECT statements have the following form:
INSERT INTO <schema>.<view> <ABAP SQL service SQL query>
where <ABAP SQL service SQL query> is a SELECT statement in the SQL dialect of the SQL service.
Currently, the <ABAP SQL service SQL query> cannot contain common table expressions (CTEs starting with the keyword “WITH”).
Again, the number of columns in the result set of the SELECT statement must match the number of columns in the exposed entity, and the data types returned by the SELECT statement need to be compatible with the data types of the exposed entity.
The following INSERT statements are equivalent:
INSERT INTO <schema>.<view> VALUES ( <expression1>, <expression2>, …)
INSERT INTO <schema>.<view> SELECT <expression1>, <expression2>, … FROM SYS.DUMMY
The SYS.DUMMY trick can also be used to formulate some kind of simple SQL array INSERT:
INSERT INTO <schema>.<view>
SELECT <expression1a>, <expression2a>, … FROM SYS.DUMMY
UNION ALL
SELECT <expression1b>, <expression2b>, … FROM SYS.DUMMY
…
I will discuss the more efficient ODBC array input method using an INSERT statement with parameter markers later with a python ODBC code sample. However, keep in mind that some ODBC tools do not support SQL statements with parameter markers or some language plugins like the node.js ODBC plugin currently do not allow to use ODBC array INSERT.
The DELETE Statement in the SQL Dialect of the ABAP SQL Service
Simple DELETE statements with or without WHERE clauses are supported.
DELETE FROM <schema>.<view> <WHERE-clause>
where <WHERE-clause> can be any WHERE clause that can be used in a corresponding query (“SELECT FROM <schema>.<view> <WHERE-clause>”).
Bundling Multiple DML Statements in one Database Transaction
For the first time, the ABAB SQL service now supports DML statements, but it still does not provide an option for explicit transaction handling. This means that the ODBC driver for ABAP always works in auto commit mode and does not support explicit commits and rollbacks.
This implies that all DML statements are automatically committed after each successful execution. On the other hand, all changes of a DML statement execution are automatically rolled back after an error occurs.
The other way to bundle multiple row changes in a single database transaction is the use of array input for ODBC execution using DML statements with parameter markers. Again, the ODBC execution with array input is atomically committed on success or rolled back after an error.
Without explicit transaction handling, multiple different DML statements cannot be bundled in one database transaction.
DML examples without Parameter Markers Using iusql
I will now provide some DML examples on our test entity using the simple iusql command line tool included in the Linux unixODBC package ( see my Linux blog post). This is just an arbitrary example of a tool that does not support SQL statements with parameter markers. As an alternative, you could, for example, use a Java-based tool like DBeaver or Squirrel with a suitable JDBC/ODBC bridge for the ODBC driver for ABAP.
In this example, the following happens:
An INSERT … VALUES statement is used to insert the first row into our test entitySome INSERT … SELECT statements are used to generate more test data with ascending ItemNr and IDateA DELETE … WHERE is used to delete a first set of rowsA subsequent DELETE is used to clean up the table
> iusql -v MYDSN
+—————————————+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+—————————————+
SQL> delete from ZWRITE.WriteView
SQLRowCount returns 0
SQL> insert into ZWRITE.WriteView
values( 1 , date ‘2015-01-01’ , CAST( 100 AS DECIMAL) )
SQLRowCount returns 1
SQL> INSERT INTO ZWRITE.WriteView
select MAX(VMAX.ItemNr) + V.ItemNr ,
DATN_ADD_DAYS( V.IDate, MAX(VMAX.ItemNr) ) ,
V.Amount
from ZWRITE.WriteView V ,
ZWRITE.WriteView VMAX
group by V.ItemNr , V.IDate, V.Amount
SQLRowCount returns 1
SQL> repeat … INSERT INTO
SQLRowCount returns 2
SQL> repeat … INSERT INTO
SQLRowCount returns 4
SQL> repeat … INSERT INTO
SQLRowCount returns 8
SQL> repeat … INSERT INTO … make sure that you do not repeat this too often
SQLRowCount returns 16
SQL> select ItemNr, IDate FROM ZWRITE.WriteView
+————+———+
| ItemNr | IDate |
+————+———+
| 1 | 20150101|
| 2 | 20150102|
| 3 | 20150103|
| … |
| 19 | 20150119|
| 20 | 20150120|
| 21 | 20150121|
| … |
| 31 | 20150131|
| 32 | 20150201|
+————+———+
SQLRowCount returns 0
32 rows fetched
SQL> delete from ZWRITE.WriteView where IDate < date ‘2015-01-20’
SQLRowCount returns 19
SQL> delete from ZWRITE.WriteView
SQLRowCount returns 13
DML Array Input Examples Using a Python Sample
ODBC supports using prepared statements multiple times. Prepared statements with parameter markers can be executed with an array input, allowing to execute the same prepared statement multiple times with a different set of input parameters in one network roundtrip. For the ODBC driver for ABAP, this also means that such statement executions with array input will be performed in one database transaction and all statement executions will be committed or rolled back together.
Native ODBC C code can easily become lengthy and is not well suited for demo purposes. Therefore, like in one of my previous blog posts about AMDP procedures, I will provide a Python code sample here. Python demo code is much more compact and easier to read and understand. The Python ODBC plugin called pyodbc supports ODBC array input. A more detailed introduction to this ODBC plugin has already been provided in this Python blog post .
The following Python sample code:
Inserts some rows with an INSERT … VALUES statement with parameter markersDoubles the number of rows with an INSERT … SELECT statementDeletes some rows with an array DELETE statement with parameter markers
A subsequent DELETE is used to clean up the table
Please note fast_executemany=True used for the array statement. This ensures that the array data is sent to the ABAP backend in one package and that the array operation is performed in one database transaction.
import pyodbc
import getpass
import datetime
# some docu in
# https://code.google.com/archive/p/pyodbc/wikis/GettingStarted.wiki
# https://github.com/mkleehammer/pyodbc/wiki
#
# install pyodbc before running the program
# “pip install pyodbc”
#
# run with
# “python3 “array_dml_demo.py”
dsn = input(“Your ODBC DSN? “)
user = input(“Your Connect User? “)
pwd = getpass.getpass(“Your Connect Password? “)
conn = pyodbc.connect(‘DSN=’ + dsn + ‘;UID=’ + user + ‘;PWD=’ + pwd + ‘;Trace=none;’ )
def truncate_data():
truncate_curs = conn.cursor()
sql_truncate = “DELETE FROM ZWRITE.WriteView”
truncate_curs.execute(sql_truncate)
row_count = truncate_curs.rowcount
print(‘Number of truncated rows: ‘ + str(row_count) )
truncate_curs.close()
truncate_data()
# The following test code runs on a writable view entity exposed as
# ZWRITE.WriteView
# with columns
# key itemnr : abap.int4 not null;
# key idate : abap.datn not null;
# amount : abap.decfloat34;
# array data for insert
array_data = [
(1, datetime.date(2023, 12, 24), 17 ),
(2, datetime.date(2023, 12, 25), 4711 ),
(3, datetime.date(2023, 12, 26), 1995 )
]
insert_curs = conn.cursor()
insert_curs.fast_executemany = True
sql_insert_with_values_clause = “INSERT INTO ZWRITE.WriteView VALUES (?, ?, ?)”
insert_curs.executemany(sql_insert_with_values_clause, array_data)
print(‘Insert Array Size: ‘ + str(len(array_data)) )
# pyodbc always returns row_count=-1 for executemany() ?!
# https://github.com/mkleehammer/pyodbc/issues/481
# row_count = insert_curs.rowcount
# print(‘Number of inserted rows: ‘ + str(row_count) )
sql_insert_select = “INSERT INTO ZWRITE.WriteView SELECT itemnr+3, DATN_ADD_DAYS(idate , 3), amount FROM ZWRITE.WriteView”
insert_curs.execute(sql_insert_select)
row_count = insert_curs.rowcount
print(‘Number of inserted rows: ‘ + str(row_count) )
insert_curs.close()
query_cursor = conn.cursor()
sql_query = “SELECT * FROM ZWRITE.WriteView”
query_cursor.execute(sql_query)
for row in query_cursor.fetchall():
print (row)
query_cursor.close()
delete_curs = conn.cursor()
delete_curs.fast_executemany = True
sql_delete = “DELETE FROM ZWRITE.WriteView WHERE itemnr = ? AND idate = ? AND amount = ?”
delete_curs.executemany(sql_delete, array_data)
print(‘Delete Array Size: ‘ + str(len(array_data)) )
truncate_data()
# clean up
conn.close()
The output of the program could look as follows:
> python3 array_dml_demo.py
Your ODBC DSN? MYDSN
Your Connect User? MYUSER
Your Connect Password?
Number of truncated rows: 0
Insert Array Size: 3
Number of inserted rows: 3
(1, datetime.date(2023, 12, 24), Decimal(’17’))
(2, datetime.date(2023, 12, 25), Decimal(‘4711’))
(3, datetime.date(2023, 12, 26), Decimal(‘1995’))
(4, datetime.date(2023, 12, 27), Decimal(’17’))
(5, datetime.date(2023, 12, 28), Decimal(‘4711’))
(6, datetime.date(2023, 12, 29), Decimal(‘1995’))
Delete Array Size: 3
Number of truncated rows: 3
Conclusion
The SQL service now allows to expose writable view entities and the blog has shown some examples of the INSERT and DELETE statements that are now supported using the ABAP SQL service.
Please provide feedback if this information has been helpful for you. I am very curious about specific use cases and about the ODBC plugins/programs you have used to implement them.
Other Blogs Related to the ABAP SQL Service or the ODBC Driver for ABAP
Consuming CDS View Entities Using ODBC-Based Client Tools | SAP Blogs
Using the ODBC driver for ABAP on Linux | SAP Blogs
Access Control for ABAP’s SQL Service (1/3) – Set the Scene | SAP Blogs
SQL Queries on CDS objects Exposed as SQL Service | SAP Blogs
Access CDS Objects Exposed as SQL Services Using Microsoft SQL Server | SAP Blogs
Data Science with SAP S/4HANA – connect with Jupiter notebooks | SAP Blogs
HANA SDA. Accessing ABAP CDS View entities in HANA cloud – SAP Blogs
Build an Azure Data Factory Pipeline with the ODBC driver for ABAP – SAP Blogs
Expose and Use ABAP-Managed Database Procedures (A… – SAP Community
Rust and ABAP ODBC – SAP Community
Links to SAP documentation
Data Integration (in the ABAP Integration and Connectivity Guide)
ODBC – ABAP Platform, On-Premise
In previous blog posts (see links at the end), I described data integration scenarios where business users can access data in an ABAP system from an external consumer, using the ABAP SQL service and the ODBC driver for ABAP. In this scenario, can query data in exposed CDS view entities. So far, this was a read-only scenario, where SELECT statements were used.Now, with the availability of writable CDS view entities, the SQL service can also be used to change data in an ABAP system. The first supported SQL DML statements that allow to change data in an SAP system using the ABAP SQL service are INSERT and DELETE.In this blog post, I will describe an example where a writable ABAP CDS view entity is exposed, I will describe the supported INSERT and DELETE statement types and their restrictions, and provide a code sample that uses those statements on an exposed entity.Prerequisites:ABAP platform cloud 2508 or ABAP on-premise 2025 or higherODBC driver for ABAP version 01, or higherThe steps to expose a writable view entity are very similar to the steps described in this blog post for read-only entities. I will try to keep the description in this blog post similar and only outline the differences for writable CDS view entities.Create a Writable CDS View Entity on Top of a TableFirst, I create a test table for this blog post and a writable CDS view entity on top of it. In my case, the writable view entity is a simple projection of a database test table containing all table columns.@EndUserText.label : ‘test table for SQL Service DMLs’
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zsql_write_tab {
key client : abap.clnt not null;
key itemnr : abap.int4 not null;
key idate : abap.datn not null;
@AbapCatalog.decfloat.outputStyle : #NORMAL
amount : abap.decfloat34;
}
@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: ‘writable view entity for SQL service’
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
serviceQuality: #X,
sizeCategory: #S,
dataClass: #MIXED
}
define writable view entity ZSQL_WRITE_VIEW
as select from zsql_write_tab as TestTable
{
key itemnr as ItemNr,
key idate as IDate,
amount as Amount
}Create a service definition and binding containing the writable CDS view entityAs a second step, define a service definition exposing the writable view entity:@EndUserText.label: ‘service def with writable view entity’
define service ZWRITE
provider contracts sql {
expose ZSQL_WRITE_VIEW as WriteView;
}I create a service binding with the same name as the service definition and enable operations. Please note that I enabled operations SELECT, INSERT, and DELETE for this service binding: Configure Privileged User Access with INSERT and DELETE OperationsNext, I define the user access to the writable CDS view entities. Again, the configuration of privileged user access has already been described in this blog post for a cloud system, or you can refer to the onprem documentation. INSERT and DELETE statements are currently only allowed for privileged user access.So, essentially, you can follow the previous blog post. The only difference is that you now need to add INSERT and DELETE as allowed SQL view operations to the S_SQL_VIEW authorization object, for example, with the following properties.SQL_SCHEMA -> ZWRITESQL_VIEW -> *SQL_VIEWOP -> SELECT, INSERT, DELETENow, everything is set up to execute queries and INSERT and DELETE statements on our test entity.I won’t repeat the description of how to install and configure the ODBC driver for ABAP here and for the next steps I will just assume that this is done and that you configured an ODBC DSN for your SAP system. For all details about installing and configuring the ODBC driver for ABAP, check my previous blog posts.Now let’s turn to the SQL statements INSERT and DELETE and how they are supported in the SQL service.The INSERT Statement in the SQL Dialect of the ABAP SQL ServiceCompared to ABAP SQL, the SQL dialect of the SQL service has a few restrictions and specialties that you must consider.INSERT … VALUES and INSERT … SELECT statements are supported.The supported INSERT … VALUES statements have the following form:INSERT INTO <schema>.<view> VALUES ( <expression1>, <expression2>, …)Compared to other databases, currently there is no column list clause supported. The maximum number of expressions in the VALUES clause is the number of columns in the exposed entity. If you provide fewer expressions, the last columns in the writable entity will be set to their default database values during INSERT.Expressions can be simple literals, expressions containing functions, or parameter markers ( ‘?’ ) for prepared statements.For literals or expressions, the types need to be compatible with the DDIC data types of exposed entity. This requires CASTs in some cases.INSERT … SELECT statements have the following form:INSERT INTO <schema>.<view> <ABAP SQL service SQL query>where <ABAP SQL service SQL query> is a SELECT statement in the SQL dialect of the SQL service.Currently, the <ABAP SQL service SQL query> cannot contain common table expressions (CTEs starting with the keyword “WITH”).Again, the number of columns in the result set of the SELECT statement must match the number of columns in the exposed entity, and the data types returned by the SELECT statement need to be compatible with the data types of the exposed entity.The following INSERT statements are equivalent:INSERT INTO <schema>.<view> VALUES ( <expression1>, <expression2>, …)
INSERT INTO <schema>.<view> SELECT <expression1>, <expression2>, … FROM SYS.DUMMYThe SYS.DUMMY trick can also be used to formulate some kind of simple SQL array INSERT:INSERT INTO <schema>.<view>
SELECT <expression1a>, <expression2a>, … FROM SYS.DUMMY
UNION ALL
SELECT <expression1b>, <expression2b>, … FROM SYS.DUMMY
… I will discuss the more efficient ODBC array input method using an INSERT statement with parameter markers later with a python ODBC code sample. However, keep in mind that some ODBC tools do not support SQL statements with parameter markers or some language plugins like the node.js ODBC plugin currently do not allow to use ODBC array INSERT.The DELETE Statement in the SQL Dialect of the ABAP SQL ServiceSimple DELETE statements with or without WHERE clauses are supported.DELETE FROM <schema>.<view> <WHERE-clause>where <WHERE-clause> can be any WHERE clause that can be used in a corresponding query (“SELECT FROM <schema>.<view> <WHERE-clause>”).Bundling Multiple DML Statements in one Database TransactionFor the first time, the ABAB SQL service now supports DML statements, but it still does not provide an option for explicit transaction handling. This means that the ODBC driver for ABAP always works in auto commit mode and does not support explicit commits and rollbacks. This implies that all DML statements are automatically committed after each successful execution. On the other hand, all changes of a DML statement execution are automatically rolled back after an error occurs.The other way to bundle multiple row changes in a single database transaction is the use of array input for ODBC execution using DML statements with parameter markers. Again, the ODBC execution with array input is atomically committed on success or rolled back after an error.Without explicit transaction handling, multiple different DML statements cannot be bundled in one database transaction.DML examples without Parameter Markers Using iusqlI will now provide some DML examples on our test entity using the simple iusql command line tool included in the Linux unixODBC package ( see my Linux blog post). This is just an arbitrary example of a tool that does not support SQL statements with parameter markers. As an alternative, you could, for example, use a Java-based tool like DBeaver or Squirrel with a suitable JDBC/ODBC bridge for the ODBC driver for ABAP.In this example, the following happens:An INSERT … VALUES statement is used to insert the first row into our test entitySome INSERT … SELECT statements are used to generate more test data with ascending ItemNr and IDateA DELETE … WHERE is used to delete a first set of rowsA subsequent DELETE is used to clean up the table> iusql -v MYDSN
+—————————————+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+—————————————+
SQL> delete from ZWRITE.WriteView
SQLRowCount returns 0
SQL> insert into ZWRITE.WriteView
values( 1 , date ‘2015-01-01’ , CAST( 100 AS DECIMAL) )
SQLRowCount returns 1
SQL> INSERT INTO ZWRITE.WriteView
select MAX(VMAX.ItemNr) + V.ItemNr ,
DATN_ADD_DAYS( V.IDate, MAX(VMAX.ItemNr) ) ,
V.Amount
from ZWRITE.WriteView V ,
ZWRITE.WriteView VMAX
group by V.ItemNr , V.IDate, V.Amount
SQLRowCount returns 1
SQL> repeat … INSERT INTO
SQLRowCount returns 2
SQL> repeat … INSERT INTO
SQLRowCount returns 4
SQL> repeat … INSERT INTO
SQLRowCount returns 8
SQL> repeat … INSERT INTO … make sure that you do not repeat this too often
SQLRowCount returns 16
SQL> select ItemNr, IDate FROM ZWRITE.WriteView
+————+———+
| ItemNr | IDate |
+————+———+
| 1 | 20150101|
| 2 | 20150102|
| 3 | 20150103|
| … |
| 19 | 20150119|
| 20 | 20150120|
| 21 | 20150121|
| … |
| 31 | 20150131|
| 32 | 20150201|
+————+———+
SQLRowCount returns 0
32 rows fetched
SQL> delete from ZWRITE.WriteView where IDate < date ‘2015-01-20’
SQLRowCount returns 19
SQL> delete from ZWRITE.WriteView
SQLRowCount returns 13DML Array Input Examples Using a Python SampleODBC supports using prepared statements multiple times. Prepared statements with parameter markers can be executed with an array input, allowing to execute the same prepared statement multiple times with a different set of input parameters in one network roundtrip. For the ODBC driver for ABAP, this also means that such statement executions with array input will be performed in one database transaction and all statement executions will be committed or rolled back together. Native ODBC C code can easily become lengthy and is not well suited for demo purposes. Therefore, like in one of my previous blog posts about AMDP procedures, I will provide a Python code sample here. Python demo code is much more compact and easier to read and understand. The Python ODBC plugin called pyodbc supports ODBC array input. A more detailed introduction to this ODBC plugin has already been provided in this Python blog post .The following Python sample code:Inserts some rows with an INSERT … VALUES statement with parameter markersDoubles the number of rows with an INSERT … SELECT statementDeletes some rows with an array DELETE statement with parameter markersA subsequent DELETE is used to clean up the tablePlease note fast_executemany=True used for the array statement. This ensures that the array data is sent to the ABAP backend in one package and that the array operation is performed in one database transaction.import pyodbc
import getpass
import datetime
# some docu in
# https://code.google.com/archive/p/pyodbc/wikis/GettingStarted.wiki
# https://github.com/mkleehammer/pyodbc/wiki
#
# install pyodbc before running the program
# “pip install pyodbc”
#
# run with
# “python3 “array_dml_demo.py”
dsn = input(“Your ODBC DSN? “)
user = input(“Your Connect User? “)
pwd = getpass.getpass(“Your Connect Password? “)
conn = pyodbc.connect(‘DSN=’ + dsn + ‘;UID=’ + user + ‘;PWD=’ + pwd + ‘;Trace=none;’ )
def truncate_data():
truncate_curs = conn.cursor()
sql_truncate = “DELETE FROM ZWRITE.WriteView”
truncate_curs.execute(sql_truncate)
row_count = truncate_curs.rowcount
print(‘Number of truncated rows: ‘ + str(row_count) )
truncate_curs.close()
truncate_data()
# The following test code runs on a writable view entity exposed as
# ZWRITE.WriteView
# with columns
# key itemnr : abap.int4 not null;
# key idate : abap.datn not null;
# amount : abap.decfloat34;
# array data for insert
array_data = [
(1, datetime.date(2023, 12, 24), 17 ),
(2, datetime.date(2023, 12, 25), 4711 ),
(3, datetime.date(2023, 12, 26), 1995 )
]
insert_curs = conn.cursor()
insert_curs.fast_executemany = True
sql_insert_with_values_clause = “INSERT INTO ZWRITE.WriteView VALUES (?, ?, ?)”
insert_curs.executemany(sql_insert_with_values_clause, array_data)
print(‘Insert Array Size: ‘ + str(len(array_data)) )
# pyodbc always returns row_count=-1 for executemany() ?!
# https://github.com/mkleehammer/pyodbc/issues/481
# row_count = insert_curs.rowcount
# print(‘Number of inserted rows: ‘ + str(row_count) )
sql_insert_select = “INSERT INTO ZWRITE.WriteView SELECT itemnr+3, DATN_ADD_DAYS(idate , 3), amount FROM ZWRITE.WriteView”
insert_curs.execute(sql_insert_select)
row_count = insert_curs.rowcount
print(‘Number of inserted rows: ‘ + str(row_count) )
insert_curs.close()
query_cursor = conn.cursor()
sql_query = “SELECT * FROM ZWRITE.WriteView”
query_cursor.execute(sql_query)
for row in query_cursor.fetchall():
print (row)
query_cursor.close()
delete_curs = conn.cursor()
delete_curs.fast_executemany = True
sql_delete = “DELETE FROM ZWRITE.WriteView WHERE itemnr = ? AND idate = ? AND amount = ?”
delete_curs.executemany(sql_delete, array_data)
print(‘Delete Array Size: ‘ + str(len(array_data)) )
truncate_data()
# clean up
conn.close()The output of the program could look as follows:> python3 array_dml_demo.pyYour ODBC DSN? MYDSNYour Connect User? MYUSERYour Connect Password?Number of truncated rows: 0Insert Array Size: 3Number of inserted rows: 3(1, datetime.date(2023, 12, 24), Decimal(’17’))(2, datetime.date(2023, 12, 25), Decimal(‘4711’))(3, datetime.date(2023, 12, 26), Decimal(‘1995′))(4, datetime.date(2023, 12, 27), Decimal(’17’))(5, datetime.date(2023, 12, 28), Decimal(‘4711’))(6, datetime.date(2023, 12, 29), Decimal(‘1995’))Delete Array Size: 3Number of truncated rows: 3ConclusionThe SQL service now allows to expose writable view entities and the blog has shown some examples of the INSERT and DELETE statements that are now supported using the ABAP SQL service.Please provide feedback if this information has been helpful for you. I am very curious about specific use cases and about the ODBC plugins/programs you have used to implement them.Other Blogs Related to the ABAP SQL Service or the ODBC Driver for ABAPConsuming CDS View Entities Using ODBC-Based Client Tools | SAP BlogsUsing the ODBC driver for ABAP on Linux | SAP BlogsAccess Control for ABAP’s SQL Service (1/3) – Set the Scene | SAP BlogsSQL Queries on CDS objects Exposed as SQL Service | SAP BlogsAccess CDS Objects Exposed as SQL Services Using Microsoft SQL Server | SAP BlogsData Science with SAP S/4HANA – connect with Jupiter notebooks | SAP BlogsHANA SDA. Accessing ABAP CDS View entities in HANA cloud – SAP BlogsBuild an Azure Data Factory Pipeline with the ODBC driver for ABAP – SAP BlogsExpose and Use ABAP-Managed Database Procedures (A… – SAP CommunityRust and ABAP ODBC – SAP CommunityLinks to SAP documentationData Integration (in the ABAP Integration and Connectivity Guide)ODBC – ABAP Platform, On-Premise Read More Technology Blog Posts by SAP articles
#SAP
#SAPTechnologyblog
![Google Cloud Associate Cloud Engineer Course [2025] – Pass the Exam!](https://i4.ytimg.com/vi/OlAmyf8_4O4/hqdefault.jpg)
