EzDevInfo.com

Derby: CONCAT equivalent

Good day!

What is the equivalent function of Derby/Java DB to the CONCAT function of MySQL?
I tried using CONCAT to Derby but it commits an error.

Does HQL have an equivalent for Restrictions.ilike (for case-insensitive matching)?

I wrote a project for Hibernate+MySQL. Now I'm porting it to Derby (for a number of reasons).

Now I discovered that Derby is case sensitive when using LIKE in queries. This could be solved using Restrictions.ilike(...) in Criteria queries... but I've many complex HQL queries that use that. Is there a way to have a functionality similar to ilike in HQL?

SQLException: No suitable driver found for jdbc:derby://localhost:1527

I get this error in Netbeans:

java.sql.SQLException: No suitable driver found for jdbc:derby://localhost:1527/

How is this caused and how can I solve it?

how to create table if it doesnt exist using Derby Db

I am new to apache derby and I cant seem to make work

    CREATE TABLE IF NOT EXISTS table1 ...

as can be achieved in MySql etc. I am getting a 'Syntax error: Encountered "NOT" at line 1, column 17.', when I try to run this SQL statement in my Java program.

I checked in the documentation page for Derby Db Create Statements, but couldn't find such an alternative.

Experience using Derby or HSQL in production mode [closed]

Anyone ever tried to use Derby or HSQLDB in a production environment? Any good, bad or ugly experiences?

Cannot create JDBC driver of class ' ' for connect URL 'null' : I do not understand this exception

Why does it say null URL and gives a empty ' ' class in the exception when i have provided the database URL ?

I try to connect to derby database via a servlet while using Tomcat. When the servlet gets run,I get the following exceptions :

org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'

at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1452)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1371)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)

at servlets.servlet_1.doGet(servlet_1.java:23) // ---> Marked the statement in servlet

at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:393)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:929)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:405)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:964)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:515)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:302)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.lang.NullPointerException
at sun.jdbc.odbc.JdbcOdbcDriver.getProtocol(JdbcOdbcDriver.java:507)
at sun.jdbc.odbc.JdbcOdbcDriver.knownURL(JdbcOdbcDriver.java:476)
at sun.jdbc.odbc.JdbcOdbcDriver.acceptsURL(JdbcOdbcDriver.java:307)
at java.sql.DriverManager.getDriver(DriverManager.java:253)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1437)
... 24 more

Servlet :

package servlets;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.http.*;
import javax.servlet.*;
import javax.sql.DataSource;

public class servlet_1 extends HttpServlet{

@Override 
public void doGet(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException {
    try {
       // String queryString = request.getQueryString();
        System.out.println("!!!!!!!!!!!!!!!!!!!");
        Context initContext = new InitialContext();
        Context envContext = (Context)initContext.lookup("java:comp/env");
        DataSource ds = (DataSource)envContext.lookup("jdbc/PollDatasource");
        Connection connection = ds.getConnection(); // -->LINE 23
        String sqlQuery = "select * from PollResult";
        PreparedStatement statement = connection.prepareStatement(sqlQuery);
        ResultSet set = statement.executeQuery();
        System.out.println("after the final statement");
    }catch(Exception exc) {
        exc.printStackTrace();
    }
}

}

What exception is this ? What is the reason i get this exception ?

I have added the following tag in context.xml of Tomcat :

<Resource name="jdbc/PollDatasource" auth="Container" type="javax.sql.DataSource"
driverClassName="org.apache.derby.jdbc.EmbeddedDriver"
url="jdbc:derby://localhost:1527/poll_database;create=true"
username="suhail" password="suhail"
maxActive="20" maxIdle="10" maxWait="-1" />

and this in web.xml :

   <resource-ref>
<description>my connection</description>
<res-ref-name>jdbc/PollDatasource</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>

Where am i making a mistake ?

Image that shows the database URL..

NOTE : After the answer by @Bryan Pendleton I changed the driver to org.apache.derby.jdbc.ClientDriver but I get the same exception.

Unable to start derby database from Netbeans 7.4

I downloaded Netbeans 7.4 and Java 7 Update 51. I get the below error when I try to start Java DB or derby connection from Netbeans. This is on a windows 8 PC. I downloaded the version for windows xp 32 bit at work. It works fine. I am not sure what is missing.

Thu Jan 16 00:48:23 EST 2014 : Security manager installed using the Basic server security policy.
Thu Jan 16 00:48:24 EST 2014 : access denied ("java.net.SocketPermission" "localhost:1527" "listen,resolve")
java.security.AccessControlException: access denied ("java.net.SocketPermission" "localhost:1527" "listen,resolve")
at java.security.AccessControlContext.checkPermission(AccessControlContext.java:372)
at java.security.AccessController.checkPermission(AccessController.java:559)
at java.lang.SecurityManager.checkPermission(SecurityManager.java:549)
at java.lang.SecurityManager.checkListen(SecurityManager.java:1134)
at java.net.ServerSocket.bind(ServerSocket.java:375)
at java.net.ServerSocket.<init>(ServerSocket.java:237)
at javax.net.DefaultServerSocketFactory.createServerSocket(ServerSocketFactory.java:231)
at org.apache.derby.impl.drda.NetworkServerControlImpl.createServerSocket(Unknown Source)
at org.apache.derby.impl.drda.NetworkServerControlImpl.access$000(Unknown Source)
at org.apache.derby.impl.drda.NetworkServerControlImpl$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at org.apache.derby.impl.drda.NetworkServerControlImpl.blockingStart(Unknown Source)
at org.apache.derby.impl.drda.NetworkServerControlImpl.executeWork(Unknown Source)

at org.apache.derby.drda.NetworkServerControl.main(Unknown Source)

Getting rid of derby.log

I'm using the Apache Derby embedded database for unit testing in a Maven project. Unfortunately whenever I run the test I end up with the derby.log file in the root of the project. The database itself is created in the target directory (jdbc:derby:target/unittest-db;create=true) so that is not a problem. After consulting the reference guide I tried setting the logDevice parameter on the JDBC url (jdbc:derby:target/unittest-db;create=true;logDevice=/mylogs) but that seems to be for a different log, hence derby.log still appears.

Any help is much appreciated.

Am I crazy? Switching an established product from HSQLDB to Apache Derby

I have an established software product that uses HSQLDB as its internal settings database. Customer projects are stored in this database. Over the years, HSQLDB has served us reasonably well, but it has some stability/corruption issues that we've had to code circles around, and even then, we can't seem to protect ourselves from them completely.

I'm considering changing internal databases. Doing this would be fairly painful from a development perspective, but corrupted databases (and lost data) are not fun to explain to customers.

So my question is: Does anyone have enough experience to weigh in on the long-term stability of Apache Derby? I found a post via Google complaining that Derby was unstable, but it was from 2006 so I'd entertain the idea that it has been improved in the last 4 years. Or, is there another pure Java embedded (in-process) database that I could use (commercial or open-source). Performance isn't very important to me. Stability is king. Data integrity across power loss, good BLOB support, and hot-backups are all a must.

Please don't suggest something that isn't a SQL-based relational database. I'm trying to retrofit an existing product, not start from scratch, thanks.

Which embedded database has maximum SQL compliance, and concurrency support?

My application at present uses Microsoft Access, but now may be hosted on Linux boxes. Additionally while being accessed from multiple computers, one of these may update the records (when its being read by other users).

I also require that the embedded database should support complex SQL queries - like inner SQL, Joins, etc.

I tried SQLite, but many of the existing queries fail, or need to be fixed (like in a simple query using inner join the brackets after FROM was not acceptable to SQLite, and had to be removed). Right join too is not supported.

I came to know about Apache Derby and H2, but would first prefer your valuable opinion.

Edit:

I forgot to mention that my application is entirely written in Java.

Edit:

At preset I use Microsoft Access mdb, shared on network drive, making DSN-less connection from remote computers.

Update

I did trials with Firebird, and it really does seem very good, zero admin, and SQL compliant. It is fast, and I had no problems in any of the typical queries I could try on it. I am very satisfied with it, and hopefully will use it for the project for which I raised this question.

Hopefully Advantage Server too will be good, but could not get time to review it. After reviewing/using Firebird do not feel any need to try anything else.

How to shutdown Derby in-memory database Properly

I'm using derby as an embedded database. Furthermore, I'm using it's in-memory database option for my unit tests.

What I can't figure out is how to properly shut down (A quick look at the code) the Derby database. I beleive I have it working for a standard database but I'm getting different exceptions when attempt similar code on a in-memory database.

I'm going to omit details, I'll add them if other feel are needed.

Basically, I'm trying to shut down my database in these two fashions where my in-memory database is consistently called "eh":

DriverManager.getConnection("jdbc:derby:memory:eh;shutdown=true");

then:

DriverManager.getConnection("jdbc:derby:eh;shutdown=true");

The former results in an exception but not the one expected. The details are:

java.sql.SQLNonTransientConnectionException: Database 'memory:eh' shutdown.

The latter results in

java.sql.SQLException: Database 'eh' not found.

Based on what I've been able to figure out, we want a SQLException but not the one we receive. On the other hand, the SQLNonTransientConnectionException error seems more appropriate but isn't the right type (though it is derived from SQLException) nor does it have the right state code. The state code end up being: 08006.

The example code I have illustrates that a SQLException with a SQL state of "XJ015".

Note: The example I'm referencing is: WwdEmbedded Program (Java Code).

Derby/JavaDB vs SQLiteJDBC [closed]

I found a lot of comparisions here, but not this one; So, what is best in each one?

Why are BOOLEAN type columns problematic in relational database design?

I've been working mostly with Oracle for the past few years, and am quite used to seeing single character varchar columns used as boolean values.

I can also see (per stack overflow answers), that suggested type for MySQL is TINYINT.

Now I've taken on my little side project - using DerbyDB, and it supports BOOLEAN columns, but not until after version 10 or so.

So, the question is, why is it so hard to incorporate a BOOLEAN column while designing a relational database? Am I missing something, or is it just pushed down the to-do list as unimportant, since you can use another column type meanwhile?

How to alter a column datatype for derby database?

I am trying to alter a datatype for a derby db column. The current price column is set as DECIMAL(5,0). I would like to alter it to DECIMAL(7,2). I did this :

alter table item alter column price set data type DECIMAL(7,2);

But it did not work, and showing the error:

Error: Only columns of type VARCHAR may have their length altered. 

May I know how is it possible to alter it? Thank you.

Setting up an embedded Derby database in a standalone Java application

I'm trying to setup an embedded Derby database for a standalone Java application, but after pouring through all sorts of documentation, I just can't seem to find any simple explanations or examples. I'm using Eclipse with the Derby plugin and have enabled Derby nature for my project.

I found an example of using an embedded Derby database in a standalone address book as well as an overview of using Derby in Eclipse (that doesn't seem to cover the embedded deployment), but I still feel like I'm missing something fundamental.

This is my first time attempting to use a database with Java, and I'm a little confused, so here are my basic questions:

• What's the basic philosophy (or model) for how Java interacts with a Derby database (in an embedded deployment)? Are their important design patterns to be followed?
• Do I need to create some type of database constructor (that includes table structure, etc.) in a class, or is that all done with some other tool?
• One the database is created and saved, how do I "start" it up? And where is the actual database saved?

Snippets of code would be very helpful!