Good Afternoon,
This is based off of my problem at link:
Looping through an array in an embedded procedure in firebird - English - Ask LibreOffice
I have set up postgres local version, 9 with pgAdmin 3. I am working with the following code in java:
import java.net.Socket;
import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;
/**
*
* @author minhaszt
*/
public class PGFunctions extends DaoFunctions {
public static void main(String[] par)
{
PGFunctions pgf = new PGFunctions();
System.out.println(pgf.isAvailable());
Connection con = null;
if (pgf.isAvailable())
{
con = pgf.makeConnection();
String sql = "drop function if exists temp.f_iu7YttW(character varying[]);\n"+
"delete from temp.table1;\n"+
"create or replace function temp.f_iu7YttW(dname varchar[]) returns void as $$\n" +
"declare\n" +
"counter integer = 1;\n" +
"begin\n" +
" while counter<1000000 loop\n" +
" insert into temp.table1(name) values(dname[counter]);\n" +
" counter=counter+1;\n" +
" end loop;\n" +
"end; $$ language plpgsql;\n" +
"select temp.f_iu7YttW(?);\n" +
"drop function temp.f_iu7YttW(character varying[]);";
try{
PreparedStatement stmt = con.prepareStatement(sql);
ArrayList<String> arvars = new ArrayList<String>();
for (int x=0;x<1000000;x++)
{
arvars.add(x+"");
}
String[] vars ={};
vars = arvars.toArray(vars);
Array sqlArray = con.createArrayOf("text", vars);
stmt.setArray(1, sqlArray);
stmt.execute();
}
catch(Exception ex)
{
ex.printStackTrace();
}
}
}
public Connection makeConnection()
{
String url = "jdbc:postgresql://localhost:5432/postgres";
Object[] conParams = {url,"root","root"};
return this.getConnection(conParams);
}
public boolean isAvailable() {
boolean ret = false;
Socket s =null;
try {
s = new Socket("localhost", 5432);
s.close();
ret = true;
}
finally {
return ret;
}
}
}I would like to make an equivalent in Basic. Here is what I have so far:
Sub Main
Dim stmt As Object
Dim rs As Object
Dim sql As String
Dim a As Variant
Dim x As Long
makePGConnection
executeDDLPg("delete from temp.table1")
executeDDLPg("drop function if exists temp.f_iu7YttW(character varying[])")
myDict= getScriptForgeDictionary()
x=0
a= Array(x & "")
SF_Array.Append(a)
For x = 1 To 50
a=SF_Array.Append(a,x & "")
Next x
max = ubound(a)
sql ="create or replace function temp.f_iu7YttW(dname varchar[]) returns void as $$ " & _
"declare " & _
"counter integer = 1; " & _
"begin " & _
" while counter<" & max & " loop " & _
" insert into temp.table1(name) values(dname[counter]); " & _
" counter=counter+1; " & _
" end loop; " & _
"end; $$ language plpgsql;" ' & _
executeDDLPg(sql)
sql="select temp.f_iu7YttW(?);\n" ' & _
a = pgCon.createArrayOf("text", a)
stmt = pgCon.prepareStatement(sql)
stmt.setArray(1,a)
stmt.executeUpdate()
stmt.clearParameters()
closeDbObject(stmt)
executeDDLPg("drop function temp.f_iu7YttW(character varying[])")
End Sub
Private sub makePGConnection()
Dim sURL$
Dim myDict As Variant
sURL$ ="sdbc:postgresql:dbname=postgres host=localhost port=5432"
myDict= getScriptForgeDictionary()
With myDict
.Add("user", "root")
.Add("password", "root")
.Add("JavaDriverClass", "org.postgresql.Driver")
oParms = .ConvertToPropertyValues()
.Dispose()
End With
pgCon=getConnectionWithUserInfo(sURL, oParms)
End sub
And my Basic code makes LO crash and die at the line:
stmt.setArray(1,a)Any help is greatly appreciated.
