NWNX ODBC Improvement?

[pdwalker]

Hi,

I've been having a fiddle with the NWNX ODBC interface and I've noticed one thing that I think could be used to improve how the inerface operates

With SELECT statements, you can use the SQLFetch function to determine if your select statement returned any rows.

Unfortunately, with UPDATEs, you do not get any feedback as to whether the updated succeeded (e.g. when updating a non existing record set). As a result a select has to be coded in, tested for an error and then the choice between an update or insert decided. This results in double the number of queries that have to be run in order update records.

Would it be possible to return an value to indicate the success or failure of a statement to avoid this extra coding?

e.g. (using SetPersistentString as an example)

[Papillon]

From the top of my head I'd say that UPDATE and INSERT do not return result values. I can see some issues about interpreting a zero result (as is the case in your update example) as an error, but the idea itself is good.
_________________
Papillon

[pdwalker]

Typically, the convention for inserts, deletes, updates and selects is to return the number of rows inserted, deleted, affected or selected.

This is the case with JDBC.

I've just peaked into the odbc api's and...ack! The api seems a little clunky to me. It looks like you would have to fiddle with the SQLGetDiagRec function to get more useful information (maybe, not sure - didnt read further)

The SQLExecDirect function does at least return an error if the statement fails, so in the case of updates, you can use that error as a guide that you have to follow it up with an insert.

I see from the docs that:

[Lokey]

I don't really follow why you set up your SQL statements that way though.

It'll make the SQL statement more messy, but won't require multiple connects if you just did:

INSERT (statement) ON DUPLICATE KEY UPDATE (statement);
_________________
Neversummer PW NWNx powered mayhem

[Vladiat0r]

[pdwalker]

[Gryphyn]

There is a MySQL API call that returns just this data.

MySQL Reference Manual :: 24.2.7.1 mysql_stmt_affected_rows()

mysql_stmt_affected_rows() my_ulonglong mysql_stmt_affected_rows(MYSQL_STMT *stmt) Description Returns ... be called immediately after mysql_stmt_execute() for UPDATE , DELETE , or INSERT statements. For SELECT statements, mysql_stmt_affected_rows() works like mysql_num_rows() . This function was added in MySQL 4.1.0. Return Values An integer greater than zero

This could possibly be added as an extra 'NWNX_Function'.
The result could even be used to control 'loops', as you know how many records were/are affected.

Cheers
Gryphyn

[Acrodania]

The important line being right here.....