Tim Dudgeon | 19 Dec 12:29 2013

varargs with functions

I'm struggling with getting a function working that uses a Java static 
method that uses varargs.
I understand this should work with 10.10.1.1?

I create function like this:

CREATE FUNCTION FORMAT_CPD_CODE
( FORMAT VARCHAR(100), VAL INT ... )
RETURNS VARCHAR(20)
PARAMETER STYLE DERBY
NO SQL LANGUAGE JAVA
EXTERNAL NAME 'java.lang.String.format'

and use it like this:

values(FORMAT_CPD_CODE('XYZ%08d', 123))

But I get error:

java.sql.SQLSyntaxErrorException: No method was found that matched the 
method call java.lang.String.format(java.lang.String, int...), tried all 
combinations of object and primitive types and any possible type 
conversion for any  parameters the method call may have. The method 
might exist but it is not public and/or static, or the parameter types 
are not method invocation convertible.

Any suggestions on how to get this working?

Thanks.

(Continue reading)

irajon | 16 Dec 23:46 2013

Database Table Null Pointer Exception

I use Derby as an embedded database.  My program Is sent to clients with the
database.  I have a client that installed an update to my software and
somehow, a new table was generated incorrectly.  The program did a create
table, but when I look at this client's database, I don't see the new table. 
When I try to create the table in the client's database, it says that the
database already exists, and when I try to drop the table, it gives me a
null pointer exception.  
I ran the following sql query select * from SYS.SYSCONGLOMERATES left outer
join sys.systables on SYS.SYSCONGLOMERATES.tableid = sys.systables.tableid. 
this produced a listing of all of my tables and conglomerates etc... 
There is an extra line in the result that has a conglomerate, but no table
data.  I'm assuming, this is the corruption.  
Please let me know if this is correct, and how I can fix the issue.  I have
been working on trying to export the data, and then bring the data back to a
new uncorrupted database, but there is a lot of work involved because of
foreign keys and such.  

Thanks

--
View this message in context: http://apache-database.10148.n7.nabble.com/Database-Table-Null-Pointer-Exception-tp136115.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Lasantha Siriwardana | 13 Dec 02:36 2013
Picon

Derby In-Memory DB is not using any available indexes

Hi All,

I've setup an derby in-memory db using derby distribution 10.10.1.1. Below are the configurations;
Max Heap Size - 3GB
Page Cache Size - 10000
I stored nearly one million records in a table with around 50 columns and trying to retrieve the data. But the query takes ~10sec. When I check the explain plan, It's not used any index and the optimizer cost is very higher. Also the scan type was heap. Please help to resolve this issue and tuneup the query. Why doesn't derby in-memory use indexes?

Lasantha
nglasantha | 11 Dec 14:27 2013
Picon

Derby logs are not getting printed

Hi all,

I have configured derby logs, but logs are not getting printed. please help
to resolve this issue.

I tried to search the answer. Everybody is saying just add following entries
to property file. Still am not able to see log entries.

derby.language.logStatementText=true
derby.stream.error.logSeverityLevel=0

Derby version : derby-10.10.1.1 

Am i missing something ?

Thanks
Lasantha

--
View this message in context: http://apache-database.10148.n7.nabble.com/Derby-logs-are-not-getting-printed-tp136011.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Frans Verster | 7 Dec 11:08 2013
Picon

Java DB / Apache Derby Table Functions,current user?

Hi Guys and Girls,

Sorry to bother.. I am making some Java DB / Apache Derby Table Functions, working very nice. I need to know the user querying the function to return only his/her items. How can I get the session/login/current user?

Thanks in advance

Frans

True||False | 6 Dec 07:33 2013

Retrieving constraint columns from system tables

Hi,

I'm trying to retrieve the columns for constraints (PK, FK, unique, check) from system tables and looking through the documentation I cannot see how I can get that information. SYS.SYSCONSTRAINTS does not contain column information. I normally would expect something like SYS.CONSTRAINTCOLUMNS.

For example,I want to know which column belong to for the primary key. I find the tables in sys schema then I can get all the primary key info. The SQL is:

select t.tablename, conglomeratename backIdxName, cst.constraintname, cst.type from sys.systables t, sys.sysconstraints cst, sys.sysconglomerates cgl, sys.syskeys sk where isindex = 'TRUE' and cgl.tableid = t.tableid and (sk.constraintid = cst.constraintid and cst.type = 'P' and sk.conglomerateid = cgl.conglomerateid) and t.tableid = cst.tableid and t.tabletype = 'T'

All the primary key is query out, but I wanna know which column belong to for the primary key. There's no column info in sys.sysconstraints..


I must be missing something obvious here. The same is true for indexes. I cannot find any information regarding them either in the system tables. Is there any other way to retrieve the information? java.sql.DatabaseMetadata only does part of the job.

Thanks in advance,

Bennie.

Bob M | 3 Dec 09:41 2013
Picon

Problems still in retrieving 'oldest' record and 'newest' record

Hi again

My code currently does three transactions
1) updates the 'newest' record
2) inserts/appends a brand new record
3) deletes the 'oldest' record

Of these three only number 2 is working correctly

1) my code does NOT identify the 'newest' record correctly

3) my code does NOT identify the 'oldest' record correctly

My records use the first two fields as the unique key
The first field is a date field and the second is a time field [the second
only has 4 possible values 0, 6, 12, and 18]

My code for (1) above is as follows.......................
****************************************************************************** 
// retrieve the last record from the table 
rs = s.executeQuery("SELECT * FROM USD_JPY ORDER BY Date DESC, Time DESC
FETCH FIRST ROW ONLY"); 
rs.next(); 
String Date1 = rs.getString("Date"); 
String Time1 = rs.getString("Time"); 

myConsole.getOut().println("Latest record:- Date/Time: " + Date1 + ", " +
Time1); 

// Update this record by adding predicted return and predicted class 
psUpdate = conn.prepareStatement("UPDATE USD_JPY SET
Return_predicted=?,Class_predicted=? WHERE Date = '" + Date1 +"' AND Time = 
" + Time1); 
statements.add(psUpdate); 

psUpdate.setDouble(1, return_current); 
psUpdate.setString(2, class_current); 
psUpdate.executeUpdate();

myConsole.getOut().println("Updated latest record with predicted return: " +
return_current + ", predicted class: " + class_current); 

*********************************************************** 

My code for (3) above is as follows...............
****************************************************************************** 
// retrieve and output date and time of oldest record from the table 
rs = s.executeQuery("SELECT * FROM USD_JPY ORDER BY Date ASC, Time ASC FETCH
FIRST ROW ONLY"); 
rs.next(); 
String Date2 = rs.getString("Date"); 
int Time2 = rs.getInt("Time"); 

myConsole.getOut().println("Oldest record:- Date/Time: " + Date2 + ", " +
Time2); 

// and now delete this record............. 

s.setCursorName("MYCURSOR"); 
rs = s.executeQuery("SELECT * from USD_JPY WHERE Date = '" + Date2 + "' AND
Time = " + Time2 
+ " FOR UPDATE"); 
rs.next(); 
conn.prepareStatement("DELETE FROM USD_JPY WHERE CURRENT OF
MYCURSOR").executeUpdate(); 

myConsole.getOut().println("Deleted oldest record"); 
*********************************************************** 

The code shows the 'latest' record as 31-12-2010, 12 and the next one as
31-12-2010, 18
The code shows the 'oldest' record as 01-01-2010, 0 and the next one as
01-01-2010, 6

Any suggestions welcome............

Bob M

--
View this message in context: http://apache-database.10148.n7.nabble.com/Problems-still-in-retrieving-oldest-record-and-newest-record-tp135875.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Bob M | 2 Dec 16:48 2013
Picon

Problem updating a record

Hi there 

I have the following code:- 

****************************************************************************** 
// retrieve the last record from the table 
rs = s.executeQuery("SELECT * FROM USD_JPY ORDER BY Date DESC, Time DESC
FETCH FIRST ROW ONLY"); 
rs.next(); 
String Date1 = rs.getString("Date"); 
String Time1 = rs.getString("Time"); 

myConsole.getOut().println("Date/Time: " + Date1 + ", " + Time1); 

// Update this record by adding predicted return and predicted class 
psUpdate = conn.prepareStatement("UPDATE USD_JPY SET
Return_predicted=?,Class_predicted=?"); 
statements.add(psUpdate); 

psUpdate.setDouble(1, return_current); 
psUpdate.setString(2, class_current); 

conn.commit();

myConsole.getOut().println("Updated latest record with predicted return: " +
return_current + ", predicted class: " + class_current); 

*********************************************************** 

However, the record is NOT being updated as I am wishing it to be ?

Any ideas ?

Bob M

--
View this message in context: http://apache-database.10148.n7.nabble.com/Problem-updating-a-record-tp135856.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Bob M | 2 Dec 16:30 2013
Picon

Problem retrieving the 'oldest' record

I have code as follows:-
*********************
// retrieve and output date and time of oldest record from the table 
rs = s.executeQuery("SELECT * FROM USD_JPY ORDER BY Date ASC, Time ASC FETCH
FIRST ROW ONLY"); 
rs.next(); 
String Date2 = rs.getString("Date"); 
int Time2 = rs.getInt("Time"); 

myConsole.getOut().println("Date/Time: " + Date2 + ", " + Time2); 

// and now delete this record............. 

s.setCursorName("MYCURSOR"); 
rs = s.executeQuery("SELECT * from USD_JPY WHERE Date = '" + Date2 + "' AND
Time = " + Time2 
+ " FOR UPDATE"); 
rs.next(); 
conn.prepareStatement("DELETE FROM USD_JPY WHERE CURRENT OF
MYCURSOR").executeUpdate(); 

myConsole.getOut().println("Deleted oldest record"); 

*********************
My 4,100 records have a date range from 6 Jan 2009 to 10 Dec 2012 and a Time
range going 0, 6, 12, 18, 0 etc. 
The primary key is Date & Time fields 

i.e. the oldest record is 6 Jan 2009 / 0 
then 6 Jan 2009 / 6, 6 Jan 2009 / 12, 6 Jan 2009 / 18, 7 Jan 2009 / 0 etc. 

In my code I am expecting to delete the oldest record each time the process
is run 
However the results I get are :- 

the deleted records are as follows................ 

1 Jan 2010 / 0 
1 Jan 2010 / 6 
1 Jan 2010 / 12 etc etc. 

Any ideas on where my code is incorrect ? 

Bob M

--
View this message in context: http://apache-database.10148.n7.nabble.com/Problem-retrieving-the-oldest-record-tp135855.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Bob M | 2 Dec 00:44 2013
Picon

Commiting several operations

Hi there

I am wishing to commit several add, delete, update operations, after the
following code:-

I added a single line with commit; but got an error message - and so I
clearly don't understand 'commit'

******************************************************************************
// retrieve the last record from the table
rs = s.executeQuery("SELECT * FROM USD_JPY ORDER BY Date DESC, Time DESC
FETCH FIRST ROW ONLY");
rs.next();
String Date1 = rs.getString("Date");
String Time1 = rs.getString("Time");

myConsole.getOut().println("Date/Time: " + Date1 + ", " + Time1);

// Update this record by adding predicted return and predicted class
psUpdate = conn.prepareStatement("UPDATE USD_JPY SET
Return_predicted=?,Class_predicted=?");
statements.add(psUpdate);

psUpdate.setDouble(1, return_current);
psUpdate.setString(2, class_current);

myConsole.getOut().println("Updated latest record with predicted return: " +
return_current + ", predicted class: " + class_current);

// add a new current record to the table

// add the new current record to the dbase
// parameter 1 is Date (varchar), parameter 2 is Time (int)
// parameter 3 is DOW_num (int), parameter 4 is DOW_nom (varchar)
// parameter 5 is Hour_num (int), parameter 6 is Hour_nom (varchar)
// parameter 7 is return (dec), parameter 8 is MA2 (dec)
// parameter 9 is MA6 (dec), parameter 10 is MA10 (dec)
// parameter 11 is class (varchar)

// parameter 12 is Return_predicted (dec)
// parameter 13 is Class_predicted (varchar)

psInsert = conn.prepareStatement("INSERT INTO USD_JPY VALUES (?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?)");
statements.add(psInsert);

psInsert.setString(1, date_current);
psInsert.setInt(2, time_current);
psInsert.setInt(3, DOW_numeric);
psInsert.setString(4, DOW_nominal);
psInsert.setInt(5, Hour_numeric);
psInsert.setString(6, Hour_nominal);
psInsert.setDouble(7, return_current);
psInsert.setDouble(8, MA2);
psInsert.setDouble(9, MA6);
psInsert.setDouble(10, MA10);
psInsert.setString(11, class_current);
psInsert.setDouble(12, return_predicted);
psInsert.setString(13, class_predicted);

myConsole.getOut().println("Inserted new record");

// retrieve and output date and time of oldest record from the table
rs = s.executeQuery("SELECT * FROM USD_JPY ORDER BY Date ASC, Time ASC FETCH
FIRST ROW ONLY");
rs.next();
String Date2 = rs.getString("Date");
int Time2 = rs.getInt("Time");

myConsole.getOut().println("Date/Time: " + Date2 + ", " + Time2);

// and now delete this record.............

s.setCursorName("MYCURSOR");
rs = s.executeQuery("SELECT * from USD_JPY WHERE Date = '" + Date2 + "' AND
Time = " + Time2 
+ " FOR UPDATE");
rs.next();
conn.prepareStatement("DELETE FROM USD_JPY WHERE CURRENT OF
MYCURSOR").executeUpdate();

myConsole.getOut().println("Deleted oldest record");
***********************************************************

Bob M

--
View this message in context: http://apache-database.10148.n7.nabble.com/Commiting-several-operations-tp135841.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

David G.P. | 30 Nov 15:49 2013
Picon

A new "Apache Derby" pronunciation.

Hello all,

I am starting to use Derby for one project, and I just saw that you are collecting pronunciations of that sentence. I just recorded one with Spanish from Spain central-northern accent (Castilla-León). I can't edit the Wiki, so how can I contribute it?

Thanks for all your work and I hope I can contribute more soon.

Gmane