Hot answers tagged

8

You can get most of those messages, but unfortunately not all. See my question on Stackoverflow regarding that. In general those messages (e.g. messages from a PRINT statement) are returned as warnings on the Statement object by the JDBC driver. To retrieve them use Statement.getWarnings() in a loop: Statement stmt = ...; stmt.execute("some_procedure"); ...


7

I have found my answer from dbeaver support forum. http://dbeaver.jkiss.org/forum/viewtopic.php?f=3&t=624&p=4261#p4261 First enable TCP/IP for your SQL instance in the SQL Server Configuration Manager tool. Open SSCM and navigate to SQL Server Network Configuration > Protocols for SQLEXPRESS > TCP/IP Enable the TCP/IP protocol in the Properties ...


6

I'm getting a bit nervous. First, the upside: I've used the MariaDB JDBC driver in an "always on" service in production with a database connection pool and it is running fine for a couple of months now. The service only uses basic JDBC functions with simple queries (e.g. no joins, no blobs). I'm about to release another "always on" service that uses the ...


6

From the Connection.setAutoCommit docs: NOTE: If this method is called during a transaction and the auto-commit mode is changed, the transaction is committed. If setAutoCommit is called and the auto-commit mode is not changed, the call is a no-op. But I don't think it's very readable/obvious in your code. You should probably simply commit before ...


6

You could schema qualify the operators when you invoke them. For example, instead of writing SELECT current <@> destination You can write: SELECT current operator(public.<@>) destination See Operator Invocations for more information


6

Just call pg_database_size(dbname) to know the size of the database. VACUUM (without the FULL clause) does not free any space, it only marks it as reusable, and thus will not change the database's size (except in a rare boundary case, see Routine Vacuuming). ANALYZE does statistical sampling and would be useful if you needed the row counts, but for the ...


6

Shamelessly stolen from Craig's answer over SO. You need to create a cast to make the varchar -> macaddr coercion work automatically. It is slightly tricky, as there is no function that does exactly this, so we have to wrap an internal function into something we can use: CREATE TABLE mac (addr macaddr); INSERT INTO mac VALUES ('11:11:11:11:11:11'::...


6

datetime in Adaptive Server Enterprise (and SQL Server, since they share a common-code-base that included the datetime type) is stored using 8 bytes. 4 bytes for the date, and 4 bytes for the time. You can see this by looking at the binary version of a datetime: SELECT CONVERT(varbinary(8), CONVERT(datetime, '1753-01-01T00:00:00.000'), 0) SELECT CONVERT(...


5

You are attempting to pass in a blank password. Odds are the sa account has a password. You should create a separate account for the application to use instead of using the sa account. Using the sa account is a major security issue.


5

The usual solution is to authenticate the user within the web app, then issue a SET ROLE or SET SESSION AUTHORIZATION to "become" the user on a JDBC session that's already authenticated with the database using a fixed username. In both cases the DISCARD ALL command that should be run by any connection pool when returning connections to the pool will ...


5

Yes, you are likely seeing implicit conversions due to the guesses SQL Server has to make. It is creating a plan that will work not just for the parameter value you're passing now, but other potential values, too. With strings it (or perhaps JDBC? not sure) chooses an arbitrary default of 4000 and Unicode. I believe there are ways in JDBC to not send Unicode ...


5

The idea behind the large object API is to mimic a file-like API. The OID is like the path of the file, and the file descriptor obtained by lo_open or lo_creat is the equivalent of the POSIX open() and creat() system calls for files. JDBC provides LargeObject.truncate() and the both libpq (in C) and the server have built-in lo_truncate() functions. So yes ...


5

From Oracle documentation- When you create an Oracle Database, the user SYSTEM is also automatically created and granted the DBA role. SQL> conn system Password: SQL> select * from session_roles; ROLE ------------------------------ CONNECT RESOURCE DBA SQL> show user; USER is "SYSTEM" SQL> grant execute on dbms_lock to jay; grant execute ...


4

Looks like we found the culprit. Following the recent app server upgrade, we inadvertently included both ojdbc14 and ojdbc6 jars into our deployment, and evidently, the jvm picked up ojdbc14 for its Oracle DB driver. Since we removed ojdbc14 manually, this problem hasn't come up again in the past 24 hours. I assume ojdbc14 is no longer officially supported, ...


4

Use a subquery (as displayed) or CTE for that purpose: SELECT * FROM ( SELECT qid, gid FROM table1 ORDER BY date DESC LIMIT 10 OFFSET ? ) q JOIN table2 a USING (qid, gid) USING (qid, gid) is just a shortcut for ON q.qid = a.qid AND q.gid = a.gid with the side effect that the two columns are only included once in the result.


4

The answer below was taken from Sybase official technical documentation available at http://www.sybase.com/detail?id=1048699 (SAP has removed support for many links at sybase.com; however The Way Back Machine seems to have a copy of the page even if it is a bit old). Sybase Servers support the use of date and time data through the datetime and ...


4

Based on a simple test case I just wrote: @Test public void test() throws SQLException { PreparedStatement ps = conn.prepareStatement("SET ROLE ?"); ps.setString(1, "someuser"); ps.executeUpdate(); } I think the error you refer to is probably: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1" Position: 10 at org....


4

The difference of oracle instant client and the oracle client installed by Oracle Universal Installer is in how they are installed. The Oracle universal installer maintains a registry which the instant client does not have or use. But the software components are the same independent of the method you use to install. If you use the jdbc oci driver then you ...


4

The common solution is to express this as: interval '1 day' * ? with ? as a placeholder for a numeric value (possibly with a fractional part). The syntax tried in the question is rejected because for the SQL grammar, the entire expression interval '1 day' is a constant. It cannot be changed by injecting a placeholder into it, just like we couldn't write 3....


4

You'll need to explicitly convert your datetime strings to appropriate data types. Since you chose not to include the SQL statement in question, I can only guess what it's like, but if it looks anything like ...WHERE timestamp_column > ?... you'll need to replace it with ...WHERE timestamp_column > TO_TIMESTAMP(?, 'YYYY-MM-DD HH24:MI:SS')... using,...


3

Consider batching your commits. A batch size of 1024 is a good starting size. Change batch sizes until you reach your optimum throughput.


3

For Oracle, this seems like a good sneaky way of catching COMMITs: https://stackoverflow.com/a/6463800/790702 What he doesn't mention is that you should be able to catch the constraint violation in your code too, to stop the 2nd situation occurring.


3

No, it's still only 1GB of total memory. The buffer is allocated as shared memory and it only allocated once, but shared between all server processes. It's basically an "error" in the memory display of the top command, which simply reports the shared memory (that only exists once) for each process.


3

Deadlocks in Oracle with logically disjoint transactions usually involve unindexed foreign keys: There are two issues associated with unindexed foreign keys. The first is the fact that a table lock will result if you update the parent records primary key (very very unusual) or if you delete the parent record and the child's foreign key is not indexed. ...


3

If the session is active and and on an idle event it's probably on the CPU and not waiting. You can run a query like the following to see for sure select nvl(s.username,s.program) username, s.sid sid, s.serial# serial, s.sql_hash_value sql_hash_value, substr(decode(w.wait_time, 0, w.event, 'ON CPU'),1,15) event , ...


3

Already answered at a parallel thread on serverfault: https://serverfault.com/questions/345253/oracle-11-updating-blob-field-db-file-sequential-read-inappropriately-slow/345588#345588 In Oracle, LOB (including BLOB) is stored as: in-the-table LOB - if the LOB is smaller than 3900 bytes it can be stored inside the table row; by default this is ...


3

Your trigger is fired for each statement not for each row. In statement level triggers you can not access the new and old records. You need to change your create trigger to create a row-level trigger, rather than a statement level trigger: CREATE TRIGGER my_trigger AFTER INSERT ON table2 FOR EACH ROW -- this is the change EXECUTE PROCEDURE my_procedure(...


3

What creates your sql script file? Is the sql script file creation modifiable? If you can modify the sql script file creation, the easiest way is to append your own separator characters to your script between the statements. (Comments should be a good idea) If not, if you're just receiving a bunch of sql or pl/sql statements in a unmodifiable-source plain ...


3

You should always ensure that the JDBC driver is at least as new as the back-end server. PostgreSQL's catalogs change between major versions, and other details like the bytea quoting method, string escaping features, etc have also changed over time. PgJDBC contains compatibility code to cope with this by querying the server version. If you don't use a ...


3

I figured it out. Instead of having the environment variables set for the user, they had to be asserted when starting the JVM for the application, just like you have to set the LD_LIBRARY_PATH and CLASSPATH for the JDBC Driver. If everything is set when starting up the JVM, and you have a sqlnet.ora file in the directory you've set TNS_ADMIN, the file will ...


Only top voted, non community-wiki answers of a minimum length are eligible