public static object Read(SQLiteType sqliteType, StatementHandle handle, int ordinal)
{
Debug.Assert(handle != null && !handle.IsInvalid, "handle is null.");
if (sqliteType == SQLiteType.Null ||
NativeMethods.sqlite3_column_type(handle, ordinal) == Constants.SQLITE_NULL)
{
return(DBNull.Value);
}
switch (sqliteType)
{
case SQLiteType.Integer:
return(NativeMethods.sqlite3_column_int64(handle, ordinal));
case SQLiteType.Float:
return(NativeMethods.sqlite3_column_double(handle, ordinal));
case SQLiteType.Text:
return(NativeMethods.sqlite3_column_text(handle, ordinal));
default:
Debug.Assert(sqliteType == SQLiteType.Blob, "_sqliteType is not Blob.");
return(NativeMethods.sqlite3_column_blob(handle, ordinal));
}
}
internal SQLiteDataReader(SQLiteCommand command, IList <StatementHandle> handles, int recordsAffected)
{
Debug.Assert(command != null, "command is null.");
Debug.Assert(handles != null, "handles is null.");
_command = command;
if (handles.Count > 0)
{
_hasRows = true;
_currentHandle = handles[0];
}
_handles = handles;
_recordsAffected = recordsAffected;
}
public override bool NextResult()
{
CheckClosed("NextResult");
_currentIndex++;
if (_currentIndex >= _handles.Count)
{
return(false);
}
_hasRead = false;
_currentHandle = _handles[_currentIndex];
return(true);
}
public FesStatement(DatabaseBase db, TransactionBase transaction)
{
if (!(db is FesDatabase))
{
throw new ArgumentException($"Specified argument is not of {nameof(FesDatabase)} type.");
}
_db = (FesDatabase)db;
_handle = new StatementHandle();
OutputParameters = new Queue <DbValue[]>();
_statusVector = new IntPtr[IscCodes.ISC_STATUS_LENGTH];
_fetchSqlDa = IntPtr.Zero;
if (transaction != null)
{
Transaction = transaction;
}
}
public FesStatement(IDatabase db, TransactionBase transaction)
{
if (!(db is FesDatabase))
{
throw new ArgumentException("Specified argument is not of FesDatabase type.");
}
_recordsAffected = -1;
_db = (FesDatabase)db;
_handle = new StatementHandle();
_outputParams = new Queue();
_statusVector = new IntPtr[IscCodes.ISC_STATUS_LENGTH];
_fetchSqlDa = IntPtr.Zero;
if (transaction != null)
{
Transaction = transaction;
}
}
/// <summary>
/// This request is used to execute a PreparedStatement, optionally with values to bind to the parameters in the Statement.
/// </summary>
public async Task <ExecuteResponse> ExecuteRequestAsync(StatementHandle statementHandle, pbc::RepeatedField <TypedValue> parameterValues, ulong firstFrameMaxSize, bool hasParameterValues, RequestOptions options)
{
ExecuteRequest req = new ExecuteRequest
{
StatementHandle = statementHandle,
ParameterValues = parameterValues,
FirstFrameMaxSize = firstFrameMaxSize,
HasParameterValues = hasParameterValues
};
WireMessage msg = new WireMessage
{
Name = Constants.WireMessagePrefix + "ExecuteRequest",
WrappedMessage = req.ToByteString()
};
using (Response webResponse = await PostRequestAsync(msg.ToByteArray(), options))
{
if (webResponse.WebResponse.StatusCode != HttpStatusCode.OK)
{
WireMessage output = WireMessage.Parser.ParseFrom(webResponse.WebResponse.GetResponseStream());
ErrorResponse res = ErrorResponse.Parser.ParseFrom(output.WrappedMessage);
throw new WebException(
string.Format(
"ExecuteRequestAsync failed! connectionId: {0}, Response code was: {1}, Response body was: {2}",
statementHandle.ConnectionId,
webResponse.WebResponse.StatusCode,
res.ToString()));
}
else
{
WireMessage output = WireMessage.Parser.ParseFrom(webResponse.WebResponse.GetResponseStream());
ExecuteResponse res = ExecuteResponse.Parser.ParseFrom(output.WrappedMessage);
return(res);
}
}
}
public void ManyRowBatchInsertTest()
{
var client = new PhoenixClient(_credentials);
string connId = GenerateRandomConnId();
RequestOptions options = RequestOptions.GetGatewayDefaultOptions();
// In gateway mode, url format will be https://<cluster dns name>.azurehdinsight.net/hbasephoenix<N>/
// Requests sent to hbasephoenix0/ will be forwarded to PQS on workernode0
options.AlternativeEndpoint = "hbasephoenix0/";
string tableName = "Persons" + connId;
OpenConnectionResponse openConnResponse = null;
CreateStatementResponse createStatementResponse = null;
try
{
// Opening connection
pbc::MapField <string, string> info = new pbc::MapField <string, string>();
openConnResponse = client.OpenConnectionRequestAsync(connId, info, options).Result;
// Syncing connection
ConnectionProperties connProperties = new ConnectionProperties
{
HasAutoCommit = true,
AutoCommit = true,
HasReadOnly = true,
ReadOnly = false,
TransactionIsolation = 0,
Catalog = "",
Schema = "",
IsDirty = true
};
client.ConnectionSyncRequestAsync(connId, connProperties, options).Wait();
// Creating statement
createStatementResponse = client.CreateStatementRequestAsync(connId, options).Result;
// Running query 1
string sql1 = "CREATE TABLE " + tableName + " (LastName varchar(255) PRIMARY KEY,FirstName varchar(255))";
ExecuteResponse execResponse1 = client.PrepareAndExecuteRequestAsync(connId, sql1, createStatementResponse.StatementId, long.MaxValue, int.MaxValue, options).Result;
// Running query 2
// Batching two sqls in a single HTTP request
pbc::RepeatedField <string> list = new pbc.RepeatedField <string>();
list.Add("UPSERT INTO " + tableName + " VALUES('d1','x1')");
list.Add("UPSERT INTO " + tableName + " VALUES('d2','x2')");
ExecuteBatchResponse execResponse2 = client.PrepareAndExecuteBatchRequestAsync(connId, createStatementResponse.StatementId, list, options).Result;
// Running query 3
string sql3 = "select count(*) from " + tableName;
ExecuteResponse execResponse3 = client.PrepareAndExecuteRequestAsync(connId, sql3, createStatementResponse.StatementId, long.MaxValue, int.MaxValue, options).Result;
long count3 = execResponse3.Results[0].FirstFrame.Rows[0].Value[0].ScalarValue.NumberValue;
Assert.AreEqual(2, count3);
// Running query 4
// Batching two sqls in a single HTTP request
// Creating statement 2
string sql4 = "UPSERT INTO " + tableName + " VALUES (?,?)";
PrepareResponse prepareResponse = client.PrepareRequestAsync(connId, sql4, int.MaxValue, options).Result;
StatementHandle statementHandle = prepareResponse.Statement;
pbc::RepeatedField <UpdateBatch> updates = new pbc.RepeatedField <UpdateBatch>();
for (int i = 3; i < 10; i++)
{
pbc::RepeatedField <TypedValue> parameterValues = new pbc.RepeatedField <TypedValue>();
TypedValue v1 = new TypedValue
{
StringValue = "d" + i,
Type = Rep.String
};
TypedValue v2 = new TypedValue
{
StringValue = "x" + i,
Type = Rep.String
};
parameterValues.Add(v1);
parameterValues.Add(v2);
UpdateBatch batch = new UpdateBatch
{
ParameterValues = parameterValues
};
updates.Add(batch);
}
ExecuteBatchResponse execResponse4 = client.ExecuteBatchRequestAsync(connId, statementHandle.Id, updates, options).Result;
// Running query 5
string sql5 = "select count(*) from " + tableName;
ExecuteResponse execResponse5 = client.PrepareAndExecuteRequestAsync(connId, sql5, createStatementResponse.StatementId, long.MaxValue, int.MaxValue, options).Result;
long count5 = execResponse5.Results[0].FirstFrame.Rows[0].Value[0].ScalarValue.NumberValue;
Assert.AreEqual(9, count5);
// Running query 5
string sql6 = "DROP TABLE " + tableName;
client.PrepareAndExecuteRequestAsync(connId, sql6, createStatementResponse.StatementId, long.MaxValue, int.MaxValue, options).Wait();
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (createStatementResponse != null)
{
client.CloseStatementRequestAsync(connId, createStatementResponse.StatementId, options).Wait();
createStatementResponse = null;
}
if (openConnResponse != null)
{
client.CloseConnectionRequestAsync(connId, options).Wait();
openConnResponse = null;
}
}
}
public void QueryManyRowTest()
{
var client = new PhoenixClient(_credentials);
string connId = GenerateRandomConnId();
RequestOptions options = RequestOptions.GetGatewayDefaultOptions();
// In gateway mode, url format will be https://<cluster dns name>.azurehdinsight.net/hbasephoenix<N>/
// Requests sent to hbasephoenix0/ will be forwarded to PQS on workernode0
options.AlternativeEndpoint = "hbasephoenix0/";
string tableName = "Persons" + connId;
OpenConnectionResponse openConnResponse = null;
CreateStatementResponse createStatementResponse = null;
try
{
// Opening connection
pbc::MapField <string, string> info = new pbc::MapField <string, string>();
openConnResponse = client.OpenConnectionRequestAsync(connId, info, options).Result;
// Syncing connection
ConnectionProperties connProperties = new ConnectionProperties
{
HasAutoCommit = true,
AutoCommit = false,
HasReadOnly = true,
ReadOnly = false,
TransactionIsolation = 0,
Catalog = "",
Schema = "",
IsDirty = true
};
client.ConnectionSyncRequestAsync(connId, connProperties, options).Wait();
createStatementResponse = client.CreateStatementRequestAsync(connId, options).Result;
// Running query 1
string sql1 = "CREATE TABLE " + tableName + " (LastName varchar(255) PRIMARY KEY,FirstName varchar(255))";
client.PrepareAndExecuteRequestAsync(connId, sql1, createStatementResponse.StatementId, long.MaxValue, int.MaxValue, options).Wait();
// Commit statement 1
client.CommitRequestAsync(connId, options).Wait();
// Creating statement 2
string sql2 = "UPSERT INTO " + tableName + " VALUES (?,?)";
PrepareResponse prepareResponse = client.PrepareRequestAsync(connId, sql2, long.MaxValue, options).Result;
StatementHandle statementHandle = prepareResponse.Statement;
// Insert 300 rows
for (int i = 0; i < 300; i++)
{
pbc::RepeatedField <TypedValue> list = new pbc.RepeatedField <TypedValue>();
TypedValue v1 = new TypedValue
{
StringValue = "d" + i,
Type = Rep.String
};
TypedValue v2 = new TypedValue
{
StringValue = "x" + i,
Type = Rep.String
};
list.Add(v1);
list.Add(v2);
ExecuteResponse executeResponse = client.ExecuteRequestAsync(statementHandle, list, long.MaxValue, true, options).Result;
}
// Commit statement 2
client.CommitRequestAsync(connId, options).Wait();
// Running query 3
string sql3 = "select * from " + tableName;
ExecuteResponse execResponse3 = client.PrepareAndExecuteRequestAsync(connId, sql3, createStatementResponse.StatementId, long.MaxValue, int.MaxValue, options).Result;
pbc::RepeatedField <Row> rows = execResponse3.Results[0].FirstFrame.Rows;
for (int i = 0; i < rows.Count; i++)
{
Row row = rows[i];
Debug.WriteLine(row.Value[0].ScalarValue.StringValue + " " + row.Value[1].ScalarValue.StringValue);
}
// 100 is hard coded in server side as default firstframe size
// In order to get remaining rows, FetchRequestAsync is used
Assert.AreEqual(100, rows.Count);
// Fetch remaining rows, offset is not used, simply set to 0
// if FetchResponse.Frame.Done = true, that means all the rows fetched
FetchResponse fetchResponse = client.FetchRequestAsync(connId, createStatementResponse.StatementId, 0, int.MaxValue, options).Result;
Assert.AreEqual(200, fetchResponse.Frame.Rows.Count);
Assert.AreEqual(true, fetchResponse.Frame.Done);
// Running query 4
string sql4 = "DROP TABLE " + tableName;
client.PrepareAndExecuteRequestAsync(connId, sql4, createStatementResponse.StatementId, long.MaxValue, int.MaxValue, options).Wait();
// Commit statement 4
client.CommitRequestAsync(connId, options).Wait();
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (createStatementResponse != null)
{
client.CloseStatementRequestAsync(connId, createStatementResponse.StatementId, options).Wait();
createStatementResponse = null;
}
if (openConnResponse != null)
{
client.CloseConnectionRequestAsync(connId, options).Wait();
openConnResponse = null;
}
}
}
/// <summary>
/// This request is used to execute a PreparedStatement, optionally with values to bind to the parameters in the Statement.
/// </summary>
public async Task<ExecuteResponse> ExecuteRequestAsync(StatementHandle statementHandle, pbc::RepeatedField<TypedValue> parameterValues, ulong firstFrameMaxSize, bool hasParameterValues, RequestOptions options)
{
ExecuteRequest req = new ExecuteRequest
{
StatementHandle = statementHandle,
ParameterValues = parameterValues,
FirstFrameMaxSize = firstFrameMaxSize,
HasParameterValues = hasParameterValues
};
WireMessage msg = new WireMessage
{
Name = Constants.WireMessagePrefix + "ExecuteRequest",
WrappedMessage = req.ToByteString()
};
using (Response webResponse = await PostRequestAsync(msg.ToByteArray(), options))
{
if (webResponse.WebResponse.StatusCode != HttpStatusCode.OK)
{
WireMessage output = WireMessage.Parser.ParseFrom(webResponse.WebResponse.GetResponseStream());
ErrorResponse res = ErrorResponse.Parser.ParseFrom(output.WrappedMessage);
throw new WebException(
string.Format(
"ExecuteRequestAsync failed! connectionId: {0}, Response code was: {1}, Response body was: {2}",
statementHandle.ConnectionId,
webResponse.WebResponse.StatusCode,
res.ToString()));
}
else
{
WireMessage output = WireMessage.Parser.ParseFrom(webResponse.WebResponse.GetResponseStream());
ExecuteResponse res = ExecuteResponse.Parser.ParseFrom(output.WrappedMessage);
return res;
}
}
}
public static async void ExecuteProcedure(DataContract dataContract)
{
string connId = Guid.NewGuid().ToString();
RequestOptions options = RequestOptions.GetGatewayDefaultOptions();
var credentials = new ClusterCredentials(new Uri("jdbc:phoenix:10.16.0.206:/hbase-unsecure"), null, null);
var client = new PhoenixClient(credentials);
// In gateway mode, PQS requests will be https://<cluster dns name>.azurehdinsight.net/hbasephoenix<N>/
// Requests sent to hbasephoenix0/ will be forwarded to PQS on workernode0
options.AlternativeEndpoint = "hbasephoenix0/";
OpenConnectionResponse openConnResponse = null;
StatementHandle statementHandle = null;
try
{
//var info = new pbc::MapField<string, string>();
// Opening connection
pbc::MapField <string, string> info = new pbc::MapField <string, string>();
openConnResponse = await client.OpenConnectionRequestAsync(connId, info, options);
// Syncing connection
ConnectionProperties connProperties = new ConnectionProperties
{
HasAutoCommit = true,
AutoCommit = true,
HasReadOnly = true,
ReadOnly = false,
TransactionIsolation = 0,
Catalog = "",
Schema = "",
IsDirty = true
};
await client.ConnectionSyncRequestAsync(connId, connProperties, options);
var createStatementResponse = await client.CreateStatementRequestAsync(connId, options);
string sql = "SELECT * FROM Customers";
ExecuteResponse executeResponse = await client.PrepareAndExecuteRequestAsync(connId, sql, createStatementResponse.StatementId, long.MaxValue, int.MaxValue, options);
pbc::RepeatedField <Row> rows = executeResponse.Results[0].FirstFrame.Rows;
// Loop through all of the returned rows and display the first two columns
for (int i = 0; i < rows.Count; i++)
{
Row row = rows[i];
Console.WriteLine(row.Value[0].ScalarValue.StringValue + " " + row.Value[1].ScalarValue.StringValue);
}
// 100 is hard-coded on the server side as the default firstframe size
// FetchRequestAsync is called to get any remaining rows
Console.WriteLine("");
Console.WriteLine($"Number of rows: {rows.Count}");
// Fetch remaining rows, offset is not used, simply set to 0
// When FetchResponse.Frame.Done is true, all rows were fetched
FetchResponse fetchResponse = await client.FetchRequestAsync(connId, createStatementResponse.StatementId, 0, int.MaxValue, options);
Console.WriteLine($"Frame row count: {fetchResponse.Frame.Rows.Count}");
Console.WriteLine($"Fetch response is done: {fetchResponse.Frame.Done}");
Console.WriteLine("");
// Running query 2
string sql2 = "select count(*) from Customers";
ExecuteResponse countResponse = await client.PrepareAndExecuteRequestAsync(connId, sql2, createStatementResponse.StatementId, long.MaxValue, int.MaxValue, options);
long count = countResponse.Results[0].FirstFrame.Rows[0].Value[0].ScalarValue.NumberValue;
Console.WriteLine($"Total customer records: {count}");
Console.WriteLine("");
// Running query 3
string sql3 = "select StateProvince, count(*) as Number from Customers group by StateProvince order by Number desc";
ExecuteResponse groupByResponse = await client.PrepareAndExecuteRequestAsync(connId, sql3, createStatementResponse.StatementId, long.MaxValue, int.MaxValue, options);
pbc::RepeatedField <Row> stateRows = groupByResponse.Results[0].FirstFrame.Rows;
for (int i = 0; i < stateRows.Count; i++)
{
Row row = stateRows[i];
Console.WriteLine(row.Value[0].ScalarValue.StringValue + ": " + row.Value[1].ScalarValue.NumberValue);
}
}
catch (Exception ex)
{
}
finally
{
if (statementHandle != null)
{
await client.CloseStatementRequestAsync(connId, statementHandle.Id, options);
statementHandle = null;
}
if (openConnResponse != null)
{
await client.CloseConnectionRequestAsync(connId, options);
openConnResponse = null;
}
}
}
private ExprHandle()
{
_statement = StatementHandle.NULL;
}
public ExprHandle(StatementHandle statement)
{
_statement = statement;
}
