SQLMode-Selection not working!?

[lbreddemann]

Hi all,

I recently came across an oddity in SQL Studio (7.6.03 Build 10):

When choosing 'ORACLE' as sqlmode SQL Studio GUI displays that it has changed the sqlmode.

But checking in the SESSIONS table shows always "INTERNAL" as sqlmode.

The same happens when using sqlcli:

sqlcli sdb=> \m ORACLE SQL mode changed to ORACLE sqlcli sdb=> select sqlmode from sessions where own='YES' | SQLMODE | | -

---

| | INTERNAL | 1 row selected (154.150 msec) sqlcli sdb=> sqlcli sdb=> \m ORACLE SQL mode changed to ORACLE sqlcli sdb=> select sqlmode from sessions where own='YES' | SQLMODE | | -

---

| | INTERNAL | 1 row selected (23.453 msec)

Only when I start sqlcli directly with the sqlmode selection it works. Unfortunately a later change is still not possible.

C:\WINDOWS>sqlcli -d sdb -u lars,lars -S ORACLE Welcome to the MaxDB interactive terminal. Type: \h for help with commands \q to quit sqlcli sdb=> select sqlmode from sessions where own='YES' | SQLMODE | | -

---

| | ORACLE | 1 row selected (3592 usec) sqlcli sdb=> \m INTERNAL SQL mode changed to INTERNAL sqlcli sdb=> select sqlmode from sessions where own='YES' | SQLMODE | | -

---

| | ORACLE | 1 row selected (3882 usec)

This happens on 7.6.3 /7.6.4 databases.

Can somebody check this?

Thanks and regards,

Lars

[Former Member · Accepted solution]

Hello,

I see the same effect but the behaviour of the SQL editor is changed to Oracle mode.

You can check it with the SQL statements:

Works in Internal mode: select * from tables where rowno < 2

Works in Oracle mode: select * from tables where rownum < 2

The reason might be that the ODBC driver sets the SQL mode for each statement and not for the complete session.

With Database Studio the JDBC driver works as you expect.

SQLMODE SESSIONID OWN;

ORACLE 12.181 YES

Regards

Wolfgang

[lbreddemann · Accepted solution]

Hello Wolfgang,

thanks for the quick reply.

You're right - the command editor behaves correctly in either mode.

Actually I wasn't aware of the fact, that sqlcli uses ODBC and not sqldbc directly to connect to the database. I have a presentation here, where exactly this is indicated...

Well, anyhow, thanks again for the clarification.

Regards,

Lars

[Former Member · Accepted solution]

Hi,

SQL Studio works with ODBC and sqlcli with SQLDBC. For both programs the SQL mode is set per statement.

Regards

Wolfgang