One possibility is the usage of the statement object.
/* get the service manager */
xContext = UNO.connect()
XMcf = xContext~getServiceManager
/* retrieve the DatabaseContext and get its XNameAccess interface */
xNameAccess = xMcf~createInstanceWithContext(-
"{%see com.sun.star.sdb.DatabaseContext}", xContext)~{%see com.sun.star.container.XNameAccess%XNameAccess}
/* we use the "mysql-test" datasorce */
dataSource = xNameAccess~getByName("mysql-test")
/************ non-interactive login ************/
/* query for the XDataSource interface of the data source */
xDataSource = dataSource~{%see com.sun.star.sdbc.XDataSource%XDataSource}
/* simple way to connect - hard code (usr,pw) */
xConnection = xDataSource~getConnection("stefan","apple");
/* the connection creates a statement */
xStatement = xConnection~createStatement
/* execute Updates on the database - the basic way*/
/* drop existing tables */
xStatement~executeUpdate("drop table product")
xStatement~executeUpdate("drop table customer")
xStatement~executeUpdate("drop table sales")
/* create the 'product' table and fill it with data */
xStatement~executeUpdate(-
"create table product (name varchar(30) primary key, price float)")
xStatement~executeUpdate(-
"insert into product (name, price) values('Monitor 17inch', 214.90)")
xStatement~executeUpdate(-
"insert into product (name, price) values('Monitor 19inch', 419.50)")
xStatement~executeUpdate(-
"insert into product (name, price) values('Monitor 21inch', 759.90)")
xStatement~executeUpdate(-
"insert into product (name, price) values('Harddisc 160MB', 67.00)")
xStatement~executeUpdate(-
"insert into product (name, price) values('Sound Card', 43.90)")
xStatement~executeUpdate(-
"insert into product (name, price) values('SDRAM 1024MB', 156.90)")
say "table 'product' created"
/* create the 'customer' table */
xStatement~executeUpdate(-
"create table customer (cid int primary key auto_increment, "-
"firstname varchar(30), lastname varchar(30), birthdate date)")
/* execute Updates on the database - using a prepared statement */
/* create a prepared statement for making inserts in the table customer */
insertCustomerStatement = xConnection~prepareStatement(-
"insert into customer (firstname, lastname, birthdate) values(?,?,?)")
/* fill the 'customer' table with data */
call insertData insertCustomerStatement, "Tyler", "Durden", "1973-7-2"
call insertData insertCustomerStatement, "Jacob", "Fuller", "1946-9-3"
call insertData insertCustomerStatement, "Mickey", "Knox", "1976-3-17"
call insertData insertCustomerStatement, "Vincent", "Vega", "1963-12-1"
say "table 'customers' created"
/* create the 'sales' table and fill it with data */
xStatement~executeUpdate(-
"create table sales(sid int primary key auto_increment, cid int,"-
"name varchar(30), quantity int)")
insertSalesStatement = xConnection~prepareStatement(-
"insert into sales (cid, name, quantity) values(?,?,?)")
call insertData insertSalesStatement, "1", "Monitor 21inch", "1"
call insertData insertSalesStatement, "2", "Sound Card", "2"
call insertData insertSalesStatement, "2", "Monitor 17inch", "1"
call insertData insertSalesStatement, "3", "Monitor 21inch", "3"
call insertData insertSalesStatement, "3", "Monitor 21inch", "1"
call insertData insertSalesStatement, "4", "Harddisc 160MB", "1"
call insertData insertSalesStatement, "3", "Monitor 19inch", "5"
say "table 'sales' created"
say
/* execute Query and retrieve the ResultSet */
xResultSet = xStatement~executeQuery(-
"select cid, firstname, lastname, birthdate from customer")
say "customer Table: (id | firstname | lastname | date of birth)"
IF xResultSet~isBeforeFirst = 0 THEN
say "no results!"
ELSE DO
/* process the ResultSet */
xRow = xResultSet~{%see com.sun.star.sdbc.XRow%XRow}
DO WHILE xResultSet~next <> .false
cid = xRow~getString(1)
firstname = xRow~getString(2)
lastname = xRow~getString(3)
birthdate = xRow~getString(4)
say cid "|" firstname "|" lastname "|" birthdate
END
END
::requires UNO.cls -- get UNO support
/* insert data using a prepared statement */
::ROUTINE insertData
USE ARG preparedStatement, data1, data2, data3
xParameters = preparedStatement~{%see com.sun.star.sdbc.XParameters%XParameters}
xParameters~setString(1, data1)
xParameters~setString(2, data2)
xParameters~setString(3, data3)
preparedStatement~executeUpdate