MySQL Binary Protocol Prepare Response

Question

Working on some code that uses the MySQL Binary protocol to talk to the DB.

http://dev.mysql.com/doc/internals/en/client-server-protocol.html

I have a table:

CREATE TABLE People ( ID  INTEGER, Name VARCHAR(64), Age  SMALLINT, Sex  CHAR(1), Height DOUBLE);

When I send a COM_STMT_PREPARE to the server with the statement "SELECT * FROM People where sex=? or Age=?". I get a COM_STMT_PREPARE_OK back from the server.

This package contains 2 parameters and 5 columns all defined in ColumnDefinition41 packets with the following values:

PARAMETERS:
ColumnDefinition:
    catalog:                    def
    schema:                     <empty string>
    table:                      <empty string>
    orgTable:                   <empty string>
    name:                       ?
    orgName:                    <empty string>
    lengthOfFixedField:         12
    charSet:                    63
    columnLength:               0
    type:                       253(MYSQL_TYPE_VAR_STRING)
    flags:                      128
    decimal:                    0

ColumnDefinition:
    catalog:                    def
    schema:                     <empty string>
    table:                      <empty string>
    orgTable:                   <empty string>
    name:                       ?
    orgName:                    <empty string>
    lengthOfFixedField:         12
    charSet:                    63
    columnLength:               0
    type:                       253(MYSQL_TYPE_VAR_STRING)
    flags:                      128
    decimal:                    0


Columns:
ColumnDefinition:
    catalog:                    def
    schema:                     test
    table:                      People
    orgTable:                   people
    name:                       ID
    orgName:                    ID
    lengthOfFixedField:         12
    charSet:                    63
    columnLength:               11
    type:                       3(MYSQL_TYPE_LONG)
    flags:                      0
    decimal:                    0

ColumnDefinition:
    catalog:                    def
    schema:                     test
    table:                      People
    orgTable:                   people
    name:                       Name
    orgName:                    Name
    lengthOfFixedField:         12
    charSet:                    33
    columnLength:               192
    type:                       253(MYSQL_TYPE_VAR_STRING)
    flags:                      0
    decimal:                    0

ColumnDefinition:
    catalog:                    def
    schema:                     test
    table:                      People
    orgTable:                   people
    name:                       Age
    orgName:                    Age
    lengthOfFixedField:         12
    charSet:                    63
    columnLength:               6
    type:                       2(MYSQL_TYPE_SHORT)
    flags:                      0
    decimal:                    0

ColumnDefinition:
    catalog:                    def
    schema:                     test
    table:                      People
    orgTable:                   people
    name:                       Sex
    orgName:                    Sex
    lengthOfFixedField:         12
    charSet:                    33
    columnLength:               3
    type:                       254(MYSQL_TYPE_STRING)
    flags:                      0
    decimal:                    0

ColumnDefinition:
    catalog:                    def
    schema:                     test
    table:                      People
    orgTable:                   people
    name:                       Height
    orgName:                    Height
    lengthOfFixedField:         12
    charSet:                    63
    columnLength:               22
    type:                       5(MYSQL_TYPE_DOUBLE)
    flags:                      0
    decimal:                    31

The second parameter has a type of MYSQL_TYPE_VAR_STRING but I was expecting to have a type of MYSQL_TYPE_SHORT because it was binding to the column Age in the table People which has this type.

Are the Parameters always going to have a type MYSQL_TYPE_VAR_STRING or is there some way to get the server to respond with the type of the parameter I am binding too?

In case it is worth noting:

> mysql --help
mysql  Ver 14.14 Distrib 5.6.22, for osx10.10 (x86_64) using  EditLine wrapper
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Answer 1

Parameters passed to the SQL server over the binary protocol are always encoded as strings. The SQL server then parses the strings and converts them internally to the correct types.