Re: selecting a particular record from a derby table

[Zorro]

On 12/14/18 5:12 AM, Bob M wrote:

Thanks Bryan

I have rewritten the code as follows
*
  // if we have a candidate_ID to be validated, update Prototype record with
Occurances
if (Candidate_ID_to_be_validated > 0) {
 fw.writetoFile(("we get to here - 1"), File_Name);
 // update Occurances field in the similar Prototypes record by 1
 psUpdate = conn.prepareStatement("UPDATE PROTOTYPES SET Occurances =
Occurances + 1 WHERE Prototype_ID = ?");
 fw.writetoFile(("we get to here - 2"), File_Name);
 ps.setInt(1,Candidate_ID_to_be_validated);
 psUpdate.executeUpdate();
 // commit the above transactions
 conn.commit();
 fw.writetoFile(("Updated Prototypes record with Occurances"),
File_Name);
 fw.writetoFile(("and Occurances is : - " + (Occurances + 1)),
File_Name);
***
Candidate_ID_to_be_validated = 3
The last entry in the log file is "we get to here - 2"

and I get a NullPointerException error - not exactly sure which line is
creating this error

Bob M



Shouldn't you use psUpdate.setInt(1,Candidate_ID_to_be_validated);

Harm-Jan Z

Re: Is this code correct ?

[Zorro]

On 12/3/18 2:23 AM, Bob M wrote:

The program actions line 8 where it prints out the penultimate record Date
and Time

However, the next line - rs = s.executeQuery("SELECT...FOR UPDATE"); is
not working

I can not understand why ?

Bob M


rs = s.executeQuery("SELECT p0 FROM HOURLY_HISTORY WHERE TRADING_DATE =
Date_temp AND TRADING_TIME = Time_temp FOR UPDATE");

I think you should use string concatenation for the above statement or use a 
prepare to have the content of Time_temp and Date_temp in the query.

Regards,
Harm-Jan Z.

Re: Installing Derby

[Zorro]

On 11/28/18 4:07 AM, Bob M wrote:

Hi
Am following the instructions
All going well until I attempt to create my first database

I receive the following message
***
C:\>java org.apache.derby.tools.ij
ij version 10.14
ij> connect 'jdbc:derby:myFirstDBase;create=true';
Wed Nov 28 15:55:44 NZDT 2018 Thread[main,5,main]
java.io.FileNotFoundException: derby.log (Access is denied)
***
what have I forgotten to do ?
Bob M


Looks like derby cannot create the derby.log file.
Maybe a missing environment variable ?

Derby comes with a number of scripts in the bin directory.
You could try to start ij using the corresponding script ij/ij.bat.
The ij/ij.bat script will attempt to set the required environment variables.

Regards, Harm-Jan Z.

Re: SQL help needed

[Zorro]

Op 27-11-2016 om 11:13 schreef John English:
I'm trying to find all rows in a table where a pair of values is not 
in anther table: that is, I want to do something like this:


  SELECT * FROM x WHERE (a,b) NOT IN (SELECT DISTINCT a,b FROM y);

which of course doesn't work.

At the moment I've bodged around it by doing this:

  SELECT * FROM x WHERE a||'-'||b NOT IN (SELECT DISTINCT a||'-'||b 
FROM y);


but this strikes me as really ugly. Can anyone a more elegant way to 
get what I want?


TIA,


Can't you use an ordinary join ?

Something like
Select x.* From x, y Where x.a = y.a And x.b = y.b

Regards,
Harm-Jan

Re: Identity Increment

[Zorro]

Ruzal Yumaev schreef op 25-3-2015 om 20:55:

Hello!
I'm creating table USERS where primary key ID has autogenerating column:
  ID INT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT
BY 1) PRIMARY KEY
After inserting second or more row ID column increment to 100 and
"INCREMENT BY 1" - it's multipling for one hundred.
Example from table:

ID FIO BIRTHDAY GROUPNUM
1 Name 2012-12-21 21312
101 Name 2001-12-21 23412
201 Name 2001-12-02 21323
I attach log file  and maven dependency is

 org.apache.derby
 derby
 10.11.1.1



Out of curiosity I tried it, also with derby 10.11.1.1, java 1.8.
My code below is your code extended with 3 inserts .

{
String dbURL1 = "jdbc:derby:bd;create=true";
Connection conn1 = null;
try {
conn1 = DriverManager.getConnection(dbURL1);
if (conn1 != null) {
System.out.println("Connected to database");
}
Statement statement = conn1.createStatement();
try {
System.out.println("Create Table USERS");
statement.executeUpdate("CREATE TABLE USERS" +
"(ID INT NOT NULL GENERATED ALWAYS AS 
IDENTITY(START WITH 1, INCREMENT BY 1) PRIMARY KEY, FIO VARCHAR(255) NOT 
NULL," +

"BIRTHDAY DATE, GROUPNUM VARCHAR(15))");
System.out.println("Table USERS created");
} catch (SQLException e) {
System.out.println("Table USERS already exists");
}
statement.execute("Insert Into USERS (FIO, BIRTHDAY, 
GROUPNUM) VALUES('Name', '2012-12-21', '21312')");
statement.execute("Insert Into USERS (FIO, BIRTHDAY, 
GROUPNUM) VALUES('Name', '2012-12-21', '21412')");
statement.execute("Insert Into USERS (FIO, BIRTHDAY, 
GROUPNUM) VALUES('Name', '2012-12-02', '21323')");

}
catch (Exception e) {
System.out.println("Exception" + e.getMessage());
}
}
}

The outcome of a Select * From App.USERS in ij was:
1   |Name  |2012-12-21|21312
2   |Name  |2012-12-21|21412
3   |Name  |2012-12-02|21323

3 rows selected

So seems to work.
Maybe you can try this code also.

Regards,
Harm-Jan Zwinderman

Cebuned.svipr.nl/Cebuned.html

Re: JBoss AS 7 integration with Derby 10.10

[Zorro]

Ajay Kumar Srivastava schreef op 3-4-2014 14:58:
Hi , I am struggling with crating JNDI connection with JBOSS for 
derby. When I try to access table from a Servlet  I use to get error


Caused by: javax.resource.ResourceException: Wrong driver class [class 
org.apache.derby.jdbc.ClientDriver] for this connection URL 
[jdbc:derby:DerbyDB;create=true]


--
With Regard's

Ajay Kumar Srivastava

mail: ajaykumar.srivast...@gmail.com 



I don't know whether this will help you but for Tomcat using the Derby 
NetworkServer I do use the URL: url="jdbc:derby://localhost:1527/derby.db".


Regards,
Harm-Jan Zwinderman

http://Cebuned.svipr.nl/Cebuned.html

Re: accessing records problem

[Zorro]

Bob M schreef op 10-2-2014 13:52:

After the rs = s.executeQuery line I try to print out a message
This message does not get printed out!

OK so if s= null is incorrect, then what should I set s to ?

Bob M
|s = connection.createStatement() in which connection| is the Connection 
object representing your database connection.


See for instance the tutorial at:
http://docs.oracle.com/javase/tutorial/jdbc/basics/processingsqlstatements.html

Regards,
Harm-Jan Zwinderman

http://Cebuned.svipr.nl/Cebuned.html

|| 
 

Re: accessing records problem

[Zorro]

Bob M schreef op 10-2-2014 10:19:

Hi

I am calling derby within a larger java program

Step1: I allocate a database "Connection" object
Step2: I allocate a "Statement" object in the connection
Step 3: I execute a SQL SELECT query, the query result is returned in a
"ResultSet" object
so far so good (I update the latest database record, I add a new record and
I delete the oldest record
I then call a data mining program [Weka]
I setup a few Weka models etc..
I then wish to look at the last 100 records in my database, one at a time
The code I have currently is as follows:-
***
try {
Statement s = null;
ResultSet rs = null;
rs = s.executeQuery ("SELECT".);
int k = 0
while (k < 101) {
rs.next();
...
...
}
k++;
}
}
catch (Exception e)
{
myConsole.getOut().println(e);
}

I get the following error:-
java.lang.NullPointerException

do I need to do something further to read my 100 records?

Bob M


Probably this is not where your program is crashing nevertheless you 
have this:

Statement s = null;
ResultSet rs = null;
rs = s.executeQuery ("SELECT".);

since s = null it will throw a npe.

Regards,
Harm-Jan Zwinderman

http://Cebuned.svipr.nl/Cebuned.html

Re: NullPointerException in Derby 10.9.1.0

[Zorro]

Op 4-12-2012 23:08, Knut Anders Hatlen schreef:

Kim Haase  writes:


Should I file a doc JIRA to clarify that ALTER TABLE adds the new
column at the end?

That would be great.


It seems obvious, but perhaps we should clarify
that a programmatic attempt to change the data type of a column will
still result in the changed column being appended.

I notice it is possible to change the data type of a column using
"ALTER TABLE ALTER column-name SET DATA TYPE", but you can only change
the type to VARCHAR or VARCHAR FOR BIT DATA. Do you know why that is?

We probably only allow changing the maximum length of columns that
already are VARCHAR or VARCHAR FOR BIT DATA. Since changing the maximum
length can be done in the table meta-data without changing the stored
format of each value, that's easier to support than the general case.
For example, changing the type from INT to DOUBLE would require an
update of every row in the table, since they have different formats.
That's my guess, at least.


Hi Knut,

Thanks for looking at this.

As you described it seemed to be related to the Order By in the Select 
phrase of the bulk Insert.


Therefore I removed the Order By of the Select and now the bulk Insert 
went well.


The Order By was not necessary for my conversion.
Out of convenience I used copy/paste to the Insert statement.

I hope my issue helped to clarify the Insert procedure in Derby.

Kind regards,
Harm-Jan Zwinderman

NullPointerException in Derby 10.9.1.0

[Zorro]

Dear All,

When doing in ij a bulk Insert into a table of my Derby database I do 
get a NullPointerException.


I am using the Derby Network Server on Fedora 14 using the OpenJDK 
Runtime Environment (IcedTea6 1.9.10) version "1.6.0_20".


I did the following:
ij> Connect 'jdbc:derby://localhost:1527/derby.db;user=...;password=...' ;
ij> AutoCommit Off ;
ij> Set Schema ExchRates ;

ij> Describe SecurityDaySummary ;
COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
--
SECURITYID |CHAR |NULL|NULL|15 |NULL |30 |NO
TRADINGDATE |DATE |0 |10 |10 |NULL |NULL |NO
VOLUME |INTEGER |0 |10 |10 |NULL |NULL |NO
CLOSINGPRICE |DECIMAL |4 |10 |12 |NULL |NULL |NO
LOWPRICE |DECIMAL |4 |10 |12 |0.0 |NULL |NO
HIGHPRICE |DECIMAL |4 |10 |12 |0.0 |NULL |NO

ij> Select 'NL0010273215', TradingDate,
> INT(Volume * 0.97048368 + 0.5),
> DOUBLE(ClosingPrice * 1.03139545 + 0.5),
> DOUBLE(LowPrice * 1.03139545 + 0.5),
> DOUBLE(HighPrice * 1.03139545 + 0.5)
> From SecurityDaySummary where SecurityID = 'NL0006034001'
> Order By SecurityID, TradingDate ;

.
.
NL0006034001 |2012-11-23|4953569 |46.00028707 |44.5047636675 |46.00028707

4398 rows selected

ij> Insert Into SecurityDaySummary
> (SecurityID, TradingDate, Volume, ClosingPrice, LowPrice, HighPrice)
> Select 'NL0010273215', TradingDate,
> INT(Volume * 0.97048368 + 0.5),
> DOUBLE(ClosingPrice * 1.03139545 + 0.5),
> DOUBLE(LowPrice * 1.03139545 + 0.5),
> DOUBLE(HighPrice * 1.03139545 + 0.5)
> From SecurityDaySummary where SecurityID = 'NL0006034001'
> Order By SecurityID, TradingDate ;

ERROR XJ001: DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ001, SQLERRMC: 
java.lang.NullPointerExceptionXJ001.U


ij> Rollback ;
ERROR 08006: A network protocol error was encountered and the connection 
has been terminated: the requested command encountered an unarchitected 
and implementation-specific condition for which there was no architected 
message (additional information may be available in the derby.log file 
on the server)
ERROR 08003: DERBY SQL error: SQLCODE: -1, SQLSTATE: 08003, SQLERRMC: No 
current connection.


ij> exit ;

The derby.log file showed the following logging:

# cat derby.log
Wed Nov 28 11:47:02 CET 2012 : Apache Derby Network Server - 10.9.1.0 - 
(1344872) started and ready to accept connections on port 1527

Wed Nov 28 11:47:57 CET 2012 : Connection number: 1.

Wed Nov 28 11:47:58 CET 2012:
Booting Derby version The Apache Software Foundation - Apache Derby - 
10.9.1.0 - (1344872): instance a816c00e-013b-46a0-80f7-8c36475a
on database directory /media/sdb1/derby/db/derby.db with class loader 
sun.misc.Launcher$AppClassLoader@affc70

Loaded from access denied (java.lang.RuntimePermission getProtectionDomain)
java.vendor=Sun Microsystems Inc.
user.dir=/tmp/Derby/logs
derby.system.home=/media/sdb1/derby/db
derby.stream.error.file=/tmp/Derby/logs/derby.log
Database Class Loader started - derby.database.classpath=''
Wed Nov 28 11:48:00 CET 2012 Thread[DRDAConnThread_3,5,main] (DATABASE = 
derby.db), (DRDAID = {1}), Apache Derby Network Server connected to 
database derby.db
Wed Nov 28 11:51:03 CET 2012 Thread[DRDAConnThread_3,5,main] (XID = 
1902670), (SESSIONID = 1), (DATABASE = derby.db), (DRDAID = 
.-4255900293765440262{ting Cleanup action starr
Wed Nov 28 11:51:03 CET 2012 Thread[DRDAConnThread_3,5,main] (XID = 
1902670), (SESSIONID = 1), (DATABASE = derby.db), (DRDAID = 
.-4255900293765440262{: Insert Into SecurityDaySummary

(SecurityID, TradingDate, Volume, ClosingPrice, LowPrice, HighPrice)
Select 'NL0010273215', TradingDate,
INT(Volume * 0.97048368 + 0.5),
DOUBLE(ClosingPrice * 1.03139545 + 0.5),
DOUBLE(LowPrice * 1.03139545 + 0.5),
DOUBLE(HighPrice * 1.03139545 + 0.5)
From SecurityDaySummary where SecurityID = 'NL0006034001'
Order By SecurityID, TradingDate
java.lang.NullPointerException
at 
org.apache.derby.impl.store.access.conglomerate.ConglomerateUtil.createFormatIds(Unknown 
Source)

at org.apache.derby.impl.store.access.heap.Heap.create(Unknown Source)
at 
org.apache.derby.impl.store.access.heap.HeapConglomerateFactory.createConglomerate(Unknown 
Source)
at 
org.apache.derby.impl.store.access.RAMTransaction.createConglomerate(Unknown 
Source)
at 
org.apache.derby.impl.sql.execute.TemporaryRowHolderImpl.insert(Unknown 
Source)
at 
org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(Unknown 
Source)

at org.apache.derby.impl.sql.execute.InsertResultSet.open(Unknown Source)
at 
org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown 
Source)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown 
Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown 
Source)

at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedSt