working code examples

[jschoch]

The auto generated code examples are not helpful.

Background: I haven't done any ruby for about 10 years. I am familiar with dynamodb, i can write elixir and c/c++ at a bit better than novice level and novice level java. I know what a prepared statement is but I haven't messed with oracle databases since 2002. I decided to pick ruby because I thought it would provide the least barrier to me learning how to query nosql.

There are no results when trying to google "ruby" "Nosql" "PreparedStatement" select from where There are no code examples when searching github for nosql querydetails statement language:Ruby There are no tests to look at. Reading the source wasn't much help.

take query: https://docs.oracle.com/en-us/iaas/tools/ruby-sdk-examples/2.15.0/nosql/query.rb.html

query_response =
  nosql_client.query(
    OCI::Nosql::Models::QueryDetails.new(
      compartment_id: 'ocid1.test.oc1..<unique_ID>EXAMPLE-compartmentId-Value',
      statement: 'EXAMPLE-statement-Value',
      is_prepared: true,
      consistency: 'EVENTUAL',
      max_read_in_kbs: 983,
      variables: { 'EXAMPLE_KEY_tSlWQ' => 'EXAMPLE--Value' },
      timeout_in_ms: 823
    )
  )

As a newbie I have no idea what EXAMPLE-statement-Value is. Further as a oracle NOSQL newebie I have no idea what the prepared syntax is.

I tried to find out what the interpolation needed was and through trial and error was able to get it to work with this:

query_response =
  ns.query(
    OCI::Nosql::Models::QueryDetails.new(
      compartment_id: cid,
      statement: "declare $hsh string; SELECT * FROM always_free_table where hsh= $hsh",
      #is_prepared: true,
      consistency: 'EVENTUAL',
      max_read_in_kbs: 983,
      variables: { '$hsh' => 'my' },
      timeout_in_ms: 823
    )
  )

So for example having to declair the variable key "$hsh" vs just "hsh" or hsh: was not obvious. Also the errors thrown while brute forcing this were not helpful

/usr/share/gems/gems/oci-2.15.0/lib/oci/api_client.rb:478:in `handle_non_success_response': QUERY: Illegal Argument: Invalid serialized prepared statement: Cannot deserialize value of type `byte[]` from String "declare $hsh string;SELECT * FROM always_free_table where hsh = $hsh": Illegal white space character (code 0x20) as character #4 of 4-char base64 unit: can only used between units (OCI::Errors::ServiceError)
 at [Source: UNKNOWN; line: -1, column: -1]
        from /usr/share/gems/gems/oci-2.15.0/lib/oci/api_client.rb:390:in `call_api_inner'
        from /usr/share/gems/gems/oci-2.15.0/lib/oci/api_client.rb:127:in `call_api'
        from /usr/share/gems/gems/oci-2.15.0/lib/oci/nosql/nosql_client.rb:1360:in `block in query'
        from /usr/share/gems/gems/oci-2.15.0/lib/oci/retry/retry.rb:24:in `make_retrying_call'
        from /usr/share/gems/gems/oci-2.15.0/lib/oci/nosql/nosql_client.rb:1359:in `query'
        from cunt.rb:53:in `<main>'

I'd like to uncomment the is_prepared flag but I don't understand what it wants.

A bit of example code that shows the string interpolation and variable format would have saved me quite a bit of time, however the bigger issue is that many would just quit and use something else with better documentation. For example, what is a working input string to prepare_statement? The below doesn't tell me much:

# Send the request to service, some parameters are not required, see API doc for more info
prepare_statement_response =
  nosql_client.prepare_statement(
    'ocid1.test.oc1..<unique_ID>EXAMPLE-compartmentId-Value',
    'EXAMPLE-statement-Value'
  )

[jodoglevy]

Hi @jschoch - thanks for reporting this. I'll route it to the NoSQL service team, since they are the experts on what can be provided for the statement parameter, what the is_prepared flag is for, and what docs exist around these two parameters. They are also the ones returning that byte[] error to you and so can work on improving that error.

[gmfeinberg]

Hi @jschoch - I'm with the NoSQL team. The same QueryDetails object can be used to execute prepared queries or execute queries directly using a SQL statement. The _is_prepared boolean indicates whether the statement_ is a SQL statement (a string) or the result of a prepared query (a byte[]). The error you see is that you apparently set is_prepared to true but passed a SQL string.

You can prepare a query using the prepare_statement method, e.g: response = client.prepare_statement( compartment_id, 'select * from mytable' )

Then you can use this in your query: response = client.query( OCI::Nosql::Models::QueryDetails.new( compartment_id: compartment_id, statement: response.data.statement, is_prepared: true) )

A heads up -- certain types of queries that are supported by the Oracle NoSQL Database SDKs directly are not supported in the OCI SDK. They include aggregations and sorting where are complete shard key is not included in the query. This is because of SDK-based processing of such results that is not present in the OCI SDK. There is no NoSQL Database SDK for Ruby although they exist for Java, Python, Go, Node.js and (shortly) .NET.

[jschoch]

Thanks for the reply.

response = client.prepare_statement( compartment_id, 'select * from mytable' )

This is a bit of a strange example since it doesn't have any variables to interpolate and doesn't really make sense to do as a prepared statement. Where is the documentation on the interpolation rules and conventions?

[gmfeinberg]

@jschoch If your intent is to learn how to use NoSQL you should use one of the SDKs that is not an OCI SDK. They have more complete user guide, examples, and API documentation. An example is the NoSQL Python SDK. There are also SDKs for Java, Go, and Node.js.

An example of the type of information you'll find is the Python Using Queries section of the documentation

In addition there is a reference guide to Oracle NoSQL SQL Language. This is independent of programming language API. Because the SQL language is shared with our on-premise offering not all features and statements are relevant in the cloud service.

While the OCI SDKs work with NoSQL and cover additional languages but as you've seen the guidance is limited.

Let me know if you want to continue with Ruby and I'll try to help answer specific questions

[mikebrey]

@jschoch I would suggest running some of the Live Labs to get a better idea about the service. They are packed full of information.

• https://apexapps.oracle.com/pls/apex/dbpm/r/livelabs/view-workshop?wid=642&clear=180&session=4761826047361
• https://apexapps.oracle.com/pls/apex/dbpm/r/livelabs/view-workshop?wid=879&clear=180&session=4761826047361

[gmfeinberg]

@jschoch Is there anything further we can do to help with this issue or can it be closed? It appears that you wanted information about the query language, which is available in the links posted. If you need additional information please let me know

[KartikShrikantHegde]

Hi @mikebrey, I will close this ticket since we haven't heard from you in a while. Please feel free to reopen if you still have problems.