+1 on some kind of test framework I can tell that Josh has put in a lot of effort trying to reproduce this problem based on descriptions in emails. For the sake of the sanity of our committers, these kind of problems should be accompanied by a pull request that contains a script that reproduces the issue. What language that script is written in is up for debate.
Julian > On Apr 18, 2016, at 7:20 AM, Josh Elser <[email protected]> wrote: > > Ok, I will try to circle around to this. > > Meanwhile, let's start thinking about ways that you and I can better > collaborate :) > > We want to make building drivers for Avatica in other languages as painless > as possible, but it seems like we have a big impedance mismatch already -- > not having a common language/test framework we can both use. What kind of > tooling would help you in testing your driver and give us a common-base to > analyze failures that you run into? > > * Dumping of protobufs to stdout by the Avatica server(PQS)? > * Known protobuf requests/responses for executing some SQL? > * ... something else entirely? > > F21 wrote: >> I have now created a repo on github containing all the binary protobuf >> requests and responses to insert binary data into a VARBINARY column. >> The repo is available here: https://github.com/F21/avatica-binary-protobufs >> >> The file 8-request is of most interest because that's the one where we >> are sending the ExecuteRequest to do the actual insertion of the image. >> >> I am using the protoc tool to decode the messages, e.g.: protoc >> --decode_raw < 8-request >> >> Let me know if this helps :) >> >> On 18/04/2016 3:31 AM, Josh Elser wrote: >>> Sorry, yes. I just didn't want to have to post the JSON elsewhere -- >>> it would've been gross to include in an email. For JSON, it is base64 >>> encoded. This is not necessary for protobuf (which can natively handle >>> raw bytes). >>> >>> For #2, did you verify that the data made it into the database >>> correctly? HBase/Phoenix still, right? Also, maybe this is a >>> SquirrelSQL issue? Can you verify the record is present via Phoenix's >>> sqlline-thin.py? >>> >>> For #3, very interesting. I'm not sure why base64 encoding it by hand >>> makes any difference for you. Avatica isn't going to be making any >>> differentiation in the types of bytes that you send. Bytes are bytes >>> are bytes as far as we care. >>> >>> F21 wrote: >>>> Just reporting back my experiments: >>>> >>>> 1. Using JSON: I set the serialization of the query server to JSON and >>>> replayed your requests using CURL. For the binary file, I first base64 >>>> encode it into a string and then sent it. This worked properly and I can >>>> see data inserted into the table using SquirrelSQL. >>>> >>>> 2. Using Protobufs: I inserted the binary data using Rep_BYTE_STRING and >>>> set BytesValues to the byte array read in from the file. It inserts >>>> (upserts) correctly, but if I query the table using SquirrelSQL, the >>>> binary column's cell is shown as <null>. >>>> >>>> 3. Using Protobufs with Base64 encoding: I first encode the binary data >>>> as base64. I then upsert the parameter as Rep_STRING and set StringValue >>>> to the base64 encoded string. This upserts correctly and I can see the >>>> data in SquirrelSQL. I then SELECT the data and base64 decode it and >>>> write it to a file and generate a hash. The file is written correctly >>>> and the hash also matches. >>>> >>>> So, approach 3 works for me, but it doesn't seem to be the correct way >>>> to do it. >>>> >>>> On 17/04/2016 8:17 AM, Josh Elser wrote: >>>>> 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": > "B >>>>> >>> YTE >>>>> >>>>> _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 <[email protected]> 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! >>>>>>>>> >>>>>>> >>>>>>> >>>> >>>> >> >>
