Thread: getProcedureColumns
Hi,
I been working with metadata on stored procedures and discovered
that I can't get parameter names via getProcedureColumns. So i
checked out the source and took a look at the method. Seems that for
column names, a $ is appended to the arg type count. I guess I don't
know if there is a reason that the actual parameter names are not
used, or its just low on the TODO list. I went ahead and updated the
method return column names as stored in procargnames of
pg_catalog.pg_proc. I have attached a patch.
I honestly don't know if this is the correct mechanism to handle this
(I poped into #postgres and was told to attach a patch to an email to
this list), so just point me in the right direction if need be
(either on how to submit the patch, or why making this change is
stupid).
Jeff
Attachment
On Wed, 31 Jan 2007, Jeffrey Cox wrote: > I been working with metadata on stored procedures and discovered that I > can't get parameter names via getProcedureColumns. So i checked out the > source and took a look at the method. Seems that for column names, a $ is > appended to the arg type count. I guess I don't know if there is a reason > that the actual parameter names are not used, or its just low on the TODO > list. I went ahead and updated the method return column names as stored in > procargnames of pg_catalog.pg_proc. I have attached a patch. It just hasn't been updated in a while. > I honestly don't know if this is the correct mechanism to handle this (I > poped into #postgres and was told to attach a patch to an email to this > list), so just point me in the right direction if need be (either on how to > submit the patch, or why making this change is stupid). > This is exactly the place to send it. Any chance we can also convince you to add a test case and fix COLUMN_TYPE for out parameters? Kris Jurka
Yea sure... I will look into the COLUM_TYPE issue and write a test case for column names. On that note, when I ran the test suit all the jdbc2 test would run fine, however, most of the others failed due to "to many connections". I of course tried to up my max connections beyond 20, but looks like will need to up shared memory maximums. (which might be interesting given I am on os X. Just never done that since switching). So before I go about figuring all that out, are failures expected? and if not, any thoughts on the number of connections I should configure for? Jeff On Jan 31, 2007, at 4:20 PM, Kris Jurka wrote: > > > On Wed, 31 Jan 2007, Jeffrey Cox wrote: > >> I been working with metadata on stored procedures and discovered >> that I can't get parameter names via getProcedureColumns. So i >> checked out the source and took a look at the method. Seems that >> for column names, a $ is appended to the arg type count. I guess I >> don't know if there is a reason that the actual parameter names >> are not used, or its just low on the TODO list. I went ahead and >> updated the method return column names as stored in procargnames >> of pg_catalog.pg_proc. I have attached a patch. > > It just hasn't been updated in a while. > >> I honestly don't know if this is the correct mechanism to handle >> this (I poped into #postgres and was told to attach a patch to an >> email to this list), so just point me in the right direction if >> need be (either on how to submit the patch, or why making this >> change is stupid). >> > > This is exactly the place to send it. Any chance we can also > convince you to add a test case and fix COLUMN_TYPE for out > parameters? > > Kris Jurka
On Wed, 31 Jan 2007, Jeffrey Cox wrote: > Yea sure... I will look into the COLUM_TYPE issue and write a test case > for column names. On that note, when I ran the test suit all the jdbc2 > test would run fine, however, most of the others failed due to "to many > connections". I of course tried to up my max connections beyond 20, but > looks like will need to up shared memory maximums. (which might be > interesting given I am on os X. Just never done that since switching). > So before I go about figuring all that out, are failures expected? and > if not, any thoughts on the number of connections I should configure > for? > No failures are expected, but it's not surprising for things to go bad after completing the jdbc2 tests because that's when it starts testing connection pooling stuff that will open a fair number of connections. I don't think it uses more than 20 simultaneous connections so it's likely that there are just some that aren't closed or are in the process of closing as new ones are opened. If you can't get these tests straightened out I wouldn't worry about it because the changes you are making shouldn't affect any tests beyond the jdbc2 ones. Kris Jurka
On Jan 31, 2007, at 6:49 PM, Kris Jurka wrote: > > > On Wed, 31 Jan 2007, Jeffrey Cox wrote: > >> Yea sure... I will look into the COLUM_TYPE issue and write a test >> case for column names. On that note, when I ran the test suit all >> the jdbc2 test would run fine, however, most of the others failed >> due to "to many connections". I of course tried to up my max >> connections beyond 20, but looks like will need to up shared >> memory maximums. (which might be interesting given I am on os X. >> Just never done that since switching). So before I go about >> figuring all that out, are failures expected? and if not, any >> thoughts on the number of connections I should configure for? >> > > No failures are expected, but it's not surprising for things to go > bad after completing the jdbc2 tests because that's when it starts > testing connection pooling stuff that will open a fair number of > connections. I don't think it uses more than 20 simultaneous > connections so it's likely that there are just some that aren't > closed or are in the process of closing as new ones are opened. > > If you can't get these tests straightened out I wouldn't worry > about it because the changes you are making shouldn't affect any > tests beyond the jdbc2 ones. > Yes I assumed as much. I took a look at the the code to night regarding COLUMN_TYPE, and it doesn't look to bad. Given other project work this week, it will probably be the weekend before I churn it out. Jeff > Kris Jurka
Finally got these patches finished up. Sorry for the delay ( had a machine learning program fighting me all weekend). Please let me know if this is not what you need, or more it turns out there is something not working. All seems to be working, and am actually using the resulting jar in a external app. Though the tests get the job done, they are a bit 'brute force'. i figure they can be re-factored later. probably goes for the other code as well I suppose. Jeff On Jan 31, 2007, at 6:49 PM, Kris Jurka wrote: > > > On Wed, 31 Jan 2007, Jeffrey Cox wrote: > >> Yea sure... I will look into the COLUM_TYPE issue and write a test >> case for column names. On that note, when I ran the test suit all >> the jdbc2 test would run fine, however, most of the others failed >> due to "to many connections". I of course tried to up my max >> connections beyond 20, but looks like will need to up shared >> memory maximums. (which might be interesting given I am on os X. >> Just never done that since switching). So before I go about >> figuring all that out, are failures expected? and if not, any >> thoughts on the number of connections I should configure for? >> > > No failures are expected, but it's not surprising for things to go > bad after completing the jdbc2 tests because that's when it starts > testing connection pooling stuff that will open a fair number of > connections. I don't think it uses more than 20 simultaneous > connections so it's likely that there are just some that aren't > closed or are in the process of closing as new ones are opened. > > If you can't get these tests straightened out I wouldn't worry > about it because the changes you are making shouldn't affect any > tests beyond the jdbc2 ones. > > Kris Jurka
Attachment
On Tue, 6 Feb 2007, Jeffrey Cox wrote: > Finally got these patches finished up. Sorry for the delay ( had a machine > learning program fighting me all weekend). Please let me know if this is not > what you need, or more it turns out there is something not working. All seems > to be working, and am actually using the resulting jar in a external app. > Though the tests get the job done, they are a bit 'brute force'. i figure > they can be re-factored later. probably goes for the other code as well I > suppose. > This doesn't work on an 8.0 server so they're unlikely to work on 7.4 or 7.3 servers either all of which the driver must support. Right now I don't have all of these handy to test with, so if you don't either we'll have to wait for me to return to my normal dev machine. Some other notes: 1) It's better to send all your changes as one patch instead of one per file. It makes it easier to apply. 2) You've sent a patch for AbstractJdbc2Array that only adds something that's commented out. 3) When doing version comparisons you want connection.haveMinimumServerVersion, not MinimumCompatibleVersion. The Compatible idea is supposed to indicate what version of the driver the current driver should imitate (although it's rarely used). Kris Jurka
On Feb 14, 2007, at 1:37 AM, Kris Jurka wrote: > > > On Tue, 6 Feb 2007, Jeffrey Cox wrote: > >> Finally got these patches finished up. Sorry for the delay ( had a >> machine learning program fighting me all weekend). Please let me >> know if this is not what you need, or more it turns out there is >> something not working. All seems to be working, and am actually >> using the resulting jar in a external app. Though the tests get >> the job done, they are a bit 'brute force'. i figure they can be >> re-factored later. probably goes for the other code as well I >> suppose. >> > > This doesn't work on an 8.0 server so they're unlikely to work on > 7.4 or 7.3 servers either all of which the driver must support. > Right now I don't have all of these handy to test with, so if you > don't either we'll have to wait for me to return to my normal dev > machine. I can make them handy, but when you say they don't work do you mean it crashes, or what exactly. I ask only because the DBMS seems to have varying support for the method overall. below 7.3 not being able to support the call at all, below 8.0 not supporting argument names and below 8.1 not supporting argument modes. when I say not supporting, I mean there is not column for the data in 'pg_proc'. I didn't actually try this against those over servers, but looked it up in the respective documentation of each server version. I suppose that information might reside in another table, I will have to install the other versions and find out. > > Some other notes: > > 1) It's better to send all your changes as one patch instead of one > per file. It makes it easier to apply. good to know, would you like me to resend them? > > 2) You've sent a patch for AbstractJdbc2Array that only adds > something that's commented out. My bad... I had made a number of changes in that file originally, then after some discussion, changed direction. I must have missed the comments. > > 3) When doing version comparisons you want > connection.haveMinimumServerVersion, not MinimumCompatibleVersion. > The Compatible idea is supposed to indicate what version of the > driver the current driver should imitate (although it's rarely used). Also good to know, changed and the patch is attached. Jeff > > Kris Jurka >
Attachment
On Wed, 14 Feb 2007, Jeffrey Cox wrote: > I can make them handy, but when you say they don't work do you mean it > crashes, or what exactly. I ask only because the DBMS seems to have varying > support for the method overall. below 7.3 not being able to support the call > at all, below 8.0 not supporting argument names and below 8.1 not supporting > argument modes. when I say not supporting, I mean there is not column for the > data in 'pg_proc'. I didn't actually try this against those over servers, but > looked it up in the respective documentation of each server version. I > suppose that information might reside in another table, I will have to > install the other versions and find out. 1) Your tests try to create functions with named and output parameters unconditionally. These can't be created on older servers. 2) The code you've added to TestUtil uses dollar quoting which isn't available on all servers. 3) The code in getProcedureColumns fails with a "couldn't find a column named ..." for older server versions. You need to either add ", NULL as colname" to the sql for older versions or not fetch these columns unconditionally. >> 1) It's better to send all your changes as one patch instead of one per >> file. It makes it easier to apply. > > good to know, would you like me to resend them? > No, that's just a minor inconvenience, not an actual problem. Kris Jurka
On Feb 14, 2007, at 10:32 AM, Kris Jurka wrote: > > > On Wed, 14 Feb 2007, Jeffrey Cox wrote: > >> I can make them handy, but when you say they don't work do you >> mean it crashes, or what exactly. I ask only because the DBMS >> seems to have varying support for the method overall. below 7.3 >> not being able to support the call at all, below 8.0 not >> supporting argument names and below 8.1 not supporting argument >> modes. when I say not supporting, I mean there is not column for >> the data in 'pg_proc'. I didn't actually try this against those >> over servers, but looked it up in the respective documentation of >> each server version. I suppose that information might reside in >> another table, I will have to install the other versions and find >> out. > > 1) Your tests try to create functions with named and output > parameters unconditionally. These can't be created on older servers. > ok will change > 2) The code you've added to TestUtil uses dollar quoting which > isn't available on all servers. > ok will change it. > 3) The code in getProcedureColumns fails with a "couldn't find a > column named ..." for older server versions. You need to either > add ", NULL as colname" to the sql for older versions or not fetch > these columns unconditionally. > yea.. I see that now. So in the end, what you are saying is stop being a ninny and fire up more versions of the DBMS and properly test this... sorry about that. Do you all have a recommended test setup. (i.e. do you have every version up and running?) Jeff >>> 1) It's better to send all your changes as one patch instead of >>> one per file. It makes it easier to apply. >> >> good to know, would you like me to resend them? >> > > No, that's just a minor inconvenience, not an actual problem. > > Kris Jurka
On Wed, 14 Feb 2007, Jeffrey Cox wrote: > So in the end, what you are saying is stop being a ninny and fire up more > versions of the DBMS and properly test this... sorry about that. Do you all > have a recommended test setup. (i.e. do you have every version up and > running?) > I'm saying, I know how much of a pain this is to test on all versions, so if you're not up to it, I'll do it. I'm just not currently at a machine that has all of them installed. And yes, I do have 7.2 - 8.3dev all available (and sometimes even multiple versions of them for things like --enable-integer-datetimes) and the regression tests should pass against all of them. Kris Jurka
On Feb 14, 2007, at 12:47 PM, Kris Jurka wrote: > > > On Wed, 14 Feb 2007, Jeffrey Cox wrote: > >> So in the end, what you are saying is stop being a ninny and fire >> up more versions of the DBMS and properly test this... sorry about >> that. Do you all have a recommended test setup. (i.e. do you have >> every version up and running?) >> > > I'm saying, I know how much of a pain this is to test on all > versions, so if you're not up to it, I'll do it. I'm just not > currently at a machine that has all of them installed. And yes, I > do have 7.2 - 8.3dev all available (and sometimes even multiple > versions of them for things like --enable-integer-datetimes) and > the regression tests should pass against all of them. > I know it's a pain, but I will test it... I just need to get the all the servers up and running. It might take me some time to get them up and going, but shouldn't be to troubling. I was just making light of me being sloppy and not fully testing the patches I submitted. I hope you didn't take offense... Anyhow, I will send you a new patch soon as I test across all version. Jeff > Kris Jurka
On Wed, 14 Feb 2007, Jeffrey Cox wrote: > I know it's a pain, but I will test it... I just need to get the all the > servers up and running. It might take me some time to get them up and going, > but shouldn't be to troubling. Here's a patch that I based off of yours. For 8.1 code we need to look at proallargtypes instead of just proargtypes to get the correct output types. I haven't really tested it, but it looks correct. Any progress on setting up test cases for different server versions? Kris Jurka
Attachment
Hi,
Did this patch ever make it into the codebase? I'm interested in getting
the actual names declared when the function was declared rather than the $x
placeholders. Currently testing with postgresql-8.2-505.jdbc3.jar and this
change doesn't seem to be in there.
Thanks,
Thomas Risberg
Kris Jurka wrote:
>
>
>
> On Wed, 14 Feb 2007, Jeffrey Cox wrote:
>
>> I know it's a pain, but I will test it... I just need to get the all the
>> servers up and running. It might take me some time to get them up and
>> going,
>> but shouldn't be to troubling.
>
> Here's a patch that I based off of yours. For 8.1 code we need to look at
> proallargtypes instead of just proargtypes to get the correct output
> types. I haven't really tested it, but it looks correct. Any progress on
> setting up test cases for different server versions?
>
> Kris Jurka
> ? .build.local.properties.swp
> ? build.local.properties
> ? getproccol.patch
> Index: org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java
> ===================================================================
> RCS file:
> /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java,v
> retrieving revision 1.33
> diff -c -r1.33 AbstractJdbc2DatabaseMetaData.java
> *** org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java 1 Dec 2006
> 08:53:45 -0000 1.33
> --- org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java 28 Feb 2007
> 07:39:43 -0000
> ***************
> *** 1724,1731 ****
> String sql;
> if (connection.haveMinimumServerVersion("7.3"))
> {
> ! sql = "SELECT
> n.nspname,p.proname,p.prorettype,p.proargtypes, t.typtype,t.typrelid " +
> ! " FROM pg_catalog.pg_proc p,pg_catalog.pg_namespace n,
> pg_catalog.pg_type t " +
> " WHERE p.pronamespace=n.oid AND p.prorettype=t.oid ";
> if (schemaPattern != null && !"".equals(schemaPattern))
> {
> --- 1724,1739 ----
> String sql;
> if (connection.haveMinimumServerVersion("7.3"))
> {
> ! sql = "SELECT
> n.nspname,p.proname,p.prorettype,p.proargtypes, t.typtype,t.typrelid ";
> !
> ! if (connection.haveMinimumServerVersion("8.1"))
> ! sql += ", p.proargnames, p.proargmodes, p.proallargtypes
> ";
> ! else if (connection.haveMinimumServerVersion("8.0"))
> ! sql += ", p.proargnames, NULL AS proargmodes, NULL AS
> proallargtypes ";
> ! else
> ! sql += ", NULL AS proargnames, NULL AS proargmodes, NULL
> AS proallargtypes ";
> !
> ! sql += " FROM pg_catalog.pg_proc p, pg_catalog.pg_namespace
> n, pg_catalog.pg_type t " +
> " WHERE p.pronamespace=n.oid AND p.prorettype=t.oid ";
> if (schemaPattern != null && !"".equals(schemaPattern))
> {
> ***************
> *** 1739,1745 ****
> }
> else
> {
> ! sql = "SELECT NULL AS
> nspname,p.proname,p.prorettype,p.proargtypes, t.typtype,t.typrelid " +
> " FROM pg_proc p,pg_type t " +
> " WHERE p.prorettype=t.oid ";
> if (procedureNamePattern != null)
> --- 1747,1753 ----
> }
> else
> {
> ! sql = "SELECT NULL AS
> nspname,p.proname,p.prorettype,p.proargtypes,t.typtype,t.typrelid, NULL AS
> proargnames, NULL AS proargmodes, NULL AS proallargtypes " +
> " FROM pg_proc p,pg_type t " +
> " WHERE p.prorettype=t.oid ";
> if (procedureNamePattern != null)
> ***************
> *** 1757,1768 ****
> int returnType = rs.getInt("prorettype");
> String returnTypeType = rs.getString("typtype");
> int returnTypeRelid = rs.getInt("typrelid");
> String strArgTypes = rs.getString("proargtypes");
> StringTokenizer st = new StringTokenizer(strArgTypes);
> Vector argTypes = new Vector();
> while (st.hasMoreTokens())
> {
> ! argTypes.addElement(new Integer(st.nextToken()));
> }
>
> // decide if we are returning a single column result.
> --- 1765,1796 ----
> int returnType = rs.getInt("prorettype");
> String returnTypeType = rs.getString("typtype");
> int returnTypeRelid = rs.getInt("typrelid");
> +
> String strArgTypes = rs.getString("proargtypes");
> StringTokenizer st = new StringTokenizer(strArgTypes);
> Vector argTypes = new Vector();
> while (st.hasMoreTokens())
> {
> ! argTypes.addElement(new Long(st.nextToken()));
> ! }
> !
> ! String argNames[] = null;
> ! Array argNamesArray = rs.getArray("proargnames");
> ! if (argNamesArray != null)
> ! argNames = (String[])argNamesArray.getArray();
> !
> ! String argModes[] = null;
> ! Array argModesArray = rs.getArray("proargmodes");
> ! if (argModesArray != null)
> ! argModes = (String[])argModesArray.getArray();
> !
> ! int numArgs = argTypes.size();
> !
> ! long allArgTypes[] = null;
> ! Array allArgTypesArray = rs.getArray("proallargtypes");
> ! if (allArgTypesArray != null) {
> ! allArgTypes = (long[])allArgTypesArray.getArray();
> ! numArgs = allArgTypes.length;
> }
>
> // decide if we are returning a single column result.
> ***************
> *** 1786,1807 ****
> }
>
> // Add a row for each argument.
> ! for (int i = 0; i < argTypes.size(); i++)
> {
> - int argOid =
> ((Integer)argTypes.elementAt(i)).intValue();
> byte[][] tuple = new byte[13][];
> tuple[0] = null;
> tuple[1] = schema;
> tuple[2] = procedureName;
> ! tuple[3] = connection.encodeString("$" + (i + 1));
> ! tuple[4] =
> connection.encodeString(Integer.toString(java.sql.DatabaseMetaData.procedureColumnIn));
> tuple[5] =
> connection.encodeString(Integer.toString(connection.getSQLType(argOid)));
> tuple[6] =
> connection.encodeString(connection.getPGType(argOid));
> tuple[7] = null;
> tuple[8] = null;
> tuple[9] = null;
> tuple[10] = null;
> ! tuple[11] =
> connection.encodeString(Integer.toString(java.sql.DatabaseMetaData.procedureNullableUnknown));
> tuple[12] = null;
> v.addElement(tuple);
> }
> --- 1814,1852 ----
> }
>
> // Add a row for each argument.
> ! for (int i = 0; i < numArgs; i++)
> {
> byte[][] tuple = new byte[13][];
> tuple[0] = null;
> tuple[1] = schema;
> tuple[2] = procedureName;
> !
> ! if (argNames != null)
> ! tuple[3] = connection.encodeString(argNames[i]);
> ! else
> ! tuple[3] = connection.encodeString("$" + (i + 1));
> !
> ! int columnMode = DatabaseMetaData.procedureColumnIn;
> ! if (argModes != null && argModes[i].equals("o"))
> ! columnMode = DatabaseMetaData.procedureColumnOut;
> ! else if (argModes != null && argModes[i].equals("b"))
> ! columnMode = DatabaseMetaData.procedureColumnInOut;
> !
> ! tuple[4] =
> connection.encodeString(Integer.toString(columnMode));
> !
> ! int argOid;
> ! if (allArgTypes != null)
> ! argOid = (int)allArgTypes[i];
> ! else
> ! argOid = ((Long)argTypes.elementAt(i)).intValue();
> !
> tuple[5] =
> connection.encodeString(Integer.toString(connection.getSQLType(argOid)));
> tuple[6] =
> connection.encodeString(connection.getPGType(argOid));
> tuple[7] = null;
> tuple[8] = null;
> tuple[9] = null;
> tuple[10] = null;
> ! tuple[11] =
> connection.encodeString(Integer.toString(DatabaseMetaData.procedureNullableUnknown));
> tuple[12] = null;
> v.addElement(tuple);
> }
> ***************
> *** 1830,1835 ****
> --- 1875,1881 ----
> tuple[12] = null;
> v.addElement(tuple);
> }
> + columnrs.close();
> }
> }
> rs.close();
> Index: org/postgresql/jdbc2/TypeInfoCache.java
> ===================================================================
> RCS file:
> /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/TypeInfoCache.java,v
> retrieving revision 1.7
> diff -c -r1.7 TypeInfoCache.java
> *** org/postgresql/jdbc2/TypeInfoCache.java 19 Feb 2007 05:57:53 -0000 1.7
> --- org/postgresql/jdbc2/TypeInfoCache.java 28 Feb 2007 07:39:43 -0000
> ***************
> *** 57,62 ****
> --- 57,63 ----
> {"numeric", new Integer(Oid.NUMERIC), new
> Integer(Types.NUMERIC), "java.math.BigDecimal"},
> {"float4", new Integer(Oid.FLOAT4), new Integer(Types.REAL),
> "java.lang.Float"},
> {"float8", new Integer(Oid.FLOAT8), new Integer(Types.DOUBLE),
> "java.lang.Double"},
> + {"char", new Integer(Oid.CHAR), new Integer(Types.CHAR),
> "java.lang.String"},
> {"bpchar", new Integer(Oid.BPCHAR), new Integer(Types.CHAR),
> "java.lang.String"},
> {"varchar", new Integer(Oid.VARCHAR), new
> Integer(Types.VARCHAR), "java.lang.String"},
> {"text", new Integer(Oid.TEXT), new Integer(Types.VARCHAR),
> "java.lang.String"},
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
--
View this message in context: http://www.nabble.com/getProcedureColumns-tf3147414.html#a11398134
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
On Mon, 2 Jul 2007, thomas.risberg wrote: > Did this patch ever make it into the codebase? I'm interested in getting > the actual names declared when the function was declared rather than the $x > placeholders. Currently testing with postgresql-8.2-505.jdbc3.jar and this > change doesn't seem to be in there. > It has not made it into the driver yet because I haven't done the testing against all our supported server versions yet. In any case it will not appear in the 8.2 series release because it is a new feature and will instead appear in the 8.3 release. Kris Jurka
Thanks Kris. I'll keep an eye on the 8.3dev drivers.
A related question - I have the following proc declaration:
create function my_proc(in_text in varchar, in_number in numeric, out_result
out varchar, out_text out varchar) RETURNS record
Since there are multiple out parameters it seemed as if I was forced to
declare the return as a record. Is this assumption correct?
Looking at the metadata for this proc I see:
- my_proc 5 1111 record
- my_proc 1 12 varchar
- my_proc 1 2 numeric
but this doesn't seem to be enough tp actually build the call statement and
executing the proc. Using con.prepareCall("{? = call my_proc(?, ?)}") - I
get org.postgresql.util.PSQLException: A CallableStatement was excecuted
with an invalid number of parameters.
Switching to con.prepareCall("{? = call my_proc(?, ?, ?)}") I can get it to
work but only by registering two out parameters of Types.VARCHAR. So it
seems that it is not possible to construct the appropriate call sequence
just based on the metadata when there are multiple out parameters.
Any insights are appreciated.
Thanks,
Thomas Risberg
Kris Jurka wrote:
>
>
>
> On Mon, 2 Jul 2007, thomas.risberg wrote:
>
>> Did this patch ever make it into the codebase? I'm interested in getting
>> the actual names declared when the function was declared rather than the
>> $x
>> placeholders. Currently testing with postgresql-8.2-505.jdbc3.jar and
>> this
>> change doesn't seem to be in there.
>>
>
> It has not made it into the driver yet because I haven't done the testing
> against all our supported server versions yet. In any case it will not
> appear in the 8.2 series release because it is a new feature and will
> instead appear in the 8.3 release.
>
> Kris Jurka
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>
--
View this message in context: http://www.nabble.com/getProcedureColumns-tf3147414.html#a11399288
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
On Mon, 2 Jul 2007, thomas.risberg wrote: > So it seems that it is not possible to construct the appropriate call > sequence just based on the metadata when there are multiple out > parameters. > Right, the current metadata returned is based on the pg_proc.argtypes field which only contains the in and in/out parameters to a function. A new column pg_proc.allargtypes was added in support of out functions and the driver was never updated to use it. So that should be part of the fixes that the patch under discussion covers. If your willing to do some testing I've built a driver version with the patch included and put it up here: http://www.ejurka.com/pgsql/jars/tr/ Kris Jurka
Kris, Thanks for the patched driver. It worked great for my simple test case. Any idea when this will be released or in beta? Thanks, Thomas Risberg Kris Jurka wrote: > > > > On Mon, 2 Jul 2007, thomas.risberg wrote: > >> So it seems that it is not possible to construct the appropriate call >> sequence just based on the metadata when there are multiple out >> parameters. >> > > Right, the current metadata returned is based on the pg_proc.argtypes > field which only contains the in and in/out parameters to a function. A > new column pg_proc.allargtypes was added in support of out functions and > the driver was never updated to use it. So that should be part of the > fixes that the patch under discussion covers. If your willing to do some > testing I've built a driver version with the patch included and put it up > here: > > http://www.ejurka.com/pgsql/jars/tr/ > > Kris Jurka > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > > -- View this message in context: http://www.nabble.com/getProcedureColumns-tf3147414.html#a11399776 Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
On Mon, 2 Jul 2007, thomas.risberg wrote: > Thanks for the patched driver. It worked great for my simple test case. > Any idea when this will be released or in beta? > My goal is to integrate it into the 8.3dev series sometime around 7/10-7/15. The official release of the 8.3 driver series will coincide with the release of the 8.3 server whose release date is a little up in the air at the moment. October? Kris Jurka
Thanks for the dates, looks good to me. Now an unrelated question -- when will you include support for Statement.getGeneratedKeys()? I've seen some discussions on the list but no recent status for this feature. Thanks, Thomas Kris Jurka wrote: > > > > On Mon, 2 Jul 2007, thomas.risberg wrote: > >> Thanks for the patched driver. It worked great for my simple test case. >> Any idea when this will be released or in beta? >> > > My goal is to integrate it into the 8.3dev series sometime around > 7/10-7/15. The official release of the 8.3 driver series will coincide > with the release of the 8.3 server whose release date is a little up in > the air at the moment. October? > > Kris Jurka > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > > -- View this message in context: http://www.nabble.com/getProcedureColumns-tf3147414.html#a11400237 Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
On Mon, 2 Jul 2007, thomas.risberg wrote: > > When will you include support for Statement.getGeneratedKeys()? I've > seen some discussions on the list but no recent status for this feature. > Not entirely sure about that. Previously a patch was posted to work in limited circumstances, but I don't recall how limited the circumstances were. I do recall that the patch was pretty rough and needed significant additional work. I will review that patch again around mid July and commit it or post an updated status. Kris Jurka
On Wed, 28 Feb 2007, Kris Jurka wrote: > Here's a patch that I based off of yours. For 8.1 code we need to look at > proallargtypes instead of just proargtypes to get the correct output types. > I haven't really tested it, but it looks correct. Any progress on setting up > test cases for different server versions? > I've committed this to 8.3dev. Kris Jurka