你好
我想透過新的 SQL 查詢功能更新資料庫：
Names Default To Here( 1 );
obj = New SQL Query(
Connection("DB connection string"),
Custom SQL "
UPDATE table
SET Column_1 = 1
where Column_2 = '2'
")
);
Error_Msg = Log capture( obj << Run;);我收到以下錯誤訊息：
""
Error running SQL Query:
[Server] Invalid object name table
日誌捕獲僅獲取空的“”字串。 所以我不知道查詢是否成功，因為當沒有發生錯誤時它會傳回一個空字串。
類似的主題但具有開放資料庫： https://community.jmp.com/t5/Discussions/Catching-database-error-message/mp/6749#M6743
謝謝
簡潔版本：
E_Msg = Log capture(
EXECUTE SQL(
create database connection("DB_String");
,
"SQL string"
);
);您可能會使用 EXECUTE SQL 函數來獲得更好的運氣。我在 EXECUTE SQL 之上編寫了一個名為 LOG_EXECUTE_SQL 的函數，該函數會尋找錯誤，如果找到，則顯示一個文字框，其中包含錯誤以及導致該錯誤的 SQL。您也可以透過設定全域變數來顯示任何呼叫的 SQL。
//------------------------------------------------------------------------------
/*
Function Name: log_execute_sql
Description: Execute SQL and trap ODBC errors found in the log file. If any are
found, display a warning message to the user. If the global variable
::g_log_execute_sql_debug flag is set to 1, display the SQL before
executing it
Arguments:
_caller Calling program. This will be included in the error message
_dbc database connection created with create database connection call
_sql sql statement to run
_invisible If 1, set the invisible flag. If 0, don't set it.
_tbl_name Optional table name to give the returned results table
Subprograms: execute sql
clear log
get log
Returns: pointer to dataset
Comments:
This preference is optional but good to use:
pref(ODBC Hide Connection String(1)); // Don't show connection string in dataset
Example calls:
1. Retrieve data from a table into an invisible dataset
pname = "Main Program";
dbc = Create Database Connection(::db_dsn); // ::db_dsn contains your database connection string
// The two colons means this is a global variable btw
// Get values from PARAMETERS table
sql_statement =
"SELECT rp.parameter_name, rp.parameter_value
FROM myschema.my_parameters rp";
param_dataset = log_execute_sql(pname, dbc, sql_statement, 1, "My Parameters");
close database connection(dbc);
2. Execute an INSERT statement:
dbc = Create Database Connection(::db_dsn); // ::db_dsn contains your database connection string
sql_statement = evalinsert(
"INSERT INTO myschema.my_activity_log
(user_created, date_created, my_section, activity, parameter1, value1, parameter2, value2,
parameter3, value3, parameter4, value4, parameter5, value5, parameter6, value6, parameter7, value7,
parameter8, value8)
VALUES ('^one_current_user_id^', CURRENT_DATE, 'DASHBOARD', 'REFRESH', 'Product Name', '^product_name_txt^',
'Owner', '^owner_txt^', 'Search Event Text', '^search_event_txt^', 'Date From', '^date_from_txt^',
'Date To', '^date_to_txt^', 'Alert Status', '^alert_status_txt^', 'Event Types', '^concat_event_types_list^',
'Alert Types', '^concat_alert_types_list^')");
// Run this SQL. No table is retrieved as it's a simple insert
log_execute_sql(pname, dbc, sql_statement, 0, "");
// Close the database connection - end of initialization queries
Close Database Connection(dbc);
*/
log_execute_SQL = function({_caller, _dbc, _sql, _invisible, _tbl_name},
{Default Local},
if (isempty(_dbc),
_nw = new window("log_execute_sql: Empty Value", << modal,
textbox("Error in log_execute_sql: The database connection is empty."),
);
throw("Empty database connection string");
);
if (isempty(_sql) | _sql == "",
_nw = new window("log_execute_sql: Empty Value", << modal,
textbox("Error in log_execute_sql: The SQL statement is empty."),
);
throw("Empty SQL string");
);
// Check the debug flag
_debug = 0;
if (! is empty(global:g_log_execute_sql_debug),
_debug = global:g_log_execute_sql_debug;
);
if (_debug,
_nw = new window("log_execute_sql DEBUG", << modal,
lineup box(ncol(2),
text box("_caller: "), text box(_caller),
text box("_dbc: "), text box(char(_dbc)),
text box("_invisible: "), text box(char(_invisible)),
text box("_tbl_name: "), text box(_tbl_name),
),
panel box("SQL",
sql_teb = text edit box(_sql)
),
hlistbox(
_cb = check box("Turn off subsequent debug output"),
_tb = text box(" "),
_bb = button box("OK",
is_checked = _cb << get(1);
if (is_checked,
global:g_log_execute_sql_debug = 0;
);
),
),
sql_teb << set nlines(40) << set width(500);
);
);
// OK we're good to go now.
batch interactive(1); // New 5-17-2018 force all errors to go to the log
_log_contents = log capture(
if (_invisible,
_dt = execute sql(_dbc, _sql, invisible, _tbl_name);
,
_dt = execute sql(_dbc, _sql, _tbl_name);
);
);
batch interactive(0); // New 5-17-2018 errors will not be forced to go to the log
// Check the log window for ODBC errors
// _log_contents = get log();
_found_error = 0;
crlf = hex to char("0D0A");
if (contains(_log_contents, "[Oracle][ODBC]") | contains(uppercase(_log_contents), "ERROR"), // Looking for errors
// then
_found_error = 1;
_log_list = words(_log_contents, crlf);
_error_msg = _log_list[1];
);
if (_found_error,
// then
// print(_sql); // Write the SQL out to the log window
_nw = new window("log_execute_sql: Oracle ODBC Error", << modal,
textbox("An Oracle error occurred in " || _caller || ": "),
panel box("Please email to technical support:",
sql_teb = text edit box(evalinsert(
"Calling Program: ^_caller^
Error Message: ^_error_msg^
SQL:
^_sql^"))),
sql_teb << set nlines(30) << set width(500),
);
);
// Return the table pointer
_dt;
); // end log_execute_sql
你好@jara95
感謝你的回覆
我故意製作一個錯誤的 SQL 語句來強制顯示錯誤訊息。
我高速更新/擴展現有資料庫，並且需要驗證新條目是否確實存在。 如果發生錯誤，應該向使用者發出錯誤訊息警報（最好的情況是我從 SQL 得到的訊息）。 解決方法是建立第二個 SQL 語句來要求新條目，該條目將開啟一個新資料表進行驗證，然後再次關閉。
這將需要更多的時間並給伺服器帶來更多的“壓力”，並且無法給出問題所在。
我找到了尾部內容=取得日誌(- 5 ）；代碼存取日誌，但訊息仍然無法存取：
{""}<BR />Error running SQL Query:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '*table name*'. [SQLSTATE=42S02]我只從日誌檔案中獲取 {""} ，就像捕獲日誌一樣。即使透過讀出整個取得日誌();
