MySQL select database() command pre-compiler error...

Hi there,

I need to get the MySQL db name I'm connected to.

MySQL command below gets pre-compiler error, "Incorrect SQL statement syntax near: database".

exec sql select database() into :aName from aTable end-exec.

'Select schema()...' gets same error, while 'select whatever()...' is fine.

Seems to be a reserved word problem?  How to solve?




  • Verified Answer

    Hi Linden,

    I suggest you contact customer support and request an RPI to get this fixed. We just need to make 'database' usable as a function name as well as a reserved word for some SQL dialects.

    In the meantime, you can use dynamic SQL to do what you want, as follows:

    $set sql(behavior=optimized)
    working-storage section.

    exec sql include sqlca end-exec.

    01 dbname pic x(32).

    01 dsqlText pic x(100) value "select database()".

    procedure division.

    exec sql
    connect to MySQL
    user myUserId.myPassword

    exec sql declare c cursor for dsql end-exec
    exec sql prepare dsql from :dsqlText end-exec
    exec sql open c end-exec
    exec sql fetch c into :dbname end-exec
    exec sql close c end-exec
    display "Default database is " dbname

    exec sql disconnect end-exec


    Hope this helps,

  • Thanks Chris,
    I have reported it but TBH I'm happy to use the cursor approach you've kindly provided here. Works fine.