How to know if a DBI connexion is read-only?

citromatik has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

Is it possible to know if a DBI connexion is read-only without failing on writing?

I have seen in the DBI documentation that there is a ReadOnly attribute, but at least for the mysql driver I am not able to make it work

Any idea?

citromatik

Comment on How to know if a DBI connexion is read-only?

Replies are listed 'Best First'.
Re: How to know if a DBI connexion is read-only? by Mr. Muskrat (Canon) on Oct 03, 2012 at 18:30 UTC
The correct answer will depend on what what you mean by a read-only connection. Setting the ReadOnly DBI attribute with a DBD::mysql database handle doesn't do anything useful; it's just advisory as explained in the section you linked. If you want to check if the database is read-only (as is common on a slave), the user only has select priviledges or if the transaction is read-only then you will need to write the appropriate queries to figure those out.	[reply]
Re: How to know if a DBI connexion is read-only? by mje (Curate) on Oct 03, 2012 at 14:27 UTC
"without failing on writing" I don't think there is a way that will work for all DBDs under DBI. e.g., ReadOnly in DBD::ODBC is mapped to the ODBC connection attribute READ_ONLY and you set it to give the driver a hint you will only be reading but it does not stop you writing.	[reply]
Re: How to know if a DBI connexion is read-only? by DrHyde (Prior) on Oct 04, 2012 at 10:30 UTC
There's not really any such thing as a read-only DBI connection, only users who don't have permission to make changes. To divine whether the user you're logged in as has permission to make changes, you need to query the database's metadata tables. Unfortunately, what these are called, where you find them, and what they contain will differ from one RDBMS to another. If, as is likely, you're using MySQL, then see this.	[reply]