calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Josh Elser <josh.el...@gmail.com>
Subject Re: How do I send binary data to avatica?
Date Sat, 16 Apr 2016 22:17:49 GMT
I wrote a simple test case for this with the gopher image. Here's the 
JSON data (but hopefully this is enough to help out). I'd have to write 
more code to dump out the actual protobufs. Let me know if this is 
insufficient to help you figure out what's wrong. My test worked fine.

CREATE TABLE binaryData(id int, data varbinary(262144));

[{"request":"prepare","connectionId":"b387f9f2-6e64-40c3-a26f-f9cd955bc0a7","sql":"INSERT

INTO binaryData values(?,?)","maxRowCount":-1}, 
{"response":"prepare","statement":{"connectionId":"b387f9f2-6e64-40c3-a26f-f9cd955bc0a7","id":1,"signature":{"columns":[],"sql":"INSERT

INTO binaryData 
values(?,?)","parameters":[{"signed":true,"precision":10,"scale":0,"parameterType":4,"typeName":"INTEGER","className":"java.lang.Integer","name":"?1"},{"signed":false,"precision":262144,"scale":0,"parameterType":-3,"typeName":"VARBINARY","className":"[B","name":"?2"}],"cursorFactory":{"style":"LIST","clazz":null,"fieldNames":null},"statementType":"SELECT"}},"rpcMetadata":null}]

[{"request":"execute","statementHandle":{"connectionId":"b387f9f2-6e64-40c3-a26f-f9cd955bc0a7","id":1,"signature":null},"parameterValues":[{"type":"INTEGER","value":1},{"type":"BYTE_STRING","value":"<REDACTED>"}],"maxRowCount":100},

{"response":"executeResults","missingStatement":false,"rpcMetadata":null,"results":[{"response":"resultSet","connectionId":"b387f9f2-6e64-40c3-a26f-f9cd955bc0a7","statementId":1,"ownStatement":true,"signature":null,"firstFrame":null,"updateCount":1,"rpcMetadata":null}]}]
[{"request":"closeStatement","connectionId":"b387f9f2-6e64-40c3-a26f-f9cd955bc0a7","statementId":1},

{"response":"closeStatement","rpcMetadata":null}]

[{"request":"prepareAndExecute","connectionId":"b387f9f2-6e64-40c3-a26f-f9cd955bc0a7","statementId":0,"sql":"SELECT

*
   FROM binaryData","maxRowCount":-1},
{"response":"executeResults","missingStatement":false,"rpcMetadata":null,"results":[{"response":"resultSet","oonnectionId":"b387f9f2-6e64-40c3-a26f-f9cd955bc0a7","statementId":0,"ownStatement":true,"signature":{"columns":[{"ordinal":0,"autoIncrement":false,"caseSensitive":false,"searchable":true,"currency":false,"nullable":1,"signed":true,"displaySize":11,"label":"ID","columnName":"ID","schemaName":"SCOTT","precision":32,"scale":0,"tableName":"BINARYDATA","catalogName":"PUBLIC","type":{"type":"scalar","id":4,"name":"INTEGER","rep":"PRIMITIVE_INT"},"readOnly":false,"writable":false,"definitelyWritable":false,"columnClassName":"java.lang.Integer"},{"ordinal":1,"autoIncrement":false,"caseSensitive":false,"searchable":true,"currency":false,"nullable":1,"signed":false,"displaySize":262144,"label":"DATA","columnName":"DATA","schemaName":"SCOTT","precision":262144,"scale":0,"tableName":"BINARYDATA","catalogName":"PUBLIC","type":{"type":"scalar","id":-3,"name":"VARBINARY","rep":"BYTE
_STRING"},"readOnly":false,"writable":false,"definitelyWritable":false,"columnClassName":"[B"}],"sql":null,"parameters":[],"cursorFactory":{"style":"LIST","clazz":null,"fieldNames":null},"statementType":"SELECT"},"firstFrame":{"offset":0,"done":true,"rows":[[1,"<REDACTED>"]]},"updateCount":-1,"rpcMetadata":null}]}]

Josh Elser wrote:
> Super helpful! Thanks.
>
> I'll try to take a look at this tonight. If not then, over the weekend,
> likely. Will report back.
>
> F21 wrote:
>> Hey Josh,
>>
>> Here's what I am doing:
>>
>> Create the table: CREATE TABLE test ( int INTEGER PRIMARY KEY, bin
>> VARBINARY) TRANSACTIONAL=false
>> Download the binary file we want to insert from here:
>> https://raw.githubusercontent.com/golang-samples/gopher-vector/master/gopher.png
>>
>>
>>
>> Prepare the statement: UPSERT INTO test VALUES (?, ?)
>>
>> Here's the code I am using to execute the statement:
>>
>> // read the file into a byte array []byte
>> file, _ := ioutil.ReadFile(filePath)
>>
>> // create array of typed values containing the parameters
>> parameters := []*message.TypedValue{
>> &message.TypedValue{
>> Type: message.Rep_LONG,
>> NumberValue: 1,
>> },
>> &message.TypedValue{
>> Type: message.Rep_BYTE_STRING,
>> BytesValues: file,
>> },
>> }
>>
>> // create message:
>> msg := &message.ExecuteRequest{
>> StatementHandle: &s.handle, // this is just the statement handle
>> ParameterValues: parameters,
>> MaxRowCount: 100,
>> HasParameterValues: true,
>> }
>>
>> // encode the message:
>> wrapped, _ := proto.Marshal(message)
>>
>> // wrap it in the wire message:
>> wire := &message.WireMessage{
>> Name: " org.apache.calcite.avatica.proto.Requests$ ExecuteRequest",
>> WrappedMessage: wrapped,
>> }
>>
>> // encode wire message and send over http:
>> body, _ := proto.Marshal(wire)
>> response, _ := ctxhttp.Post(context.Background(), httpClient,
>> avaticaServer, "application/x-google-protobuf", bytes.NewReader(body))
>>
>> Let me know if there's more information I can provide or if something's
>> unclear :)
>>
>> On 15/04/2016 2:46 AM, Josh Elser wrote:
>>> Yeah, that sounds right to me too.
>>>
>>> I think we have a test for random bytes. Maybe there's something weird
>>> happening under the hood in the Avatica JDBC driver that isn't obvious
>>> to you in the Go driver.
>>>
>>> Any chance you can share some example code you're running? I can try
>>> to convert it to a Java test case, maybe help track down your issue.
>>>
>>> F21 wrote:
>>>> I also tried casting the data to a string and setting it to StringValue
>>>> and the Rep type to STRING.
>>>>
>>>> This works when I store and retrieve strings from the binary column,
>>>> but
>>>> doesn't work correctly if I try to store something like a small image.
>>>>
>>>> On 14/04/2016 5:03 PM, Julian Hyde wrote:
>>>>> BytesValue sounds right. I’m not sure why it isn’t working for you.
>>>>>
>>>>>> On Apr 14, 2016, at 6:34 AM, F21 <f21.groups@gmail.com> wrote:
>>>>>>
>>>>>> As mentioned on the list, I am currently working on a golang
>>>>>> client/driver for avatica using protobufs for serialization.
>>>>>>
>>>>>> I've got all datatypes working, except for BINARY and VARBINARY.
>>>>>>
>>>>>> For my test table looks like this:
>>>>>> CREATE TABLE test (int INTEGER PRIMARY KEY, bin BINARY(20), varbin
>>>>>> VARBINARY) TRANSACTIONAL=false
>>>>>>
>>>>>> In go, we have a datatype called a slice of bytes ([]byte) which
is
>>>>>> essentially an array of bytes (8-bits each).
>>>>>>
>>>>>> When I generated the golang protobufs using the .proto files, this
is
>>>>>> the definition of TypedValue:
>>>>>> type TypedValue struct {
>>>>>> Type Rep `protobuf:"varint,1,opt,name=type,enum=Rep"
>>>>>> json:"type,omitempty"`
>>>>>> BoolValue bool
>>>>>> `protobuf:"varint,2,opt,name=bool_value,json=boolValue"
>>>>>> json:"bool_value,omitempty"`
>>>>>> StringValue string
>>>>>> `protobuf:"bytes,3,opt,name=string_value,json=stringValue"
>>>>>> json:"string_value,omitempty"`
>>>>>> NumberValue int64
>>>>>> `protobuf:"zigzag64,4,opt,name=number_value,json=numberValue"
>>>>>> json:"number_value,omitempty"`
>>>>>> BytesValues []byte
>>>>>> `protobuf:"bytes,5,opt,name=bytes_values,json=bytesValues,proto3"
>>>>>> json:"bytes_values,omitempty"`
>>>>>> DoubleValue float64
>>>>>> `protobuf:"fixed64,6,opt,name=double_value,json=doubleValue"
>>>>>> json:"double_value,omitempty"`
>>>>>> Null bool `protobuf:"varint,7,opt,name=null" json:"null,omitempty"`
>>>>>> }
>>>>>>
>>>>>> I am currently creating a TypedValue that looks like this when
>>>>>> sending binary data:
>>>>>> {BYTE_STRING false 0 [116 101 115 116] 0 false}
>>>>>>
>>>>>> So, the Rep is set to BYTE_STRING and ByteValues is populated with
>>>>>> the string "test" in bytes (it's shown here as decimal because I
>>>>>> printed it).
>>>>>>
>>>>>> The problem is that even though the insert executes properly, if
I
>>>>>> look at the row using SquirrelSQL, both the BINARY and VARBINARY
>>>>>> columns are <null>.
>>>>>>
>>>>>> Is BYTE_STRING the correct rep type for sending binary data? Do I
>>>>>> also have to encode my bytes in a special format?
>>>>>>
>>>>>> Thanks!
>>>>
>>
>>

Mime
View raw message