query failing on 10.3.1.4
This query that used to work on the 10.2 version now return no results on 10.3.1.4: SELECT MYTABLE.MY_ID FROM MYTABLE WHERE MYTABLE.MY_ID 100 AND MYTABLE.MY_ID IN ( 2,15,19,20,21,48,49 ) Both parts of the AND expression work OK on their own, but when combined you get 0 rows Thanks Tim
Re: how to remove a column
Rick Yes I am using the older version, not sure which version, but I haven't updated it since January of this year. Due to time constraints i would prefer not to update at this time. Unless it can't be avoided to drop a column that I need dropped. Thanks for your help Rick Hillegas wrote: Hi Brandon, This syntax works in the latest Derby release (10.3.1.4). Are you using an older version of Derby? Thanks, -Rick Brandon Dohman wrote: I was wondering what the sql syntax is to drop a column from a derby database. i tried this and it gave me the error below: ALTER TABLE contacts DROP COLUMN userid Error code -1, SQL state 42X01: Syntax error: Encountered DROP at line 1, column 22. Line 1, column 1 Thanks for your help
Re: how to remove a column
Create another table without the column you want to drop and insert data from old table. On 7.9.2007, at 18:07, Brandon Dohman wrote: Is it possible to remove a column any other way? Without using the drop command? Tim Dudgeon wrote: 10.3 is the first released version to support DROP COLUMN. Prior to 10.3 this was not possible. Tim Brandon Dohman wrote: Rick Yes I am using the older version, not sure which version, but I haven't updated it since January of this year. Due to time constraints i would prefer not to update at this time. Unless it can't be avoided to drop a column that I need dropped. Thanks for your help Rick Hillegas wrote: Hi Brandon, This syntax works in the latest Derby release (10.3.1.4). Are you using an older version of Derby? Thanks, -Rick Brandon Dohman wrote: I was wondering what the sql syntax is to drop a column from a derby database. i tried this and it gave me the error below: ALTER TABLE contacts DROP COLUMN userid Error code -1, SQL state 42X01: Syntax error: Encountered DROP at line 1, column 22. Line 1, column 1 Thanks for your help -- Brandon Dohman Chief Technical Officer Mobile Medical Systems 2612 DeWitt Av Mattoon, IL 61938 815.383.6909 www.mobilemedicalsystems.com www.pen.eiu.edu/~brdohman
Re: how to remove a column
Brandon Dohman wrote: Is it possible to remove a column any other way? Without using the drop command? Create a new table with the correct columns but a different name, copy content of the old table to the new one (except for the unwanted column). Drop any foreign key constraints referencing the old table. drop the old table. Rename the new table to the old name. Add back constraints if required. I guess this wasn't what you wanted to know. Mark Thornton
Re: how to remove a column
Is it possible to remove a column any other way? Without using the drop command? Tim Dudgeon wrote: 10.3 is the first released version to support DROP COLUMN. Prior to 10.3 this was not possible. Tim Brandon Dohman wrote: Rick Yes I am using the older version, not sure which version, but I haven't updated it since January of this year. Due to time constraints i would prefer not to update at this time. Unless it can't be avoided to drop a column that I need dropped. Thanks for your help Rick Hillegas wrote: Hi Brandon, This syntax works in the latest Derby release (10.3.1.4). Are you using an older version of Derby? Thanks, -Rick Brandon Dohman wrote: I was wondering what the sql syntax is to drop a column from a derby database. i tried this and it gave me the error below: ALTER TABLE contacts DROP COLUMN userid Error code -1, SQL state 42X01: Syntax error: Encountered DROP at line 1, column 22. Line 1, column 1 Thanks for your help -- Brandon Dohman Chief Technical Officer Mobile Medical Systems 2612 DeWitt Av Mattoon, IL 61938 815.383.6909 www.mobilemedicalsystems.com www.pen.eiu.edu/~brdohman
Re: query failing on 10.3.1.4
Rick Hillegas wrote: Thanks for finding this bug, Tim. I have verified that this occurs in the development mainline as well. I have created DERBY-3061 to track this issue. OK, thanks. I am watching this issue in JIRA. Its a critical one for me. If it can't be fixed I need to roll back to 10.2 before I release next version of my product. Thanks Tim
how to remove a column
I was wondering what the sql syntax is to drop a column from a derby database. i tried this and it gave me the error below: ALTER TABLE contacts DROP COLUMN userid Error code -1, SQL state 42X01: Syntax error: Encountered DROP at line 1, column 22. Line 1, column 1 Thanks for your help
Re: how to remove a column
Hi Brandon, This syntax works in the latest Derby release (10.3.1.4). Are you using an older version of Derby? Thanks, -Rick Brandon Dohman wrote: I was wondering what the sql syntax is to drop a column from a derby database. i tried this and it gave me the error below: ALTER TABLE contacts DROP COLUMN userid Error code -1, SQL state 42X01: Syntax error: Encountered DROP at line 1, column 22. Line 1, column 1 Thanks for your help
Re: ipv6 and Network Server hostname
Dave Been wrote: for IPv4 we listen on host 0.0.0.0 in case a machine has multiple NIC cards, so we accept connections from all NICs. (I believe this was for windows only. is that correct?) No, it is not for Windows only. As far as I know, it should work the same way on all operating systems. -- John
Re: how to remove a column
Brandon Dohman wrote: Is it possible to remove a column any other way? Without using the drop command? I solved this by building a patched version of the 10.2 releases that included the drop column capability form 10.3 back ported. I can provide you with that derby.jar if that helps. But I would suggest that planning to use 10.3 is a better approach. Tim
Re: jdbc metadata getProcedures does not return newly created proc/funcs
Hi Mike,
Try passing in null as the catalog and schema arguments. The javadoc for
DatabaseMetaData.getProcedures() is a little vague about this.
Hope this helps,
-Rick
Mike Norman wrote:
latest Derby:
Apache Derby
10.3.1.4 - (561794)
Apache Derby Network Client JDBC Driver
10.1.2.1
I created 2 very simple stored proc/funcs:
package test;
import java.sql.SQLException;
public class SPHelper {
public static void ProcNoArgs() throws SQLException {
}
public static String FuncNoArgs() throws SQLException {
return gidday;
}
}
CREATE PROCEDURE PROC_NO_ARGS()
LANGUAGE JAVA
EXTERNAL NAME 'test.SPHelper.ProcNoArgs'
PARAMETER STYLE JAVA
NO SQL;
CREATE FUNCTION FUNC_NO_ARGS() RETURNS VARCHAR(100)
LANGUAGE JAVA
EXTERNAL NAME 'test.SPHelper.FuncNoArgs'
PARAMETER STYLE JAVA
NO SQL
RETURNS NULL ON NULL INPUT;
Now, when I try to get the metadata for them, nothing is returned:
String catalogPattern = ;
String schemaPattern = %;
String procedurePattern = %NO_ARGS;
ListDbStoredProcedure tmpProcs
try {
DatabaseMetaData databaseMetaData = getDatabaseMetaData(connection);
procsInfo = databaseMetaData.getProcedures(catalogPattern,
schemaPattern, procedurePattern);
// did we get a hit?
if (procsInfo != null) {
tmpProcs = new ArrayListDbStoredProcedure();
while (procsInfo.next()) {
String actualCatalogName = procsInfo.getString(PROCS_INFO_CATALOG);
//
index 1
String actualSchemaName = procsInfo.getString(PROCS_INFO_SCHEMA); //
index 2
String actualProcedureName = procsInfo.getString(PROCS_INFO_NAME);
//
index 3
short procedureType = procsInfo.getShort(PROCS_INFO_TYPE); // index
8
DbStoredProcedure dbStoredProcedure;
if (procedureType ==
java.sql.DatabaseMetaData.procedureReturnsResult)
{
dbStoredProcedure = new DbStoredFunction(actualProcedureName);
}
else {
dbStoredProcedure = new DbStoredProcedure(actualProcedureName);
}
if (actualCatalogName != null actualCatalogName.length() 0) {
dbStoredProcedure.setCatalog(actualCatalogName);
}
if (actualSchemaName != null actualSchemaName.length() 0) {
dbStoredProcedure.setSchema(actualSchemaName);
}
tmpProcs.add(dbStoredProcedure);
}
procsInfo.close();
}
catch (SQLException sqlException) {
throw new IllegalStateException(failure retrieving Stored Procedure
metadata,
sqlException);
}
No exceptions are thrown but the list is empty - an empty result set was
returned.
Any ideas?
Thanks in advance,
Mike
Re: Derby 10.3.1.4 not working with ibatis-2.3.0.677
Zsolt Koppany [EMAIL PROTECTED] writes: Hi, I tried to upgrade to derby 10.3.1.4 but I get strange error messages that I didn't get with derby 10.2.2.0. I use 10.3.1.4 with ibatis-2.3.0.677. Any ideas? [...] --- Cause: java.sql.SQLException: An attempt was made to get a data value of type 'UNKNOWN' from a data value of type 'VARCHAR'. [...] at org.apache.derby.client.am.PreparedStatement.setNull(Unknown Source) Hi, It seems like your application calls PreparedStatement.setNull() with an invalid type specification. If you change the type to java.sql.Types.VARCHAR, it will probably work. This was changed in 10.3.1.4 to make the network client driver and the embedded driver behave the same way. Please see this section in the release notes for details: http://db.apache.org/derby/releases/release-10.3.1.4.cgi#N10C87 -- Knut Anders
Compressing derby databases
Hello, Maybe I have just missed this in the archives... I am creating a Java desktop app that will only perform read-only accesses (selects only) on some derby databases. I would like to compress the databases using either jar or pack200. But, does derby support databases stored this way? If so, can someone please point me to a page that explains how to do this? Thanks, Kevin Shape Yahoo! in your own image. Join our Network Research Panel today! http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7
Re: how to remove a column
what is the process for upgrading from one db version to another. i looked over the guides but didn't see anything for it, potentially i missed it. Thanks for all the help on this topic. - Original Message - From: Tim Dudgeon [EMAIL PROTECTED] To: derby-user@db.apache.org Sent: Fri, 7 Sep 2007 10:34:34 -0500 (CDT) Subject: Re: how to remove a column Brandon Dohman wrote: Is it possible to remove a column any other way? Without using the drop command? I solved this by building a patched version of the 10.2 releases that included the drop column capability form 10.3 back ported. I can provide you with that derby.jar if that helps. But I would suggest that planning to use 10.3 is a better approach. Tim
Re: how to remove a column
Hi Brandon, This is described in the Derby Developer's Guide, http://db.apache.org/derby/docs/10.3/devguide/ The left pane is a table of contents--near the top you will see a link for the section on Upgrade. Hope this helps, -Rick Brandon Dohman wrote: what is the process for upgrading from one db version to another. i looked over the guides but didn't see anything for it, potentially i missed it. Thanks for all the help on this topic. - Original Message - From: Tim Dudgeon [EMAIL PROTECTED] To: derby-user@db.apache.org Sent: Fri, 7 Sep 2007 10:34:34 -0500 (CDT) Subject: Re: how to remove a column Brandon Dohman wrote: Is it possible to remove a column any other way? Without using the drop command? I solved this by building a patched version of the 10.2 releases that included the drop column capability form 10.3 back ported. I can provide you with that derby.jar if that helps. But I would suggest that planning to use 10.3 is a better approach. Tim
Re: Compressing derby databases
Hi Kevin, Derby does support databases stored in jar files. This is described in the Derby Developer's Guide (http://db.apache.org/derby/docs/10.3/devguide/) in a subsection called Accessing databases from a jar or zip file. Just follow these headings to get to that subsection: JDBC applications and Derby basics Derby embedded basics Connecting to databases Special database access Accessing databases from a jar or zip file Hope this helps, -Rick Kevin M. wrote: Hello, Maybe I have just missed this in the archives... I am creating a Java desktop app that will only perform read-only accesses (selects only) on some derby databases. I would like to compress the databases using either jar or pack200. But, does derby support databases stored this way? If so, can someone please point me to a page that explains how to do this? Thanks, Kevin Shape Yahoo! in your own image. Join our Network Research Panel today! http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7
Using class org.apache.derby.jdbc.EmbeddedDataSource40 From NetBeans
My Java DB installation directory is d:/javadb. I have a database d:/DERBYDBS/BdsDemoDb. I want to connect to that database using class org.apache.derby.jdbc.EmbeddedDataSource40 (A Java DB class implementing interface javax.sql.DataSource) and JNDI. Someone kindly suggest how do I do that using NetBeans5.5. [NOTE: 1 I know how to connect to the database using class org.apache.derby.jdbc.EmbeddedDataSource40 and JNDI from manually written code (without using IDE). I want to know how to do it using NetBeans5.5. 2Kindly do not suggest using org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource40 or any other ConnectionPoolDataSource since I need to connect from a stand alone GUI application. 3 Kindly do not suggest using DriverManager class. I know how to do that. ] Thanks and regards. -- View this message in context: http://www.nabble.com/Using-class-org.apache.derby.jdbc.EmbeddedDataSource40-From-NetBeans-tf4403864.html#a12563585 Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: need to *set* REMARKS field of DatabaseMetaData
Hi Geoff, Several DatabaseMetaData methods return ResultSets which have a column named REMARKS. For instance, getColumns(), getTables(), and getProcedures() return ResultSets which contain a REMARKS column. The contents of these columns are determined by queries stored in the following Derby source file: java/engine/org/apache/derby/impl/jdbc/metadata.properties. In order to change the contents of these columns, you would have to edit those queries and build your own Derby engine. Hope this helps, -Rick Geoff hendrey wrote: I want to use the REMARKS column of DatabaseMetaData to store remarks about a column. I see how to retrieve the REMARKS, but how would I go about *setting* the REMARKS. -geoff
