U16String as InputParameter

Hello,

I currently try to communicate with an older Oracle database through ODBC.
ODBC drivers for Oracle can't be bothered to handle UTF-8 gracefully. And binding a parameter with UTF-8 encoded Unicode (eg. "Gruß" German for "Greetings") does not work. In the C++ ODBC Client I wrote once, I defaulted to using SQL_C_WCHAR to get unicode in and out of the database with those Oracle drivers.

I've been using into_parameter in my current approach. But it does not seem to be implemented for U16String.
I also could not find any example in the repository featuring this. There was some code in tests/integration.rs about into_column_inserter but I can't decipher how to apply that to passing parameters to prepared.execute.
Can I somehow work around and make odbc-api bind a U16String as WChar?

Hello @WeirdConstructor ,

thanks for taking the time to investigate the documentations and the examples. I am sorry that your search did not yield anything. This is indeed a missing feature. Technically you could work around this in your code, but there are actually just a few type aliases missing in odbc-api to support UTF-16 strings for good.

If no problems come up which would betray my expectations, I should be able to support this soonish. Keep in mind however I maintain this in my spare time, and do not know then next I'll get to this.

Also note that there are two encodings at play. Each parameter can choose its own character type (narrow vs wide) in ODBC. The application has to decide though, how to send the statement text globally though. For odbc-api this already defaults always to wide strings (UTF-16). A behavior which could be changed by compiling with the narrow feature. See comment in the Cargo.toml

Best, Markus

Hello @WeirdConstructor ,

happy to tell you, I have a working prototype already. A minimal form of the feature could be released soon.

I've also not been entirely correct about odbc-api not supporting wide character strings. The tests you found refer to bulk insertion, i.e then you want to install lot's of strings in a single roundtrip to the database for speed. This is typically used with INSERT statements for fast iteration.

What is your usecase? Do you

a) want to insert lots of strings into a table or
b) use a wchar as a parameter in e.g. a WHERE clause of a query or
c) insert/update a single row in the DB

?

Cheers, Markus

odbc-api 0.56.2 support U16Str as input parameters.

See:

odbc-api/odbc-api/tests/integration.rs

Line 1455 in 9ec4cde

let hello = U16String::from_str("Hello");

happy to tell you, I have a working prototype already. A minimal form of the feature could be released soon.

Oh, that sounds awesome!

What is your usecase? Do you

a) want to insert lots of strings into a table or
b) use a wchar as a parameter in e.g. a WHERE clause of a query or
c) insert/update a single row in the DB

My use case is b) and c). Primarily I'm just prototyping a more or less generic SQL client API to my scripting language WLambda ( https://github.com/WeirdConstructor/WLambda/blob/master/src/stdlib/odbc.rs ). And I mostly need it these days for a quick tool to access some old legacy database.

odbc-api/odbc-api/tests/integration.rs

Line 1455 in 9ec4cde

let hello = U16String::from_str("Hello");

Oh that seems perfect, and thanks for uploading it so quickly! Will try it out right away.

I tried it now. Unfortunately I run into lifetime issues, because it's only implemented for borrowed U16String:

odbc-api/odbc-api/src/into_parameter.rs

Lines 110 to 129 in 3ee615c

    
           impl<'a> IntoParameter for &'a U16Str { 
        
               type Parameter = VarWCharSlice<'a>; 
        
               fn into_parameter(self) -> Self::Parameter { 
        
                   let slice = self.as_slice(); 
        
                   let (_, bytes, _) = unsafe { slice.align_to::<u8>() }; 
        
                   VarWCharSlice::from_buffer(bytes, Indicator::Length(bytes.len())) 
        
               } 
        
           } 
        
           impl<'a> IntoParameter for Option<&'a U16Str> { 
        
               type Parameter = VarWCharSlice<'a>; 
        
               fn into_parameter(self) -> Self::Parameter { 
        
                   match self { 
        
                       Some(str) => str.into_parameter(), 
        
                       None => VarWCharSlice::NULL, 
        
                   } 
        
               } 
        
           }

I am using it in code like this, which Box<..>es input parameters. Maybe I'm doing something completely insane though :-)

    let mut params: Vec<Box<dyn odbc_api::parameter::InputParameter>> = vec![];
    if let Some(input_params) = input_params {
        use odbc_api::IntoParameter;
        for p in input_params.into_iter() {
            match p {
                WParam::Str(s) => {
                    let u16str = U16String::from_str(&s);
                    // XXX: breaks here, due to u16str only be a temporary:
                    params.push(Box::new(u16str.into_parameter()))
                },
                WParam::Double(d) => params.push(Box::new(d.into_parameter())),
                WParam::Bytes(uv) => params.push(Box::new(uv.into_parameter())),
                WParam::Bit(b) => {
                    let b: i16 = if b { 1 } else { 0 };
                    params.push(Box::new(b.into_parameter()))
                }
                WParam::Null => params.push(Box::new(odbc_api::Nullable::<i16>::null())),
            }
        }
    }

    let mut cursor = match prep.execute(&params[..]) {
        Ok(None) => return Ok(VVal::None),
        Err(e) => {
            return Err(format!("SQL execute error (query: {}): {}", sql, e));
        }
        Ok(Some(cursor)) => cursor,
    };

The extra allocations are not optimal, but I would love to keep that borrow checker optimization fight for later :-)

Hello @WeirdConstructor ,

thanks for the feedback. Implementing it for U16String is on my todo list. I wanted to deliver something fast so I relied on basically a transmute form u16 to u8 to implement it in terms of &str. This however won't work for owned parameters. I see no major hurdles in implementing it though.

Just looking at your code, you might want to explore the design space around implementing IntoParameter directly for WParam.

Best, Markus

Thanks a lot :-)

This however won't work for owned parameters. I see no major hurdles in implementing it though.

Yes, I tried hacking it in myself. But it turned out much more involved than I was anticipating and I ran out of time today. (And I am not familiar enough with the code to make any unsafe changes).

Just looking at your code, you might want to explore the design space around implementing IntoParameter directly for WParam.

Oh, good idea! I will look into that :-)

@pacman82 Hi! I've seen you worked on the U16String parameter implementation.

Wanted to give you feedback here and a big thanks! It works amazingly with this old Oracle 11 Database and Oracle Instant Client Version 12:

Thanks :-) I close this issue then.

Hey thanks, for the feedback! You are welcome.

	impl<'a> IntoParameter for &'a U16Str {
	type Parameter = VarWCharSlice<'a>;

	fn into_parameter(self) -> Self::Parameter {
	let slice = self.as_slice();
	let (_, bytes, _) = unsafe { slice.align_to::<u8>() };
	VarWCharSlice::from_buffer(bytes, Indicator::Length(bytes.len()))
	}
	}

	impl<'a> IntoParameter for Option<&'a U16Str> {
	type Parameter = VarWCharSlice<'a>;

	fn into_parameter(self) -> Self::Parameter {
	match self {
	Some(str) => str.into_parameter(),
	None => VarWCharSlice::NULL,
	}
	}
	}