SQLAnywhere gives odbc driver error

[swi2012]

Hello. I've tried example from guide to make simple query for SAP sqlanywhere db with odbc source. but get error message from odbc driver

Hello, world!
row set1
Error: Diagnostics(State: HY024, Native error: 0, Message: [SAP][ODBC Driver]Invalid attribute value)

odbc.ini:

[sybase]
Driver=/opt/sqlanywhere17/lib64/libdbodbc17_r.so
DatabaseName=demo
ServerName=local
CommLinks=tcpip(host=localhost;port=2638)
CharSet=UTF-8

code example:

use odbc_api::{buffers::TextRowSet, Cursor, Environment};
use std::error::Error;
const BATCH_SIZE: u32 = 100_000;

fn main() -> Result<(), Box<dyn Error>> {
    println!("Hello, world!");

    let environment = unsafe { Environment::new() }?;

    let conn = environment.connect("sybase", "***", "***")?;

    match conn.execute("select current date", ())? {
        Some(cursor) => {
            let mut buffers = TextRowSet::for_cursor(BATCH_SIZE, &cursor, Some(4096))?;
            println!("row set1");
            let mut row_set_cursor = cursor.bind_buffer(&mut buffers)?;
            println!("row set2");

            while let Some(batch) = row_set_cursor.fetch()? {
                for row_index in 0..batch.num_rows() {
                    let rec = (0..batch.num_cols())
                        .map(|col_index| batch.at(col_index, row_index).unwrap_or(&[]));
                    println!("{:#?}", rec);
                }
            }
        }
        None => {
            eprintln!("Query is empty.");
        }
    }
    Ok(())
}

crate odbc is working with this odbc driver, but it's seems to be abandoned.

[pacman82]

Hello @swi2012 ,

thanks for filing a bug report and taking a time to make a reproducing example. Sadly a crucial component is still missing for me to learn what exactly goes wrong, namely I do not have access to an SAP system to test against. So your help going further would be greatly appreciated.

Thanks to your example and the error message I can tell you two things so far.

• Invalid attribute value roughly means I requested a functionality from the ODBC driver, it does not know about. Sadly I do not know much more than that, due to the lacking context of which ODBC function with what arguments caused this exception. It would be a huge help to me if you could step into bind_buffer with a debugger and tell me about the call stack, then the error occurrs.
• Your assumption that the odbc crate is abandoned is correct. I abandoned odbc and odbc-safe because my initial assumption that I could build higher level abstractions on top of a safe abstractions of ODBC turned out to be wrong. I am aiming for a zero cost abstraction, but that implies that I can not abstract the low level stuff safely. I do not think that these crates can be salvaged without breaking the interface so much that it is in essence a new crate. This is why odbc-api exists.
• If it works with the odbc crate, it is likely due to the fact that the odbc crate does not bind a buffer to the statement before it is executed. I do not use this technique in the examples and discourage it in the documentation, as it usually is a horrible inefficient way to extract data (I've seen real world use cases being 500x times slower and they are not the exception). Yet you can do the same thing with this crate by calling Cursor::next_row directly on the cursor without binding a buffer first. You may then call either get_data, get_text or get_binary to retrieve data if the option contains a row.

That is all I can say for now without either improving diagnostics, or having access to a test system to reproduce the error. To reiterate: You helping me to figure out which ODBC function call within bind_buffer would help me a lot in figuring out the root cause of this.

Cheers, Markus

[swi2012]

Thanks for answer. I'm new to Rust so if you can explain to me what i need to do to give your more info on this issue i'm gladly give you more info. I.e. how can i view what function called (gdb?) within bind_buffer.

[pacman82]

Hello @swi2012 ,

thanks for helping out. Yeah, I think setting up a debugger is the right way to go. I know little about your background or platform, so I won't write a complete tutorial on how to setup a debugger for Rust. Yet I gladly give you some pointers. Personally I use vscode. You should install the rust-analyzer extension. For debugging I go with the C/C++ extension on windows, on Linux or OS-X I use the CodeLLDB extension.

With these extensions setup you should be able to debug the application and step into function calls. You may want to set a breakpoint here: https://github.com/pacman82/odbc-api/blob/8fbde14920c3a2e0f42aa3321c430f5155a06792/odbc-api/src/handles/error.rs#L48.

This is where ODBC return values are translated into errors. I am interessted at the callstack at the moment you example reaches this line.

Hope this helps you moving forward. Otherwise please get back to me.

Cheers, Markus

[swi2012]

I'm on linux. Emacs+Rustic+LSP (rust-analyzer). I've tried to debug in console with rust-gdb but can't figure out how to set breakpoint in odbc_api::handlers:error:48 (gdb mark this bp as pdending because of it located in shared lib). I've set dap-mode for emacs, run it first time and get to the bp you specified. I dont realy understand is this picture is right or something not working right in my debug environment but at least i see self and handle there.

[pacman82]

I use neither Emacs, Rustor or LSP. So I do not understand your setup. Yet the screenshot looks like a debug session and the little green dot looks suspiciously like a breakpoint. So you did a lot of things right. Sadly I can not see the callstack anywhere in the screenshot. You can try this:

• I think callstack is called backtrace in GDB. You can type bt in gdb console session.
• You can try to step manually through the code and keep track of the function names.
• Hack: You could edit your local copy of odbc-api codebase to panic! at that point and set RUST_BACKTRACE=1.

[swi2012]

I've tried to use your hint to get more info from gdb.

Thread 1 "oapitest" hit Breakpoint 1, odbc_api::handles::error::{impl#0}::into_result (self=..., handle=...) at /home/morsov/.cargo/registry/src/github.com-1ecc6299db9ec823/odbc-api-0.24.1/src/handles/error.rs:48
warning: Source file is more recent than executable.
48                  let mut rec = DiagnosticRecord::default();
(gdb) bt
#0  odbc_api::handles::error::{impl#0}::into_result (self=..., handle=...) at /home/morsov/.cargo/registry/src/github.com-1ecc6299db9ec823/odbc-api-0.24.1/src/handles/error.rs:48
#1  0x000055555556d9bb in odbc_api::handles::statement::{impl#3}::set_row_array_size (self=0x7fffffffd8a0, size=100000) at /home/morsov/.cargo/registry/src/github.com-1ecc6299db9ec823/odbc-api-0.24.1/src/handles/statement.rs:530
#2  0x00005555555620bb in odbc_api::cursor::{impl#6}::bind_buffer<odbc_api::handles::statement::StatementImpl, &mut odbc_api::buffers::text_row_set::TextRowSet> (self=..., row_set_buffer=0x7fffffffdfc0)
    at /home/morsov/.cargo/registry/src/github.com-1ecc6299db9ec823/odbc-api-0.24.1/src/cursor.rs:389
#3  0x00005555555665e6 in oapitest::main () at src/main.rs:16
(gdb) i frame
Stack level 0, frame at 0x7fffffffd820:
 rip = 0x55555557013f in odbc_api::handles::error::{impl#0}::into_result (/home/morsov/.cargo/registry/src/github.com-1ecc6299db9ec823/odbc-api-0.24.1/src/handles/error.rs:48); saved rip = 0x55555556d9bb
 called by frame at 0x7fffffffd860
 source language rust.
 Arglist at 0x7fffffffd698, args: self=..., handle=...
 Locals at 0x7fffffffd698, Previous frame's sp is 0x7fffffffd820
 Saved registers:
  rip at 0x7fffffffd818
(gdb) n
49                  if rec.fill_from(handle, 1) {
(gdb) bt
#0  odbc_api::handles::error::{impl#0}::into_result (self=..., handle=...) at /home/morsov/.cargo/registry/src/github.com-1ecc6299db9ec823/odbc-api-0.24.1/src/handles/error.rs:49
#1  0x000055555556d9bb in odbc_api::handles::statement::{impl#3}::set_row_array_size (self=0x7fffffffd8a0, size=100000) at /home/morsov/.cargo/registry/src/github.com-1ecc6299db9ec823/odbc-api-0.24.1/src/handles/statement.rs:530
#2  0x00005555555620bb in odbc_api::cursor::{impl#6}::bind_buffer<odbc_api::handles::statement::StatementImpl, &mut odbc_api::buffers::text_row_set::TextRowSet> (self=..., row_set_buffer=0x7fffffffdfc0)
    at /home/morsov/.cargo/registry/src/github.com-1ecc6299db9ec823/odbc-api-0.24.1/src/cursor.rs:389
#3  0x00005555555665e6 in oapitest::main () at src/main.rs:16
(gdb) i frame
Stack level 0, frame at 0x7fffffffd820:
 rip = 0x5555555701b6 in odbc_api::handles::error::{impl#0}::into_result (/home/morsov/.cargo/registry/src/github.com-1ecc6299db9ec823/odbc-api-0.24.1/src/handles/error.rs:49); saved rip = 0x55555556d9bb
 called by frame at 0x7fffffffd860
 source language rust.
 Arglist at 0x7fffffffd698, args: self=..., handle=...
 Locals at 0x7fffffffd698, Previous frame's sp is 0x7fffffffd820
 Saved registers:
  rip at 0x7fffffffd818
(gdb)

As i understand function that hit error was set_row_array_size, maybe i'm wrong.

[pacman82]

Hello @swi2012 ,

sorry for the delayed response, but I do not have internet these next few days. The information you provided is exactly what I needed thanks. I discovered that u32 instead usize had been used to transport the row_array_size. This has been fixed in version 0.25.0. Please tell me if this fixes your error. If it does not please also try it with BATCH_SIZE 1 and 5000. Tell me how it goes.

I'll write a more thourough response once I have a bit more time (and internet).

Cheers, Markus

[swi2012]

Thanks for the answer. On 0.25 version with BATCH_SIZE up to 65000 no error appear. At 66000 it throw Error: Diagnostics { record: State: HY024, Native error: 0, Message: [SAP][ODBC Driver]Invalid attribute value, function: "SQLSetStmtAttrW" }

[pacman82]

Hello @swi2012 ,

Thank you for conducting the experiments. From what you tell me I would guess the SAP ODBC driver uses a 16 Bit integer to store the batch size. This would make the upper bound 65536. It is fine for ODBC drivers to have an upper bound. Here is what the reference has to say in the matter:

If the specified rowset size exceeds the maximum rowset size supported by the data source, the driver substitutes that value and returns SQLSTATE 01S02 (Option value changed).

Found here: https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlsetstmtattr-function?view=sql-server-ver15

I read this as the driver should emit a warning and just emit smaller batches. You may want to file a bug with SAP. On a practical matter 65536 should be enough for all applications, and even too large for most. After all your App has to preallocate all that memory.

Cheer, Markus

[pacman82]

From the viewpoint of this crate, I will improve the error on invalid attributes then specifing array size. But I must wait until I have internet again.

Cheers Markus

[swi2012]

Thank for your answer. I think just constanting MAX buf value not bigger than 64k for my app will be fine enough. I think write to SAP about that would be useless cause they point out that they have native C/C++ C# drivers for that and have no support for rust at all. Well, their own sql server manager app on windows works through odbc :) Thanks a lot for you help. Now i can play with my DB with odbc-api crate futher.

[pacman82]

Hello @swi2012 ,

I added tests for binding large buffers with the three db systems I currently test with. Non of them reproduce the error though. In any case the crate is now emmiting a way more meaninful error in version 0.26.0. Feel free to tell me if you can actually observe it, and would find it helpful.

Cheers, Markus

[swi2012]

Error: InvalidRowArraySize { record: State: HY024, Native error: 0, Message: [SAP][ODBC Driver]Invalid attribute value, size: 66000 } Yep. Thanks a lot for your work!