public static void Main()
{
SQLRConnection con = new SQLRConnection("sqlrserver", 9000, "/tmp/example.socket", "user", "password", 0, 1);
SQLRCursor cur = new SQLRCursor(con);
cur.prepareQuery("select * from mytable $(whereclause)")
cur.substitution("whereclause", "where stringcol=:stringval and integercol>:integerval and floatcol>floatval");
cur.inputBind("stringval", "true");
cur.inputBind("integerval", 10);
cur.inputBind("floatval", 1.1, 2, 1);
cur.executeQuery();
public static void Main()
{
SQLRConnection con = new SQLRConnection("sqlrserver", 9000, "/tmp/example.socket", "user", "password", 0, 1);
SQLRCursor cur = new SQLRCursor(con);
cur.prepareQuery("select * from mytable where mycolumn>:value");
cur.inputBind("value", 1);
cur.executeQuery();
public static void Main()
{
SQLRConnection con = new SQLRConnection("sqlrserver", 9000, "/tmp/example.socket", "user", "password", 0, 1);
SQLRCursor cur = new SQLRCursor(con);
cur.prepareQuery("begin :result1:=addTwoIntegers(:integer1,:integer2); :result2=addTwoFloats(:float1,:float2); :result3=convertToString(:integer3); end;");
cur.inputBind("integer1", 10);
cur.inputBind("integer2", 20);
cur.inputBind("float1", 1.1, 2, 1);
cur.inputBind("float2", 2.2, 2, 1);
cur.inputBind("integer3", 30);
cur.defineOutputBindInteger("result1");
cur.defineOutputBindDouble("result2");
cur.defineOutputBindString("result3", 100);
cur.executeQuery();
Int64 result1=cur.getOutputBindInteger("result1");
Double result2=cur.getOutputBindDouble("result2");
String result3=cur.getOutputBindString("result3");
con.endSession();
public static void Main()
{
SQLRConnection con = new SQLRConnection("sqlrserver", 9000, "/tmp/example.socket", "user", "password", 0, 1);
SQLRCursor cursor1 = new SQLRCursor(con);
SQLRCursor cursor2 = new SQLRCursor(con);
cursor1.setResultSetBufferSize(10);
cursor1.sendQuery("select * from my_huge_table");
UInt64 index = 0;
while (!cursor1.endOfResultSet())
{
cursor2.prepareQuery("insert into my_other_table values (:1,:2,:3)");
cursor2.inputBind("1", cursor1.getField(index, 1));
cursor2.inputBind("2", cursor1.getField(index, 2));
cursor2.inputBind("3", cursor1.getField(index, 3));
cursor2.executeQuery();
}
}
private void bindParameters()
{
_sqlrcur.clearBinds();
for (Int32 i = 0; i < Parameters.Count; i++)
{
SQLRelayParameter param = (SQLRelayParameter)Parameters[i];
if (param.Direction == ParameterDirection.Input)
{
if (param.IsNull)
{
_sqlrcur.inputBind(param.ParameterName, null);
continue;
}
switch (param.SQLRelayType)
{
case SQLRelayType.Clob:
_sqlrcur.inputBindClob(param.ParameterName, Convert.ToString(param.Value), (UInt32)Convert.ToString(param.Value).Length);
continue;
case SQLRelayType.Blob:
_sqlrcur.inputBindBlob(param.ParameterName, (Byte[])param.Value, (UInt32)((Byte[])param.Value).Length);
continue;
case SQLRelayType.Cursor:
throw new NotSupportedException();
}
switch (param.DbType)
{
case DbType.AnsiString:
case DbType.AnsiStringFixedLength:
case DbType.String:
case DbType.StringFixedLength:
case DbType.Time:
case DbType.Guid:
_sqlrcur.inputBind(param.ParameterName, Convert.ToString(param.Value));
continue;
case DbType.Date:
case DbType.DateTime:
case DbType.DateTime2:
case DbType.DateTimeOffset:
DateTime dt = Convert.ToDateTime(param.Value);
_sqlrcur.inputBind(param.ParameterName, Convert.ToInt16(dt.Year), Convert.ToInt16(dt.Month), Convert.ToInt16(dt.Day), Convert.ToInt16(dt.Hour), Convert.ToInt16(dt.Minute), Convert.ToInt16(dt.Second), Convert.ToInt16(dt.Millisecond) * 1000, null, false);
continue;
case DbType.Binary:
_sqlrcur.inputBindBlob(param.ParameterName, (Byte[])param.Value, (UInt32)((Byte[])param.Value).Length);
continue;
case DbType.Boolean:
_sqlrcur.inputBind(param.ParameterName, (Convert.ToBoolean(param.Value) == true) ? 1 : 0);
continue;
case DbType.Currency:
case DbType.Decimal:
case DbType.Single:
case DbType.Double:
case DbType.VarNumeric:
_sqlrcur.inputBind(param.ParameterName, Convert.ToDouble(param.Value), 0, 0);
continue;
case DbType.Byte:
case DbType.Int16:
case DbType.Int32:
case DbType.Int64:
case DbType.SByte:
case DbType.UInt16:
case DbType.UInt32:
case DbType.UInt64:
_sqlrcur.inputBind(param.ParameterName, Convert.ToInt64(param.Value));
continue;
case DbType.Object:
case DbType.Xml:
_sqlrcur.inputBind(param.ParameterName, Convert.ToString(param.Value));
continue;
}
}
else if (param.Direction == ParameterDirection.Output)
{
switch (param.SQLRelayType)
{
case SQLRelayType.Clob:
_sqlrcur.defineOutputBindClob(param.ParameterName);
continue;
case SQLRelayType.Blob:
_sqlrcur.defineOutputBindBlob(param.ParameterName);
continue;
case SQLRelayType.Cursor:
_sqlrcur.defineOutputBindCursor(param.ParameterName);
continue;
}
switch (param.DbType)
{
case DbType.AnsiString:
case DbType.AnsiStringFixedLength:
case DbType.String:
case DbType.StringFixedLength:
case DbType.Time:
case DbType.Guid:
_sqlrcur.defineOutputBindString(param.ParameterName, param.Size);
continue;
case DbType.Date:
case DbType.DateTime:
case DbType.DateTime2:
case DbType.DateTimeOffset:
_sqlrcur.defineOutputBindDate(param.ParameterName);
continue;
case DbType.Binary:
_sqlrcur.defineOutputBindBlob(param.ParameterName);
continue;
case DbType.Boolean:
_sqlrcur.defineOutputBindInteger(param.ParameterName);
continue;
case DbType.Currency:
case DbType.Decimal:
case DbType.Single:
case DbType.Double:
case DbType.VarNumeric:
_sqlrcur.defineOutputBindDouble(param.ParameterName);
continue;
case DbType.Byte:
case DbType.Int16:
case DbType.Int32:
case DbType.Int64:
case DbType.SByte:
case DbType.UInt16:
case DbType.UInt32:
case DbType.UInt64:
_sqlrcur.defineOutputBindInteger(param.ParameterName);
continue;
case DbType.Object:
case DbType.Xml:
_sqlrcur.defineOutputBindString(param.ParameterName, param.Size);
continue;
}
}
else if (param.Direction == ParameterDirection.InputOutput)
{
// FIXME: SQL Relay doesn't currently support in/out parameters
throw new NotSupportedException();
}
}
}
