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?

Thanks,

Linden

 

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
end-exec

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

goback.

Hope this helps,

Chris

---

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?

Thanks,

Linden

 

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

---