Ruby API returns different result based on same SQL but prepared vs execute_direct

[kbachl]

Hi,

following code:

 sql = <<-END_SQL
            select count("ItemCode") from "TEST2024"."OITM" where "ItemCode" =  ?
        END_SQL
        p item_code

        # this won't work, allways returns [0,0]!
        stmt = @@api.hanaclient_prepare @@connection, sql
        _, param = @@api.hanaclient_describe_bind_param(stmt, 0)
        param.set_value(item_code)
        @@api.hanaclient_bind_param(stmt, 0, param)
        _rc = @@api.hanaclient_execute(stmt)
        @@api.hanaclient_fetch_next( stmt )


        puts "R1: " + @@api.hanaclient_get_column(stmt, 0).to_s
        @@api.hanaclient_free_stmt(stmt)


        #This works -> correct results returned:
        sql = ActiveRecord::Base.sanitize_sql([sql, item_code])
        stmt = @@api.hanaclient_execute_direct(@@connection, sql)
        @@api.hanaclient_fetch_next( stmt )
        puts "R2: " +  @@api.hanaclient_get_column(stmt, 0).to_s

whatever I try I allways get a return of array [0,0] in case of my prepared statement compared to the direct executed one that returns the correct and expected data.

Result:

11:38:27 web.1             | "CODE-1"
11:38:27 web.1             | R1: [1, 0]
11:38:27 web.1             | R2: [1, 1]
11:38:27 web.1             | nil
11:38:27 web.1             | "CODE-2"
11:38:27 web.1             | R1: [1, 0]
11:38:27 web.1             | R2: [1, 1]
11:38:27 web.1             | nil
11:38:27 web.1             | "NOTEXISITNG"
11:38:27 web.1             | R1: [1, 0]
11:38:27 web.1             | R2: [1, 0]
11:38:27 web.1             | nil

Im using the latest driver 20.20.20. Any idea what I made wrong in my prepared statement?

[jeff_albion]

Hello,

I can't seem to reproduce this behaviour with a simplified example:

require 'hanaclient'
include HANACLIENT

@@api = HANACLIENT::HANACLIENTInterface.new()
HANACLIENT::API.hanaclient_initialize_interface(@@api)
@@api.hanaclient_init()
@@conn = @@api.hanaclient_new_connection()
@@api.hanaclient_connect(@@conn, "servernode=myserver:30015;UID=myuser;PWD=mypwd")

sql = <<-END_SQL
            select 42 from "SYS"."DUMMY" where 1 = ?
        END_SQL
item_code = 1

stmt = @@api.hanaclient_prepare @@conn, sql
_, param = @@api.hanaclient_describe_bind_param(stmt, 0)
param.set_value(item_code)
@@api.hanaclient_bind_param(stmt, 0, param)
_rc = @@api.hanaclient_execute(stmt)
@@api.hanaclient_fetch_next( stmt )

puts "R1: " + @@api.hanaclient_get_column(stmt, 0).to_s
@@api.hanaclient_free_stmt(stmt)

@@api.hanaclient_disconnect(@@conn)
@@api.hanaclient_free_connection(@@conn)
@@api.hanaclient_fini()
HANACLIENT::API.hanaclient_finalize_interface(@@api)

Can you also run my example and see the correct value?

From your example, are you sure that the value being bound is being found correctly in the table? Have you tried adding SQLDBC Tracing 

to your application to better understand what's happening inside the driver for the bind value and result? You can quickly set environment variables to trace to stdout if that's helpful:

export HDB_SQLDBC_TRACEFILE=stdout
export HDB_SQLDBC_TRACEOPTS=SQL,DEBUG,FLUSH

Best regards,

Jeff

[kbachl]

I made an "Answer" below - seems I cant directly reply to you

[jeff_albion]

Hi @kbachl, thanks for trying the updated instructions. I can see from your trace that the string value isn't bound properly from the ruby level:

10:13:01 web.1             | [SQLDBC] >StringTranslator::translateUTF8Input (2024-05-17 10:13:01.192845)
10:13:01 web.1             | [SQLDBC] data=
10:13:01 web.1             | [SQLDBC] >GenericTranslator::addCharacterData (2024-05-17 10:13:01.192855)
10:13:01 web.1             | [SQLDBC] <=SQLDBC_OK
10:13:01 web.1             | [SQLDBC] <=SQLDBC_OK
10:13:01 web.1             | [SQLDBC] <=SQLDBC_OK
10:13:01 web.1             | [SQLDBC] 1     UTF8          0             0             ''

I had been originally trying Ruby 2.7, but couldn't reproduce the problem:

[SQLDBC] >GenericTranslator::addCharacterData (2024-05-17 16:21:08.715159)
[SQLDBC] <=SQLDBC_OK
[SQLDBC] <=SQLDBC_OK
[SQLDBC] <=SQLDBC_OK
[SQLDBC] 1     UTF8          9             9             'TestValue'

ruby 2.7.6p219 (2022-04-12 revision c9c2245c0a) [x86_64-linux]

After I upgraded to the latest Ruby 3.3, I could then reproduce the problem as you described:

ruby 3.3.1 (2024-04-23 revision c56cd86388) [x86_64-linux]

[SQLDBC] >GenericTranslator::addCharacterData (2024-05-17 16:13:54.026311)
[SQLDBC] <=SQLDBC_OK
[SQLDBC] <=SQLDBC_OK
[SQLDBC] <=SQLDBC_OK
[SQLDBC] 1     UTF8          0             0             ''

I am also noticing these ruby messages in Ruby 3.3:

simple.rb:7: warning: undefining the allocator of T_DATA class DBCAPI::a_hanaclient_connection
simple.rb:14: warning: undefining the allocator of T_DATA class DBCAPI::a_hanaclient_stmt
simple.rb:15: warning: undefining the allocator of T_DATA class DBCAPI::a_hanaclient_bind_data

So it seems like there's a bug with newer Ruby versions here - I have opened an internal bug report to investigate further.

Can you try an older version of Ruby for now?

Note that the official Supported Platforms page only lists up to Ruby 2.7 currently: https://me.sap.com/notes/3447024 

Best regards,

Jeff

[kbachl]

@jeff_albionThanks for the update!

I hardly can go on to ruby 2.7 as I'm doing a current new rails project and ruby 3.1.0 is the new baseline for upcoming rails 7.2, me using ruby 3.3.1.
Beside ruby 2.7 is EOL since 2023-03-31. Since I only need this on some rare occasions (when the SAP B1 ServiceLayer is too slow or can't deliver the complexity of a query well enough) I can go forward with using this with the direct SQL way of using ActiveRecord's base

 sql = ActiveRecord::Base.sanitize_sql([sql, item_code])

 for the time. Yet, it would also be nice if you could do a bug report that an access to a non existing row or column dont lead to a SegFault like in my last post as this is quite annoying in development 🙂