Attachment 186308 Details for Bug 154464 – Macrocode for creating and inserting data in PostgreSQL table.

Macrocode for creating and inserting data in PostgreSQL table.

PostrgeSQL_setArray.txt (text/plain), 2.35 KB, created by Robert Großkopf on 2023-03-30 06:35:57 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Robert Großkopf

Created: 2023-03-30 06:35:57 UTC

Size: 2.35 KB

patch

obsolete

>REM  *****  BASIC  *****
>
>SUB NewTable
>	DIM oDatasource AS OBJECT
>	DIM oConnection AS OBJECT
>	oDatasource = thisDatabaseDocument.CurrentController
>	IF NOT (oDatasource.isConnected()) THEN oDatasource.connect()
>	oConnection = oDatasource.ActiveConnection()
>	oSQL_Statement = oConnection.createStatement()
>	stSql = "DROP TABLE IF EXISTS ""public"".""Table_Ar"" "
>	oSQL_Statement.executeUpdate(stSql)
>	stSql = "CREATE TABLE ""public"".""Table_Ar"" (""ID"" int4 NOT NULL,""Surname"" varchar(100),""Forenames"" varchar(200)[], PRIMARY KEY (""ID""));"
>	oSQL_Statement.executeUpdate(stSql)
>END SUB
>
>SUB Insert
>	DIM oDatasource AS OBJECT
>	DIM oConnection AS OBJECT
>	oDatasource = thisDatabaseDocument.CurrentController
>	IF NOT (oDatasource.isConnected()) THEN oDatasource.connect()
>	oConnection = oDatasource.ActiveConnection()
>	oSQL_Statement = oConnection.createStatement()
>	stSql = "INSERT INTO ""public"".""Table_Ar"" (""ID"", ""Surname"", ""Forenames"") VALUES (1, 'MÃ¼ller', '{Lise, Gerd}')"
>	oSQL_Statement.executeUpdate(stSql)
>END SUB
>
>SUB PrepareStatementInsert
>	DIM oDatasource AS OBJECT
>	DIM oConnection AS OBJECT
>	DIM ar
>	oDatasource = thisDatabaseDocument.CurrentController
>	IF NOT (oDatasource.isConnected()) THEN oDatasource.connect()
>	oConnection = oDatasource.ActiveConnection()
>	DIM stSql AS STRING
>	stSql = "INSERT INTO ""public"".""Table_Ar"" (""ID"", ""Surname"", ""Forenames"") VALUES (?, ?, ?)"
>	oSQL_Statement = oConnection.prepareStatement(stSql)
>	oSQL_Statement.setLong(1, 2)
>	oSQL_Statement.setString(2, "Big")
>	ar = array("Will","John","Jack")
>'	msgbox ar(2)
> 	oSQL_Statement.setArray(3, ar)
> 	oSQL_Statement.executeUpdate(stSql)
>END SUB
>
>SUB PrepareStatementInsertWithoutArray
>	DIM oDatasource AS OBJECT
>	DIM oConnection AS OBJECT
>	DIM ar
>	oDatasource = thisDatabaseDocument.CurrentController
>	IF NOT (oDatasource.isConnected()) THEN oDatasource.connect()
>	oConnection = oDatasource.ActiveConnection()
>	DIM stSql AS STRING
>	stSql = "INSERT INTO ""public"".""Table_Ar"" (""ID"", ""Surname"", ""Forenames"") VALUES (?, ?, ?)"
>	oSQL_Statement = oConnection.prepareStatement(stSql)
>	oSQL_Statement.setLong(1, 2)
>	oSQL_Statement.setString(2, "Big")
>	ar = array("Will","John","Jack")
>	stAr = "{"
>	FOR i = LBound(ar()) TO UBound(ar())
>		stAr = stAr & ar(i) & ","
>	NEXT
>	stAr = Left(stAr,Len(stAr)-1) & "}"
> 	oSQL_Statement.setString(3, stAr)
> 	oSQL_Statement.executeUpdate(stSql)
>END SUB

Actions: View

Attachments on bug 154464: 186308 | 186309 | 186339