QueryRow().Scan() fails for query with binding variables

[hqin]

Description

QueryRow().Scan() fails for query with binding variables

Expected behaviour

QueryRow().Scan() should return correct result

Current behaviour

It returns error:

go-ase: cursor deletion finished with error: tds: error in user-defined processing function: query failed with errors: received EED messages: 557: The cursor '(Unknown name)' can not be used as it could not be found. It is possible that either it was not declared or it is not available in the current context.;

Steps to Reproduce

Steps to reproduce the behavior:

1. Export the following environment variables: export DB_USERNAME= export DB_PASSWORD= export DB_DATABASE= export DB_HOSTNAME= export DB_PORT= export DB_DRIVER=ase

2. Prepare test data

   create table TEST_PROFILE(UserId BIGINT, ItemName varchar(32) not null, ItemValue varchar(32) not null)
   create unique clustered index iuc_test_profile on TEST_PROFILE(UserId, ItemName)
   insert into TEST_PROFILE values(1, "CUR_ACCOUNT", "1200")

3. Run

   DB_DRIVER=ase go test  -v

4. See error

   === RUN   TestQueryRowWithoutBindingVars
   --- PASS: TestQueryRowWithoutBindingVars (0.10s)
   === RUN   TestQueryRowWithBindingVars
   queryrow_test.go:41: querying failed: go-ase: cursor deletion finished with error: tds: error in user-defined processing function: query failed with errors: received EED messages: 557: The cursor '(Unknown name)' can not be used as it could not be found. It is possible that either it was not declared or it is not available in the current context.;
   --- FAIL: TestQueryRowWithBindingVars (0.10s)
   FAIL

5. See the correct results from tds driver for comparison:

   DB_DRIVER=tds go test -v
   === RUN   TestQueryRowWithoutBindingVars
   --- PASS: TestQueryRowWithoutBindingVars (0.03s)
   === RUN   TestQueryRowWithBindingVars
   --- PASS: TestQueryRowWithBindingVars (0.03s)
   PASS
   ok      main    0.075s

Environment

Client: OS: Centos-7 go version: 1.16.3 go-ase version / commit hash: v0.0.0-20210401085605-ce408ad54d8b go-dblib version / commit hash: v0.0.0-20210315102028-46975ce12d39

Server: OS: Centos-7 ASE open client version: 16.0_SP03PL02_EBF28306.el7 ASE data server version: 16.0_SP03PL09_EBF29247.r7.x86_64

Additional context

queryrow_test.go.gz

[frewilhelm]

Thank you for the issue and the informative context! Unfortunately, we were not able to reproduce the error with your test (yet).

However, we cannot conduct the test with the (deprecated) ASE release 16.0_SP03PL02_EBF28306.el7, because the release is (currently) not available for us. Nevertheless, we suspect that the deprecated release could be the cause of the problem:

• The error indicates that something related to the cursors does not work

  [...] received EED messages: 557: The cursor '(Unknown name)' can not be used as it could not be found. It is possible that either it was not declared or it is not available in the current context.;

• There were several CR addressing cursors since the release of 16.0_SP03PL02_EBF28306.el7.

---

There are now several approaches to shed light on the issue:

• Upgrade the release if possible, e.g. > 16.0.03.08
• Change the execution-order of TestQueryRowWithoutBindingVars and TestQueryRowWithBindingVars to check if it is related to QueryRow or to the cursor-execution
• Disable the cursors by setting NoQueryCursor to true. For example, by adjusting your func setup(...) to:

  func setup(t *testing.T) {
  envs := []string{"DB_USERNAME", "DB_PASSWORD", "DB_DATABASE", "DB_HOSTNAME", "DB_PORT", "DB_DRIVER"}
  for _, env := range envs {
      if os.Getenv(env) == "" {
          t.Fatalf("Environment variable %v needs to be set", env)
      }
  }
  driver := os.Getenv("DB_DRIVER")                                                                                                                                                                                                                                                                                         
  info, err := ase.NewInfo()
  if err != nil {
      t.Fatalf("failed initializing info: %v", err)
  }                                                                                                                                                                                                                                                                                                                        
  if err := dsn.FromEnv("DB", info); err != nil {
      t.Fatalf("failed retrieving information from environment: %v", err)
  }                                                                                                                                                                                                                                                                                                                        
  var dsnStr string                                                                                                                                                                                                                                                                                                        
  if driver == "tds" {
      dsnStr = driver + "://" + info.Username + ":" + info.Password + "@" + info.Host + ":" + info.Port + "/" + info.Database + "?encryptPassword=no&charset=utf8"
  } else if driver == "ase" {
      info.NoQueryCursor = true
      dsnStr = dsn.FormatSimple(info)
  } else {
      t.Fatalf("Driver '%s' not supported", driver)
  }
  db, err := sql.Open(driver, dsnStr)
  if err != nil {
      t.Fatalf("Failed to open database: %v", err)
  }
  if err := db.Ping(); err != nil {
      db.Close()
      t.Fatalf("Failed to ping database: %v", err)
  }
  DB = db
  }

[hqin]

Before submitting the bug report, I tested with Query() function and it worked without issue. Thus it is not really a cursor issue. The bug lies in the implementation of QueryRow().

Besides, if you run the attached code by setting driver to "tds", QueryRow() works without issue.

I did test with query-no-cursor property set to "true". It worked without using cursors.

[hqin]

By the way, I had a separate bug report for cgo-ase. QueryRow() neither with binding vars nor without binding vars worked.

[frewilhelm]

By the way, I had a separate bug report for cgo-ase. QueryRow() neither with binding vars nor without binding vars worked.

This was fixed in the meantime (see https://github.com/SAP/cgo-ase/pull/23). Thanks for your report!

Before submitting the bug report, I tested with Query() function and it worked without issue. Thus it is not really a cursor issue. The bug lies in the implementation of QueryRow().

As stated above, we cannot replicate your issue. Your tests run flawless on our hosts.

However, we cannot apply the same test environment since the ASE release 16.0_SP03PL02_EBF28306.el7 is deprecated (Additionally, the release 16.0_SP03PL02_EBF28306 is not officially released for production, but rather for testing and evaluation purposes in sandbox systems).

Can you patch your release and/or replicate the issue on an ASE release greater or equal than 16.0 SP03 PL08 HF1 EBF_29398?

[hqin]

I have double checked with our database admin, it turned out that the ASE data server version is 16.0_SP03PL09_EBF29247.r7.x86_64. On our client boxes, we have an older open client version (which is not relevant to goase driver).

I have updated the ticket with the current ASE data server version.

Could one take a look of the fix of https://github.com/SAP/cgo-ase/pull/23? The behavior of this bug looks similar.

[ntnn]

Hi @hqin,

the issue in SAP/cgo-ase#23 was an issue with a Client-Library command struct not being properly aborted, which returned an error for database/sql as it returns the error of the rows.Close instead of the error of the reading of the row in QueryRow.

QueryRow also isn't a separate interface we can handle, it only utilizes the existing interfaces database/sql.Row etc.pp. to implement a simpler interface for the user.

Thanks

[frewilhelm]

I have double checked with our database admin, it turned out that the ASE data server version is 16.0_SP03PL09_EBF29247.r7.x86_64.

We ran your test on an ASE instance with the release 16.0_SP03PL09_EBF29247 and we still cannot replicate the issue, since the test is running fine.

Can you enable DebugLogPackages (see go-dblib) and post the output? It should look similar to this:

>>> ASE_DEBUG_LOG_PACKAGES=true DB_DRIVER=ase go test -v
=== RUN   TestQueryRowWithoutBindingVars
2021/05/12 14:37:54 TX: tds.TokenlessPackage(possibleToken=64) tds.TokenlessPackage{Data:(*bytes.Buffer)(0xc00009a210)}
2021/05/12 14:37:54 TX: Capabilities: map[tds.CapabilityType]*tds.valueMask{0x1:(*tds.valueMask)(0xc0000be018), 0x2:(*tds.valueMask)(0xc0000be030), 0x3:(*tds.valueMask)(0xc0000be048)}
2021/05/12 14:37:54 RX: tds.LoginAckPackage(TDS_LOG_NEGOTIATE)
2021/05/12 14:37:54 RX: tds.MsgPackage(TDS_MSG_HASARGS, TDS_MSG_SEC_ENCRYPT4)
...

[hqin]

Here are the new test programs along with test logs.

testlogs.tar.gz

[hqin]

Any update on this ticket?

[frewilhelm]

@ntnn was able to replicate your error on our hosts using a very specific test-case. Due to the uniqueness of the problem, we are still working on the best approach to solve it. Thank you for your patience!

[ntnn]

GitHub automatically closed this issue because I wrote This may also fix #193 in the PR message.

[frewilhelm]

Hi @hqin, as you may have noticed, we merged https://github.com/SAP/go-ase/pull/197 that fixes an issue similar to yours.

Can you update your go.mod to the latest commit (github.com/SAP/go-ase v0.0.0-20210527145755-79e20c6a1585) and redo your tests? (Regardless of whether it works or not, we would like to take a look at at your DebugLogPackages to verify if this fix actually solves the problem)

[hqin]

I reran the test and it looks that the error no longer shows up testlogs-2020-06-01.tar.gz

[ntnn]

Thank you, it looks like it was the same error.