Access form ODBC error using "GET DIAGNOSTICS"
It's well known that Accesss forms don't return the exact ODBC error message. If you perform an insert without setting a NO NULL field to a value it just returns "ODBC call failed" rather than e.g. "'Field ''table1col3Nonnull'' doesn''t have a default value'".
You can use DAO or ADO and return the precise error after performing a query in DAO or ADO but when the query is called by an Access form the only error returned is "ODBC call failed".
I tried using this function:
CREATE FUNCTION `new_function` ()
RETURNS VARCHAR(5)
BEGIN
GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT, @p3 = MYSQL_ERRNO;
RETURN @p1;
END
The function works fine when called from MySQL workbench but returns null when called from VBA e.g.:
Set rs = db.OpenRecordset("SELECT mydb.new_function();", dbOpenSnapshot, dbSQLPassThrough)
Debug.Print rs.Fields(0)
This returns null (and subsequent calls to the function from within MySQL workbench does return the error code meaning that the diagnostics area is still populated with the error.
When the function is changed to:
CREATE FUNCTION `new_function` ()
RETURNS VARCHAR(5)
BEGIN
GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT, @p3 = MYSQL_ERRNO;
RETURN "Testing";
END
Set rs = db.OpenRecordset("SELECT mydb.new_function();", dbOpenSnapshot, dbSQLPassThrough)
Debug.Print rs.Fields(0)
Returns "Testing" as expected.
I've also tried "SHOW ERRORS;" but this returns null as well.
Does anyone know if ODBC/VBA can return variables ok? Or is it just that "GET DIAGNOSTICS" doesn't work when called from ODBC/VBA?
Or does anyone know of another method to return the precise error?
Subject
Views
Written By
Posted
Access form ODBC error using "GET DIAGNOSTICS"
2517
December 16, 2015 05:52AM
1453
December 16, 2015 07:15AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.