Highlighted
Absent Member.
Absent Member.
637 views

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

Jump to solution

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

 

Linden Rowland - IT Consultant
Owner/Developer, www.SchoolReportWriter.com
IT Consultant to Tindle Newspaper Group

0 Likes
1 Solution

Accepted Solutions
Highlighted
Absent Member.
Absent Member.

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

Jump to solution
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

View solution in original post

0 Likes
2 Replies
Highlighted
Absent Member.
Absent Member.

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

Jump to solution
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

View solution in original post

0 Likes
Highlighted
Absent Member.
Absent Member.

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

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

Linden Rowland - IT Consultant
Owner/Developer, www.SchoolReportWriter.com
IT Consultant to Tindle Newspaper Group

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.