Tag Info

Hot answers tagged

5

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 ...


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 ...


4

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 ...


3

When you use Kerberos authentication (for example Active Directory) with JDBC, you have to prepare your code for it, and not just simply change the connection string. The Database JDBC Developer's Guide and Reference covers this topic: http://docs.oracle.com/cd/E11882_01/java.112/e16548/clntsec.htm#JJDBC28344 Essential parts from the example of the above ...


3

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 ...


3

An ORM requires information about the first write (SCOPE_IDENTITY or such) to complete the second write. This means 2 (with an OUTPUT clause) or 3 database (with SELECT SCOPE_IDENTITY) calls in general in a client side transaction. No amount of tinkering with isolation levels will eliminate these 2 or 3 calls. If you want more performance, then the best ...


3

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 ...


2

Yes it does support different transaction isolation levels per-connection. You can set the transaction isolation level (as well as the read-only and deferrable status for transactions) for a connection with SET SESSION CHARACTERISTICS: localhost:5432 postgres postgres # SHOW transaction_isolation; transaction_isolation ----------------------- read ...


2

Transaction isolation level is set up per transaction basis. Even one connection can have different isolation level on each transaction. Of course different connection can have different isolation level. Here's some testing : postgres=# begin; BEGIN postgres=# show transaction_isolation ; transaction_isolation ----------------------- read committed (1 ...


2

The only isolation level that influences writes is SNAPSHOT (and the READ_COMMITTED_SNAPSHOT). Snapshot isolation requires row versioning and row versioning requires extra writes. Read Understanding Row Versioning-Based Isolation Levels. Now about the 'super-fast' part of the question: the 'super-fast' option for INSERT is the bulk insert path. This ...


2

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 ...


2

You could create a function that executes SET ROLE with dynamic SQL, using format to safely insert the role identifier (%I inserts an identifier, placing double quotes around it if necessary, and escaping double quotes in the string by doubling them up if necessary). Something along the lines of CREATE FUNCTION setrole(role text) RETURNS void AS $$ BEGIN ...


2

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 ...


2

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 ...


2

Pass the values as XML (in a VARCHAR). The following database all support shredding XML from the query language: Postgres MySQL SQL Server Oracle DB2 UDM Obviously, not every obscure database on the planet supports it. But the ones above are realistically the only ones you need to care about to hit 99.9% of the database market.


2

It is unlikely that a named instance is running on port 1433, unless you explicitly set it as such. I also suspect that SSMS is using shared memory (if local) or named pipes (if remote), unlike JDBC which is using TCP/IP (and I don't know how to set JDBC to use a different protocol). From this documentation, it seems your connection string should be: ...


2

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


2

You can escape single quotes by using 2 single quotes, that way your query would work. I'm not sure about the java syntax for a replace but your query should look something like this SELECT COUNT(DISTINCT ROOT_ID) as rows1 FROM DANIEL.UNIQUE_PHYSICIAN WHERE UNIQUE_PHYSICIAN.SOURCE_TABLE = 'STI.PHYSICIAN' AND UNIQUE_PHYSICIAN.FIRST_NAME = 'JEANNE-D''ARC' ...


2

The Instant Client is easier to install and more lightweight. It doesn't even need to be installed, you can just unzip it and use it. You don't need an Oracle Home either. On the other hand, it does not support deprecated features like Oracle Names. If you don't need any feature the instant client omits, I suggest you use it. It looks like you are covered ...


2

In WebLogic console, if you are selected XA Driver then it need additional drivers specific library files like sqljdbc.dll etc. Better to choose Oracle non XA driver(second item from the dropdown list) while you are configuring your datasource. HTH


1

That stored procedure isn't installed on SQL Server by default. Seems like JDBC needs to do some kind of initialization on that server first. Compare the target server with a server where the connection does work - I'll bet you find a difference in some JDBC item in Programs and Features, or hopefully this has already rung a bell about something that had to ...


1

You can use setNull(): preparedStatement.setNull(8, Types.VARCHAR); Although I have seen drivers that do not properly support that, in that case simply try: preparedStatement.setString(8, null);


1

I managed to produce a working trigger based on ypercube's help. create or replace TRIGGER ROOT_PHYSICIAN_TRG BEFORE INSERT ON UNIQUE_PHYSICIAN REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN IF INSERTING AND :NEW.is_root_phys = 1 THEN :NEW.root_id := :NEW.unique_id; END IF; END;


1

If you have a condition that specifies 1005 whether root_id has to get the same value as unique_id, you could use a TRIGGER. Something like this: CREATE OR REPLACE TRIGGER root_unique_trg BEFORE INSERT ON UNIQUE_PHYSICIAN FOR EACH ROW WHEN (NEW.is_root_phys = 1) BEGIN :NEW.root_id := :NEW.unique_id ; END ; Tested in SQLfiddle


1

It appears to be a bug in the way Squirrel handles additional parameters. Data Studio acts as expected with both db2jcc and db2jcc4 drivers. I have filed a bug to: https://sourceforge.net/p/squirrel-sql/bugs/1201/


1

Apparently, the JDBC driver needs to be loaded before the connection is made by Class.forName("com.orientechnologies.orient.jdbc.OrientJdbcDriver").


1

I think select 1 from dual; is a syntax error. Here's what happens on the wire: With select 1 from dual a connection's lifecycle is: Initial establishment First query (validation does not yet happen) Return to the pool Validation: the pool sends select 1 from dual The database answers: ORA-01403: no data found Next query and so on. The connection is ...


1

Percona wrote up a nice article MySQL Prepared Statements explaining pros and cons So there are good reasons to use prepared statements: Save on query parsing Save on data conversion and copying Avoid SQL Injection Save memory on handling blobs There are also drawbacks and chewats of using prepared statements: Query cache ...


1

Another possibility: Encode the values with Base64. Concatenate the values into a comma-separated string. Pass the resulting string. Split the string in the stored procedure. Decode the strings using Base64. Base64 encodes no comma in its default character set and either is or appears to be supported by: Oracle SQL Server Sysbase PostgreSQL MySQL ...


1

Oracle has mentioned related to this in the Tec documents JDBC-ODBC Bridge The JDBC-ODBC Bridge allows applications written in the Java programming language to use the JDBC API with many existing ODBC drivers. The Bridge is itself a driver based on JDBC technology ("JDBC driver") that is defined in the class sun.jdbc.odbc.JdbcOdbcDriver. The ...



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