Examples with SybaseDialect org.hibernate.dialect.SybaseDialect used on opensource projects

Example 1 with SybaseDialect

use of org.hibernate.dialect.SybaseDialect in project hibernate-orm by hibernate.

the class ASTParserLoadingTest method testStr.

@Test
public void testStr() {
    Session session = openSession();
    Transaction txn = session.beginTransaction();
    Animal an = new Animal();
    an.setBodyWeight(123.45f);
    session.persist(an);
    String str = (String) session.createQuery("select str(an.bodyWeight) from Animal an where str(an.bodyWeight) like '%1%'").uniqueResult();
    if ((getDialect() instanceof DB2Dialect || getDialect() instanceof TeradataDialect) && !(getDialect() instanceof DerbyDialect)) {
        assertTrue(str.startsWith("1.234"));
    } else //noinspection deprecation
    if (getDialect() instanceof SybaseDialect || getDialect() instanceof Sybase11Dialect || getDialect() instanceof SybaseASE15Dialect || getDialect() instanceof SybaseAnywhereDialect || getDialect() instanceof SQLServerDialect) {
        // str(val) on sybase assumes a default of 10 characters with no decimal point or decimal values
        // str(val) on sybase result is right-justified
        assertEquals(str.length(), 10);
        assertTrue(str.endsWith("123"));
        str = (String) session.createQuery("select str(an.bodyWeight, 8, 3) from Animal an where str(an.bodyWeight, 8, 3) like '%1%'").uniqueResult();
        assertEquals(str.length(), 8);
        assertTrue(str.endsWith("123.450"));
    } else {
        assertTrue(str.startsWith("123.4"));
    }
    //noinspection deprecation
    if (!(getDialect() instanceof SybaseDialect) && !(getDialect() instanceof Sybase11Dialect) && !(getDialect() instanceof SybaseASE15Dialect) && !(getDialect() instanceof SybaseAnywhereDialect) && !(getDialect() instanceof SQLServerDialect || getDialect() instanceof TeradataDialect)) {
        // In TransactSQL (the variant spoken by Sybase and SQLServer), the str() function
        // is explicitly intended for numeric values only...
        String dateStr1 = (String) session.createQuery("select str(current_date) from Animal").uniqueResult();
        String dateStr2 = (String) session.createQuery("select str(year(current_date))||'-'||str(month(current_date))||'-'||str(day(current_date)) from Animal").uniqueResult();
        System.out.println(dateStr1 + '=' + dateStr2);
        if (!(getDialect() instanceof Oracle8iDialect)) {
            //Oracle renders the name of the month :(
            String[] dp1 = StringHelper.split("-", dateStr1);
            String[] dp2 = StringHelper.split("-", dateStr2);
            for (int i = 0; i < 3; i++) {
                if (dp1[i].startsWith("0")) {
                    dp1[i] = dp1[i].substring(1);
                }
                assertEquals(dp1[i], dp2[i]);
            }
        }
    }
    session.delete(an);
    txn.commit();
    session.close();
}

Example 2 with SybaseDialect

use of org.hibernate.dialect.SybaseDialect in project hibernate-orm by hibernate.

the class FooBarTest method testScrollableIterator.

@Test
public void testScrollableIterator() throws Exception {
    // skip if not one of these named dialects
    boolean match = getDialect() instanceof DB2Dialect || getDialect() instanceof SybaseDialect || getDialect() instanceof HSQLDialect || // 9i/10g too because of inheritence...
    getDialect() instanceof Oracle8iDialect;
    if (!match) {
        return;
    }
    Session s = openSession();
    Transaction txn = s.beginTransaction();
    s.save(new Foo());
    s.save(new Foo());
    s.save(new Foo());
    s.save(new Bar());
    Query query = s.createQuery("select f, f.integer from Foo f");
    assertTrue(query.getReturnTypes().length == 2);
    ScrollableResults iter = query.scroll();
    assertTrue(iter.next());
    assertTrue(iter.scroll(1));
    FooProxy f2 = (FooProxy) iter.get()[0];
    assertTrue(f2 != null);
    assertTrue(iter.scroll(-1));
    Object f1 = iter.get(0);
    iter.next();
    assertTrue(f1 != null && iter.get(0) == f2);
    iter.getInteger(1);
    assertTrue(!iter.scroll(100));
    assertTrue(iter.first());
    assertTrue(iter.scroll(3));
    Object f4 = iter.get(0);
    assertTrue(f4 != null);
    assertTrue(!iter.next());
    assertTrue(iter.first());
    assertTrue(iter.get(0) == f1);
    assertTrue(iter.last());
    assertTrue(iter.get(0) == f4);
    assertTrue(iter.previous());
    txn.commit();
    s.close();
    s = openSession();
    txn = s.beginTransaction();
    query = s.createQuery("select f, f.integer from Foo f");
    assertTrue(query.getReturnTypes().length == 2);
    iter = query.scroll();
    assertTrue(iter.next());
    assertTrue(iter.scroll(1));
    f2 = (FooProxy) iter.get()[0];
    assertTrue(f2 != null);
    assertTrue(f2.getString() != null && f2.getComponent().getImportantDates().length > 0);
    assertTrue(iter.scroll(-1));
    f1 = iter.get(0);
    iter.next();
    assertTrue(f1 != null && iter.get(0) == f2);
    iter.getInteger(1);
    assertTrue(!iter.scroll(100));
    assertTrue(iter.first());
    assertTrue(iter.scroll(3));
    f4 = iter.get(0);
    assertTrue(f4 != null);
    assertTrue(!iter.next());
    assertTrue(iter.first());
    assertTrue(iter.get(0) == f1);
    assertTrue(iter.last());
    assertTrue(iter.get(0) == f4);
    assertTrue(iter.previous());
    int i = 0;
    for (Object entity : s.createQuery("from Foo").list()) {
        i++;
        s.delete(entity);
    }
    assertEquals(4, i);
    s.flush();
    assertTrue(s.createQuery("from java.lang.Object").list().size() == 0);
    txn.commit();
    s.close();
}

Example 3 with SybaseDialect

use of org.hibernate.dialect.SybaseDialect in project hibernate-orm by hibernate.

the class SQLFunctionsTest method testSQLFunctions.

@Test
public void testSQLFunctions() throws Exception {
    Session s = openSession();
    Transaction t = s.beginTransaction();
    Simple simple = new Simple(Long.valueOf(10));
    simple.setName("Simple 1");
    s.save(simple);
    if (getDialect() instanceof DB2Dialect && !(getDialect() instanceof DerbyDialect)) {
        s.createQuery("from Simple s where repeat('foo', 3) = 'foofoofoo'").list();
        s.createQuery("from Simple s where repeat(s.name, 3) = 'foofoofoo'").list();
        s.createQuery("from Simple s where repeat( lower(s.name), 3 + (1-1) / 2) = 'foofoofoo'").list();
    }
    assertTrue(s.createQuery("from Simple s where upper( s.name ) ='SIMPLE 1'").list().size() == 1);
    if (!(getDialect() instanceof HSQLDialect)) {
        assertTrue(s.createQuery("from Simple s where not( upper( s.name ) ='yada' or 1=2 or 'foo'='bar' or not('foo'='foo') or 'foo' like 'bar' )").list().size() == 1);
    }
    if (!(getDialect() instanceof MySQLDialect) && !(getDialect() instanceof SybaseDialect) && !(getDialect() instanceof SQLServerDialect) && !(getDialect() instanceof MckoiDialect) && !(getDialect() instanceof InterbaseDialect) && !(getDialect() instanceof TimesTenDialect)) {
        //My SQL has a funny concatenation operator
        assertTrue(s.createQuery("from Simple s where lower( s.name || ' foo' ) ='simple 1 foo'").list().size() == 1);
    }
    if ((getDialect() instanceof SybaseDialect)) {
        assertTrue(s.createQuery("from Simple s where lower( s.name + ' foo' ) ='simple 1 foo'").list().size() == 1);
    }
    if ((getDialect() instanceof MckoiDialect) || (getDialect() instanceof TimesTenDialect)) {
        assertTrue(s.createQuery("from Simple s where lower( concat(s.name, ' foo') ) ='simple 1 foo'").list().size() == 1);
    }
    Simple other = new Simple(Long.valueOf(20));
    other.setName("Simple 2");
    other.setCount(12);
    simple.setOther(other);
    s.save(other);
    //s.find("from Simple s where s.name ## 'cat|rat|bag'");
    assertTrue(s.createQuery("from Simple s where upper( s.other.name ) ='SIMPLE 2'").list().size() == 1);
    assertTrue(s.createQuery("from Simple s where not ( upper( s.other.name ) ='SIMPLE 2' )").list().size() == 0);
    assertTrue(s.createQuery("select distinct s from Simple s where ( ( s.other.count + 3 ) = (15*2)/2 and s.count = 69) or ( ( s.other.count + 2 ) / 7 ) = 2").list().size() == 1);
    assertTrue(s.createQuery("select s from Simple s where ( ( s.other.count + 3 ) = (15*2)/2 and s.count = 69) or ( ( s.other.count + 2 ) / 7 ) = 2 order by s.other.count").list().size() == 1);
    Simple min = new Simple(Long.valueOf(30));
    min.setCount(-1);
    s.save(min);
    if (!(getDialect() instanceof MySQLDialect) && !(getDialect() instanceof HSQLDialect)) {
        //My SQL has no subqueries
        assertTrue(s.createQuery("from Simple s where s.count > ( select min(sim.count) from Simple sim )").list().size() == 2);
        t.commit();
        t = s.beginTransaction();
        assertTrue(s.createQuery("from Simple s where s = some( select sim from Simple sim where sim.count>=0 ) and s.count >= 0").list().size() == 2);
        assertTrue(s.createQuery("from Simple s where s = some( select sim from Simple sim where sim.other.count=s.other.count ) and s.other.count > 0").list().size() == 1);
    }
    Iterator iter = s.createQuery("select sum(s.count) from Simple s group by s.count having sum(s.count) > 10").iterate();
    assertTrue(iter.hasNext());
    assertEquals(Long.valueOf(12), iter.next());
    assertTrue(!iter.hasNext());
    if (!(getDialect() instanceof MySQLDialect)) {
        iter = s.createQuery("select s.count from Simple s group by s.count having s.count = 12").iterate();
        assertTrue(iter.hasNext());
    }
    s.createQuery("select s.id, s.count, count(t), max(t.date) from Simple s, Simple t where s.count = t.count group by s.id, s.count order by s.count").iterate();
    Query q = s.createQuery("from Simple s");
    q.setMaxResults(10);
    assertTrue(q.list().size() == 3);
    q = s.createQuery("from Simple s");
    q.setMaxResults(1);
    assertTrue(q.list().size() == 1);
    q = s.createQuery("from Simple s");
    assertTrue(q.list().size() == 3);
    q = s.createQuery("from Simple s where s.name = ?");
    q.setString(0, "Simple 1");
    assertTrue(q.list().size() == 1);
    q = s.createQuery("from Simple s where s.name = ? and upper(s.name) = ?");
    q.setString(1, "SIMPLE 1");
    q.setString(0, "Simple 1");
    q.setFirstResult(0);
    assertTrue(q.iterate().hasNext());
    q = s.createQuery("from Simple s where s.name = :foo and upper(s.name) = :bar or s.count=:count or s.count=:count + 1");
    q.setParameter("bar", "SIMPLE 1");
    q.setString("foo", "Simple 1");
    q.setInteger("count", 69);
    q.setFirstResult(0);
    assertTrue(q.iterate().hasNext());
    q = s.createQuery("select s.id from Simple s");
    q.setFirstResult(1);
    q.setMaxResults(2);
    iter = q.iterate();
    int i = 0;
    while (iter.hasNext()) {
        assertTrue(iter.next() instanceof Long);
        i++;
    }
    assertTrue(i == 2);
    q = s.createQuery("select all s, s.other from Simple s where s = :s");
    q.setParameter("s", simple);
    assertTrue(q.list().size() == 1);
    q = s.createQuery("from Simple s where s.name in (:name_list) and s.count > :count");
    HashSet set = new HashSet();
    set.add("Simple 1");
    set.add("foo");
    q.setParameterList("name_list", set);
    q.setParameter("count", Integer.valueOf(-1));
    assertTrue(q.list().size() == 1);
    ScrollableResults sr = s.createQuery("from Simple s").scroll();
    sr.next();
    sr.get(0);
    sr.close();
    s.delete(other);
    s.delete(simple);
    s.delete(min);
    t.commit();
    s.close();
}

Example 4 with SybaseDialect

use of org.hibernate.dialect.SybaseDialect in project hibernate-orm by hibernate.

the class SQLFunctionsTest method testBlobClob.

@Test
public void testBlobClob() throws Exception {
    // Sybase does not support ResultSet.getBlob(String)
    if (getDialect() instanceof SybaseDialect || getDialect() instanceof Sybase11Dialect || getDialect() instanceof SybaseASE15Dialect || getDialect() instanceof SybaseAnywhereDialect || getDialect() instanceof TeradataDialect) {
        return;
    }
    Session s = openSession();
    s.beginTransaction();
    Blobber b = new Blobber();
    b.setBlob(s.getLobHelper().createBlob("foo/bar/baz".getBytes()));
    b.setClob(s.getLobHelper().createClob("foo/bar/baz"));
    s.save(b);
    //s.refresh(b);
    //assertTrue( b.getClob() instanceof ClobImpl );
    s.flush();
    s.refresh(b);
    //b.getBlob().setBytes( 2, "abc".getBytes() );
    b.getClob().getSubString(2, 3);
    //b.getClob().setString(2, "abc");
    s.flush();
    s.getTransaction().commit();
    s.close();
    s = openSession();
    s.beginTransaction();
    b = (Blobber) s.load(Blobber.class, new Integer(b.getId()));
    Blobber b2 = new Blobber();
    s.save(b2);
    b2.setBlob(b.getBlob());
    b.setBlob(null);
    //assertTrue( b.getClob().getSubString(1, 3).equals("fab") );
    b.getClob().getSubString(1, 6);
    //b.getClob().setString(1, "qwerty");
    s.flush();
    s.getTransaction().commit();
    s.close();
    s = openSession();
    s.beginTransaction();
    b = (Blobber) s.load(Blobber.class, new Integer(b.getId()));
    b.setClob(s.getLobHelper().createClob("xcvfxvc xcvbx cvbx cvbx cvbxcvbxcvbxcvb"));
    s.flush();
    s.getTransaction().commit();
    s.close();
    s = openSession();
    s.beginTransaction();
    b = (Blobber) s.load(Blobber.class, new Integer(b.getId()));
    assertTrue(b.getClob().getSubString(1, 7).equals("xcvfxvc"));
    //b.getClob().setString(5, "1234567890");
    s.flush();
    s.getTransaction().commit();
    s.close();
}

Example 5 with SybaseDialect

use of org.hibernate.dialect.SybaseDialect in project hibernate-orm by hibernate.

the class FooBarTest method testCollectionsInSelect.

@Test
public void testCollectionsInSelect() throws Exception {
    Session s = openSession();
    Transaction t = s.beginTransaction();
    Foo[] foos = new Foo[] { null, new Foo() };
    s.save(foos[1]);
    Baz baz = new Baz();
    baz.setDefaults();
    baz.setFooArray(foos);
    s.save(baz);
    Baz baz2 = new Baz();
    baz2.setDefaults();
    s.save(baz2);
    Bar bar = new Bar();
    bar.setBaz(baz);
    s.save(bar);
    List list = s.createQuery("select new Result(foo.string, foo.long, foo.integer) from Foo foo").list();
    assertTrue(list.size() == 2 && (list.get(0) instanceof Result) && (list.get(1) instanceof Result));
    /*list = s.find("select new Result( baz.name, foo.long, count(elements(baz.fooArray)) ) from Baz baz join baz.fooArray foo group by baz.name, foo.long");
		assertTrue( list.size()==1 && ( list.get(0) instanceof Result ) );
		Result r = ((Result) list.get(0) );
		assertEquals( r.getName(), baz.getName() );
		assertEquals( r.getCount(), 1 );
		assertEquals( r.getAmount(), foos[1].getLong().longValue() );*/
    list = s.createQuery("select new Result( baz.name, max(foo.long), count(foo) ) from Baz baz join baz.fooArray foo group by baz.name").list();
    assertTrue(list.size() == 1 && (list.get(0) instanceof Result));
    Result r = ((Result) list.get(0));
    assertEquals(r.getName(), baz.getName());
    assertEquals(r.getCount(), 1);
    assertTrue(r.getAmount() > 696969696969696000l);
    //The following test is disabled for databases with no subselects...also for Interbase (not sure why).
    if (!(getDialect() instanceof MySQLDialect) && !(getDialect() instanceof HSQLDialect) && /*&& !(dialect instanceof MckoiDialect)*/
    !(getDialect() instanceof SAPDBDialect) && !(getDialect() instanceof PointbaseDialect)) {
        s.createQuery("select count(*) from Baz as baz where 1 in indices(baz.fooArray)").list();
        s.createQuery("select count(*) from Bar as bar where 'abc' in elements(bar.baz.fooArray)").list();
        s.createQuery("select count(*) from Bar as bar where 1 in indices(bar.baz.fooArray)").list();
        if (!(getDialect() instanceof DB2Dialect) && !(getDialect() instanceof Oracle8iDialect) && !(getDialect() instanceof SybaseDialect) && !(getDialect() instanceof Sybase11Dialect) && !(getDialect() instanceof SybaseASE15Dialect) && !(getDialect() instanceof PostgreSQLDialect) && !(getDialect() instanceof PostgreSQL81Dialect) && !(getDialect() instanceof AbstractHANADialect)) {
            // SybaseAnywhereDialect supports implicit conversions from strings to ints
            s.createQuery("select count(*) from Bar as bar, bar.component.glarch.proxyArray as g where g.id in indices(bar.baz.fooArray)").list();
            s.createQuery("select max( elements(bar.baz.fooArray) ) from Bar as bar, bar.component.glarch.proxyArray as g where g.id in indices(bar.baz.fooArray)").list();
        }
        s.createQuery("select count(*) from Bar as bar where '1' in (from bar.component.glarch.proxyArray g where g.name='foo')").list();
        s.createQuery("select count(*) from Bar as bar where '1' in (from bar.component.glarch.proxyArray g where g.name='foo')").list();
        s.createQuery("select count(*) from Bar as bar left outer join bar.component.glarch.proxyArray as pg where '1' in (from bar.component.glarch.proxyArray)").list();
    }
    list = s.createQuery("from Baz baz left join baz.fooToGlarch join fetch baz.fooArray foo left join fetch foo.foo").list();
    assertTrue(list.size() == 1 && ((Object[]) list.get(0)).length == 2);
    s.createQuery("select baz.name from Bar bar inner join bar.baz baz inner join baz.fooSet foo where baz.name = bar.string").list();
    s.createQuery("SELECT baz.name FROM Bar AS bar INNER JOIN bar.baz AS baz INNER JOIN baz.fooSet AS foo WHERE baz.name = bar.string").list();
    if (!(getDialect() instanceof HSQLDialect))
        s.createQuery("select baz.name from Bar bar join bar.baz baz left outer join baz.fooSet foo where baz.name = bar.string").list();
    s.createQuery("select baz.name from Bar bar join bar.baz baz join baz.fooSet foo where baz.name = bar.string").list();
    s.createQuery("SELECT baz.name FROM Bar AS bar JOIN bar.baz AS baz JOIN baz.fooSet AS foo WHERE baz.name = bar.string").list();
    if (!(getDialect() instanceof HSQLDialect)) {
        s.createQuery("select baz.name from Bar bar left join bar.baz baz left join baz.fooSet foo where baz.name = bar.string").list();
        s.createQuery("select foo.string from Bar bar left join bar.baz.fooSet foo where bar.string = foo.string").list();
    }
    s.createQuery("select baz.name from Bar bar left join bar.baz baz left join baz.fooArray foo where baz.name = bar.string").list();
    s.createQuery("select foo.string from Bar bar left join bar.baz.fooArray foo where bar.string = foo.string").list();
    s.createQuery("select bar.string, foo.string from Bar bar inner join bar.baz as baz inner join baz.fooSet as foo where baz.name = 'name'").list();
    s.createQuery("select foo from Bar bar inner join bar.baz as baz inner join baz.fooSet as foo").list();
    s.createQuery("select foo from Bar bar inner join bar.baz.fooSet as foo").list();
    s.createQuery("select bar.string, foo.string from Bar bar join bar.baz as baz join baz.fooSet as foo where baz.name = 'name'").list();
    s.createQuery("select foo from Bar bar join bar.baz as baz join baz.fooSet as foo").list();
    s.createQuery("select foo from Bar bar join bar.baz.fooSet as foo").list();
    assertTrue(s.createQuery("from Bar bar join bar.baz.fooArray foo").list().size() == 1);
    assertTrue(s.createQuery("from Bar bar join bar.baz.fooSet foo").list().size() == 0);
    assertTrue(s.createQuery("from Bar bar join bar.baz.fooArray foo").list().size() == 1);
    s.delete(bar);
    if (getDialect() instanceof DB2Dialect || getDialect() instanceof PostgreSQLDialect || getDialect() instanceof PostgreSQL81Dialect) {
        s.createQuery("select one from One one join one.manies many group by one order by count(many)").iterate();
        s.createQuery("select one from One one join one.manies many group by one having count(many) < 5").iterate();
    }
    s.createQuery("from One one join one.manies many where one.id = 1 and many.id = 1").list();
    s.createQuery("select one.id, elements(one.manies) from One one").iterate();
    s.createQuery("select max( elements(one.manies) ) from One one").iterate();
    s.createQuery("select one, elements(one.manies) from One one").list();
    Iterator iter = s.createQuery("select elements(baz.fooArray) from Baz baz where baz.id=?").setParameter(0, baz.getCode(), StandardBasicTypes.STRING).iterate();
    assertTrue(iter.next() == foos[1] && !iter.hasNext());
    list = s.createQuery("select elements(baz.fooArray) from Baz baz where baz.id=?").setParameter(0, baz.getCode(), StandardBasicTypes.STRING).list();
    assertEquals(1, list.size());
    iter = s.createQuery("select indices(baz.fooArray) from Baz baz where baz.id=?").setParameter(0, baz.getCode(), StandardBasicTypes.STRING).iterate();
    assertTrue(iter.next().equals(new Integer(1)) && !iter.hasNext());
    iter = s.createQuery("select size(baz.stringSet) from Baz baz where baz.id=?").setParameter(0, baz.getCode(), StandardBasicTypes.STRING).iterate();
    assertEquals(new Integer(3), iter.next());
    s.createQuery("from Foo foo where foo.component.glarch.id is not null").list();
    iter = s.createQuery("select baz, size(baz.stringSet), count( distinct elements(baz.stringSet) ), max( elements(baz.stringSet) ) from Baz baz group by baz").iterate();
    while (iter.hasNext()) {
        Object[] arr = (Object[]) iter.next();
        log.info(arr[0] + " " + arr[1] + " " + arr[2] + " " + arr[3]);
    }
    s.delete(baz);
    s.delete(baz2);
    s.delete(foos[1]);
    t.commit();
    s.close();
}