private void EnsureStatement(bool generatingKeys) { // if the statement is prepared, but with a different setting for generatingKeys, re-prepare it if (isPrepared && isPreparedWithKeys != generatingKeys) { Close(); Prepare(generatingKeys); } // if the connection has been closed and reopened, the statement identified by this handle // has been closed on the server, and we must re-create it if (handle == -1 || !connection.InternalConnection.IsCommandRegistered(handle)) { if (CommandType == CommandType.StoredProcedure || parameters.Count > 0) { Prepare(generatingKeys); } else { EncodedDataStream dataStream = new RemEncodedStream(connection.InternalConnection.protocolVersion); dataStream.startMessage(Protocol.CreateStatement); connection.InternalConnection.sendAndReceive(dataStream); handle = dataStream.getInt(); connection.InternalConnection.RegisterCommand(handle); } } }
public override bool NextResult() { closeCurrentResultSet(); EncodedDataStream dataStream = new RemEncodedStream(connection.InternalConnection.protocolVersion); dataStream.startMessage(Protocol.GetMoreResults); dataStream.encodeInt(statement.handle); connection.InternalConnection.sendAndReceive(dataStream); if (dataStream.getInt() != 1) { return(false); } dataStream = new RemEncodedStream(connection.InternalConnection.protocolVersion); dataStream.startMessage(Protocol.GetResultSet); dataStream.encodeInt(statement.handle); connection.InternalConnection.sendAndReceive(dataStream); int rsHandle = dataStream.getInt(); if (rsHandle == -1) { return(false); } InitResultSet(rsHandle, dataStream, true); return(true); }
private int InvokeStoredProcedure(bool generatingKeys) { if (connection.InternalConnection.protocolVersion < Protocol.PROTOCOL_VERSION12) { throw new NuoDbSqlException(String.Format("server protocol {0} doesn't support prepareCall", connection.InternalConnection.protocolVersion)); } EncodedDataStream dataStream = new RemEncodedStream(connection.InternalConnection.protocolVersion); dataStream.startMessage(Protocol.ExecuteCallableStatement); dataStream.encodeInt(handle); putParameters(dataStream); // check how many OUT parameters we have int outParameters = 0; for (int i = 0; i < parameters.Count; i++) { DbParameter param = parameters[i]; if (param.Direction == ParameterDirection.InputOutput || param.Direction == ParameterDirection.Output) { outParameters++; } } dataStream.encodeInt(outParameters); for (int i = 0; i < parameters.Count; i++) { DbParameter param = parameters[i]; if (param.Direction == ParameterDirection.InputOutput || param.Direction == ParameterDirection.Output) { dataStream.encodeInt(i); dataStream.encodeInt(NuoDbConnectionInternal.mapDbTypeToJavaSql(param.DbType)); dataStream.encodeInt(-1); } } connection.InternalConnection.sendAndReceive(dataStream); int result = dataStream.getInt(); updateRecordsUpdated(dataStream); updateLastCommitInfo(dataStream, generatingKeys); outParameters = dataStream.getInt(); for (int i = 0; i < outParameters; i++) { int index = dataStream.getInt(); parameters[index].Value = dataStream.getValue(connection.InternalConnection.sqlContext).Object; } return(updateCount); }
private int ExecuteUpdate(bool generatingKeys) { #if DEBUG System.Diagnostics.Trace.WriteLine("NuoDbCommand.ExecuteNonQuery(" + CommandText + ")"); #endif checkConnection(); EnsureStatement(generatingKeys); if (CommandType == CommandType.StoredProcedure) { return(InvokeStoredProcedure(generatingKeys)); } EncodedDataStream dataStream = new RemEncodedStream(connection.InternalConnection.protocolVersion); if (isPrepared) { dataStream.startMessage(Protocol.ExecutePreparedUpdate); dataStream.encodeInt(handle); putParameters(dataStream); } else { if (generatingKeys) { dataStream.startMessage(Protocol.ExecuteUpdateKeys); dataStream.encodeInt(generatingKeys ? 1 : 0); } else { dataStream.startMessage(Protocol.ExecuteUpdate); } dataStream.encodeInt(handle); dataStream.encodeString(CommandText); } connection.InternalConnection.sendAndReceive(dataStream); updateRecordsUpdated(dataStream); // V2 txn ID obsolete as of V3 and no longer sending as of V7 if (connection.InternalConnection.protocolVersion >= Protocol.PROTOCOL_VERSION2 && connection.InternalConnection.protocolVersion < Protocol.PROTOCOL_VERSION7) { long txId = dataStream.getLong(); } updateLastCommitInfo(dataStream, generatingKeys); return(updateCount); }
private int ExecuteUpdate(bool generatingKeys) { #if DEBUG System.Diagnostics.Trace.WriteLine("NuoDbCommand.ExecuteNonQuery(" + CommandText + ")"); #endif checkConnection(); EnsureStatement(generatingKeys); if (CommandType == CommandType.StoredProcedure) { return InvokeStoredProcedure(generatingKeys); } EncodedDataStream dataStream = new RemEncodedStream(connection.InternalConnection.protocolVersion); if (isPrepared) { dataStream.startMessage(Protocol.ExecutePreparedUpdate); dataStream.encodeInt(handle); putParameters(dataStream); } else { if (generatingKeys) { dataStream.startMessage(Protocol.ExecuteUpdateKeys); dataStream.encodeInt(generatingKeys ? 1 : 0); } else dataStream.startMessage(Protocol.ExecuteUpdate); dataStream.encodeInt(handle); dataStream.encodeString(CommandText); } connection.InternalConnection.sendAndReceive(dataStream); updateRecordsUpdated(dataStream); // V2 txn ID obsolete as of V3 and no longer sending as of V7 if (connection.InternalConnection.protocolVersion >= Protocol.PROTOCOL_VERSION2 && connection.InternalConnection.protocolVersion < Protocol.PROTOCOL_VERSION7) { long txId = dataStream.getLong(); } updateLastCommitInfo(dataStream, generatingKeys); return updateCount; }
private void EnsureStatement(bool generatingKeys) { // if the statement is prepared, but with a different setting for generatingKeys, re-prepare it if (isPrepared && isPreparedWithKeys != generatingKeys) { Close(); Prepare(generatingKeys); } // if the connection has been closed and reopened, the statement identified by this handle // has been closed on the server, and we must re-create it if (handle == -1 || !connection.InternalConnection.IsCommandRegistered(handle)) { if (CommandType==CommandType.StoredProcedure || parameters.Count > 0) { Prepare(generatingKeys); } else { EncodedDataStream dataStream = new RemEncodedStream(connection.InternalConnection.protocolVersion); dataStream.startMessage(Protocol.CreateStatement); connection.InternalConnection.sendAndReceive(dataStream); handle = dataStream.getInt(); connection.InternalConnection.RegisterCommand(handle); } } }
protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior) { string trimmedSql = CommandText.TrimStart(null); if (CommandType == CommandType.Text && !trimmedSql.StartsWith("SELECT ", StringComparison.InvariantCultureIgnoreCase) && !trimmedSql.StartsWith("CALL ", StringComparison.InvariantCultureIgnoreCase) && !trimmedSql.StartsWith("EXECUTE ", StringComparison.InvariantCultureIgnoreCase) ) { #if DEBUG System.Diagnostics.Trace.WriteLine("The statement is not a SELECT: redirecting to ExecuteNonQuery"); #endif // If the command was already prepared, it will be prepared again, in order to enable the generatingKeys option int count = ExecuteUpdate(true); NuoDbDataReader reader = generatedKeys != null ? generatedKeys : new NuoDbDataReader(connection, -1, null, this, false); reader.UpdatedRecords = count; return reader; } #if DEBUG System.Diagnostics.Trace.WriteLine("NuoDbCommand.ExecuteDbDataReader(" + CommandText + ", " + behavior + ")"); #endif checkConnection(); EnsureStatement(false); EncodedDataStream dataStream = new RemEncodedStream(connection.InternalConnection.protocolVersion); if (CommandType == CommandType.StoredProcedure) { InvokeStoredProcedure(false); dataStream.startMessage(Protocol.GetResultSet); dataStream.encodeInt(handle); connection.InternalConnection.sendAndReceive(dataStream); return createResultSet(dataStream, true); } bool readColumnNames = true; if (isPrepared) { dataStream.startMessage(Protocol.ExecutePreparedQuery); dataStream.encodeInt(handle); putParameters(dataStream); if (connection.InternalConnection.protocolVersion >= Protocol.PROTOCOL_VERSION8) { /* if (columnNames != null) { dataStream.encodeInt(Protocol.SkipColumnNames); readColumnNames = false; } else */ { dataStream.encodeInt(Protocol.SendColumnNames); } } } else { dataStream.startMessage(Protocol.ExecuteQuery); dataStream.encodeInt(handle); dataStream.encodeString(CommandText); } connection.InternalConnection.sendAndReceive(dataStream); return createResultSet(dataStream, readColumnNames); }
private void WriteToServer(Feeder feeder) { if (this.tableName.Length == 0) { throw new ArgumentException("The name of the destination table hasn't been specified", "DestinationTableName"); } StringBuilder builder = new StringBuilder(); builder.Append("INSERT INTO `"); builder.Append(this.tableName.Replace("`", "``")); builder.Append("` "); if (mappings.Count == 0) { // the target table has the same number and names of the columns as in the specified input rows builder.Append("VALUES ("); for (int i = 0; i < feeder.FieldCount; i++) { if (i != 0) { builder.Append(", "); } builder.Append("?"); } builder.Append(")"); } else { DataRowCollection targetColumns = null; builder.Append(" ("); for (int i = 0; i < mappings.Count; i++) { NuoDbBulkLoaderColumnMapping mapping = mappings[i]; if (i != 0) { builder.Append(", "); } builder.Append("`"); if (mapping.DestinationColumn == null) { // we are requested to map to a target column that is identified with its ordinal number, so // fetch the schema of the target table to find out what is its name if (targetColumns == null) { // split the destination table into its different parts string[] parts = this.tableName.Split(new char[] { '.' }); DataTable targetSchema = this.connection.GetSchema("Columns", new string[] { null, // catalog parts.Length == 2 ? parts[0] : null, // schema parts.Length == 2 ? parts[1] : parts[0] // table }); targetColumns = targetSchema.Rows; } if (mapping.DestinationOrdinal < 0 || mapping.DestinationOrdinal > targetColumns.Count) { throw new IndexOutOfRangeException(String.Format("The specified ordinal of the target column ({0}) is outside the range of the column count ({1}) of table {2}", new object[] { mapping.DestinationOrdinal, targetColumns.Count, this.tableName })); } string columnName = (string)(targetColumns[mapping.DestinationOrdinal]["COLUMN_NAME"]); builder.Append(columnName.Replace("`", "``")); } else { builder.Append(mapping.DestinationColumn.Replace("`", "``")); } builder.Append("`"); } builder.Append(") VALUES ("); for (int i = 0; i < mappings.Count; i++) { if (i != 0) { builder.Append(", "); } builder.Append("?"); } builder.Append(")"); } string sqlString = builder.ToString(); #if DEBUG System.Diagnostics.Trace.WriteLine("NuoDbBulkLoader::WriteToServer: " + sqlString); #endif if (this.connection.State != ConnectionState.Open) { this.connection.Open(); } using (NuoDbCommand command = new NuoDbCommand(sqlString, this.connection)) { command.Prepare(); int totalSize = 0; // do the check for out-of-range values just once foreach (NuoDbBulkLoaderColumnMapping mapping in mappings) { if (mapping.SourceColumn == null && mapping.SourceOrdinal < 0 || mapping.SourceOrdinal > feeder.FieldCount) { throw new IndexOutOfRangeException(String.Format("The specified ordinal of the source column ({0}) is outside the range of the column count ({1})", new object[] { mapping.SourceOrdinal, feeder.FieldCount })); } } while (true) { EncodedDataStream dataStream = new RemEncodedStream(connection.InternalConnection.protocolVersion); dataStream.startMessage(Protocol.ExecuteBatchPreparedStatement); dataStream.encodeInt(command.handle); int batchCount = 0; for (; batchCount < this.batchSize && feeder.MoveNext(); batchCount++) { dataStream.encodeInt(command.Parameters.Count); if (mappings.Count == 0) { for (int i = 0; i < feeder.FieldCount; i++) { dataStream.encodeDotNetObject(feeder[i]); } } else { foreach (NuoDbBulkLoaderColumnMapping mapping in mappings) { if (mapping.SourceColumn == null) { dataStream.encodeDotNetObject(feeder[mapping.SourceOrdinal]); } else { dataStream.encodeDotNetObject(feeder[mapping.SourceColumn]); } } } } // the iterator hasn't found any more data to import, let's break out if (batchCount == 0) { break; } dataStream.encodeInt(-1); dataStream.encodeInt(batchCount); totalSize += batchCount; #if DEBUG System.Diagnostics.Trace.WriteLine("NuoDbBulkLoader::WriteToServer: sending a batch of " + batchCount + " rows"); #endif this.connection.InternalConnection.sendAndReceive(dataStream); bool hasErrors = false; string errorMessage = string.Empty; for (int i = 0; i < batchCount; i++) { int result = dataStream.getInt(); if (result == EXECUTE_FAILED) { if (this.connection.InternalConnection.protocolVersion >= Protocol.PROTOCOL_VERSION6) { int sqlCode = dataStream.getInt(); string message = dataStream.getString(); errorMessage = AppendError(errorMessage, message, i); } hasErrors = true; } } if (this.connection.InternalConnection.protocolVersion >= Protocol.PROTOCOL_VERSION3) { long txnId = dataStream.getLong(); int nodeId = dataStream.getInt(); long commitSequence = dataStream.getLong(); this.connection.InternalConnection.setLastTransaction(txnId, nodeId, commitSequence); } if (handlers.Count != 0) { BatchProcessedEventHandler[] tmpArray = new BatchProcessedEventHandler[handlers.Count]; handlers.CopyTo(tmpArray); BatchProcessedEventArgs args = new BatchProcessedEventArgs(); args.BatchSize = batchCount; args.TotalSize = totalSize; args.HasErrors = hasErrors; foreach (BatchProcessedEventHandler h in tmpArray) { h.Invoke(this, args); } } if (hasErrors) { throw new NuoDbSqlException(errorMessage, NuoDbSqlCode.FindError("BATCH_UPDATE_ERROR")); } } } }
private void Prepare(bool generatingKeys) { checkConnection(); Close(); StringBuilder sqlString = new StringBuilder(CommandText.Length); NuoDbDataParameterCollection newParams = new NuoDbDataParameterCollection(); int state = 0; string curParamName = ""; bool inSingleQuotes = false, inDoubleQuotes = false, inSmartQuotes = false; foreach (char c in CommandText) { if (c == '\'' && !(inDoubleQuotes || inSmartQuotes)) { inSingleQuotes = !inSingleQuotes; state = 0; sqlString.Append(c); continue; } else if (c == '\"' && !(inSingleQuotes || inSmartQuotes)) { inDoubleQuotes = !inDoubleQuotes; state = 0; sqlString.Append(c); continue; } else if (c == '`' && !(inSingleQuotes || inDoubleQuotes)) { inSmartQuotes = !inSmartQuotes; state = 0; sqlString.Append(c); continue; } if (inSingleQuotes || inDoubleQuotes || inSmartQuotes) { sqlString.Append(c); continue; } if (c == '?') { state = 1; } else if (c == '@') { state = 2; } else if (state == 1) { if (c == '.') { state = 2; } else { // either add a new parameter, or carry over the user-provided one if (parameters.Count > newParams.Count) { newParams.Add(parameters[newParams.Count]); } else { newParams.Add(new NuoDbParameter()); } state = 0; sqlString.Append("?"); sqlString.Append(c); } } else if (state == 2) { if ((c >= 'A' && c <= 'Z') || (c >= 'a' && c <= 'z') || c == '_' || (curParamName.Length > 0 && ((c >= '0' && c <= '9') || c == '-'))) { curParamName += c; } else { // if the user-provided parameters have a value for this name, preserve it if (parameters.Contains(curParamName)) { newParams.Add(parameters[curParamName]); } else if (parameters.Contains("@" + curParamName)) { newParams.Add(parameters["@" + curParamName]); } else { NuoDbParameter p = new NuoDbParameter(); p.ParameterName = curParamName; newParams.Add(p); } sqlString.Append("?."); sqlString.Append(curParamName); sqlString.Append(c); curParamName = ""; state = 0; } } else { state = 0; sqlString.Append(c); } } // handle the case where the SQL statement ended while parsing a parameter if (state == 1) { // either add a new parameter, or carry over the user-provided one if (parameters.Count > newParams.Count) { newParams.Add(parameters[newParams.Count]); } else { newParams.Add(new NuoDbParameter()); } sqlString.Append("?"); } else if (state == 2) { // if the user-provided parameters have a value for this name, preserve it if (parameters.Contains(curParamName)) { newParams.Add(parameters[curParamName]); } else if (parameters.Contains("@" + curParamName)) { newParams.Add(parameters["@" + curParamName]); } else { NuoDbParameter p = new NuoDbParameter(); p.ParameterName = curParamName; newParams.Add(p); } sqlString.Append("?."); sqlString.Append(curParamName); } string nuodbSqlString = sqlString.ToString().TrimStart(null); // if we are given just the name of the stored procedure, retrieve the number of parameters and generate the full command if (CommandType == CommandType.StoredProcedure && !nuodbSqlString.StartsWith("EXECUTE ", StringComparison.InvariantCultureIgnoreCase) && !nuodbSqlString.StartsWith("CALL ", StringComparison.InvariantCultureIgnoreCase)) { char[] quotes = new char[] { '"' }; string[] parts = nuodbSqlString.Split(new char[] { '.' }); DataTable paramTable = null; if (parts.Length == 2) { paramTable = NuoDbConnectionInternal.GetSchemaHelper(connection, "ProcedureParameters", new string[] { null, parts[0].Trim(quotes), parts[1].Trim(quotes), null }); } else { NuoDbConnectionStringBuilder builder = new NuoDbConnectionStringBuilder(connection.ConnectionString); string schema = builder.Schema; if (schema.Length == 0) { schema = "USER"; } paramTable = NuoDbConnectionInternal.GetSchemaHelper(connection, "ProcedureParameters", new string[] { null, schema, parts[0].Trim(quotes), null }); } int numParams = 0; foreach (DataRow row in paramTable.Select("PARAMETER_DIRECTION <> 3", "ORDINAL_POSITION ASC")) { int ordinal = (int)row["ORDINAL_POSITION"]; if (ordinal != ++numParams) { throw new NuoDbSqlException(String.Format("Internal error: unexpected ordering of the parameters of the procedure {0}", nuodbSqlString)); } int direction = (int)row["PARAMETER_DIRECTION"]; ParameterDirection paramDirection; switch (direction) { case 1: paramDirection = ParameterDirection.Input; break; case 2: paramDirection = ParameterDirection.InputOutput; break; case 4: paramDirection = ParameterDirection.Output; break; default: throw new NuoDbSqlException(String.Format("Internal error: unexpected parameter type for procedure {0}", nuodbSqlString)); } // either add a new parameter, or carry over the user-provided one string paramName = (string)row["PARAMETER_NAME"]; if (parameters.Contains(paramName)) { newParams.Add(parameters[paramName]); } else if (parameters.Contains("@" + paramName)) { newParams.Add(parameters["@" + paramName]); } else if (parameters.Count > newParams.Count) { if (parameters[newParams.Count].ParameterName.Length == 0) { parameters[newParams.Count].ParameterName = paramName; } newParams.Add(parameters[newParams.Count]); } else { NuoDbParameter p = new NuoDbParameter(); p.ParameterName = paramName; newParams.Add(p); } newParams[newParams.Count - 1].DbType = NuoDbConnectionInternal.mapJavaSqlToDbType((int)row["PARAMETER_DATA_TYPE"]); newParams[newParams.Count - 1].Direction = paramDirection; } StringBuilder strBuilder = new StringBuilder("EXECUTE "); strBuilder.Append(nuodbSqlString); strBuilder.Append("("); for (int i = 0; i < numParams; i++) { if (i != 0) { strBuilder.Append(","); } strBuilder.Append("?"); } strBuilder.Append(")"); nuodbSqlString = strBuilder.ToString(); } if (nuodbSqlString.StartsWith("EXECUTE ", StringComparison.InvariantCultureIgnoreCase) || nuodbSqlString.StartsWith("CALL ", StringComparison.InvariantCultureIgnoreCase)) { if (connection.InternalConnection.protocolVersion < Protocol.PROTOCOL_VERSION12) { throw new NuoDbSqlException(String.Format("server protocol {0} doesn't support prepareCall", connection.InternalConnection.protocolVersion)); } EncodedDataStream dataStream = new RemEncodedStream(connection.InternalConnection.protocolVersion); dataStream.startMessage(Protocol.PrepareCall); dataStream.encodeString(nuodbSqlString); connection.InternalConnection.sendAndReceive(dataStream); handle = dataStream.getInt(); connection.InternalConnection.RegisterCommand(handle); int numberParameters = dataStream.getInt(); for (int i = 0; i < numberParameters; i++) { int direction = dataStream.getInt(); String name = dataStream.getString(); switch (direction) { case 0: newParams[i].Direction = ParameterDirection.Input; break; case 1: newParams[i].Direction = ParameterDirection.InputOutput; break; case 2: newParams[i].Direction = ParameterDirection.Output; break; } if (newParams[i].ParameterName.Length == 0) { newParams[i].ParameterName = name; } } parameters = newParams; isPrepared = true; isPreparedWithKeys = generatingKeys; } else { EncodedDataStream dataStream = new RemEncodedStream(connection.InternalConnection.protocolVersion); if (generatingKeys) { dataStream.startMessage(Protocol.PrepareStatementKeys); dataStream.encodeInt(generatingKeys ? 1 : 0); } else { dataStream.startMessage(Protocol.PrepareStatement); } dataStream.encodeString(nuodbSqlString); connection.InternalConnection.sendAndReceive(dataStream); handle = dataStream.getInt(); connection.InternalConnection.RegisterCommand(handle); int numberParameters = dataStream.getInt(); // a prepared DDL command fails to execute if (numberParameters != 0 || nuodbSqlString.StartsWith("SELECT ", StringComparison.InvariantCultureIgnoreCase)) { parameters = newParams; isPrepared = true; isPreparedWithKeys = generatingKeys; } else { Close(); isPrepared = false; } } }
private void WriteToServer(Feeder feeder) { if (this.tableName.Length == 0) throw new ArgumentException("The name of the destination table hasn't been specified", "DestinationTableName"); StringBuilder builder = new StringBuilder(); builder.Append("INSERT INTO `"); builder.Append(this.tableName.Replace("`", "``")); builder.Append("` "); if (mappings.Count == 0) { // the target table has the same number and names of the columns as in the specified input rows builder.Append("VALUES ("); for (int i = 0; i < feeder.FieldCount; i++) { if (i != 0) builder.Append(", "); builder.Append("?"); } builder.Append(")"); } else { DataRowCollection targetColumns = null; builder.Append(" ("); for(int i=0;i<mappings.Count;i++) { NuoDbBulkLoaderColumnMapping mapping = mappings[i]; if (i != 0) builder.Append(", "); builder.Append("`"); if (mapping.DestinationColumn == null) { // we are requested to map to a target column that is identified with its ordinal number, so // fetch the schema of the target table to find out what is its name if (targetColumns == null) { // split the destination table into its different parts string[] parts = this.tableName.Split(new char[] { '.' }); DataTable targetSchema = this.connection.GetSchema("Columns", new string[] { null, // catalog parts.Length == 2 ? parts[0] : null, // schema parts.Length == 2 ? parts[1] : parts[0] // table }); targetColumns = targetSchema.Rows; } if (mapping.DestinationOrdinal < 0 || mapping.DestinationOrdinal > targetColumns.Count) throw new IndexOutOfRangeException(String.Format("The specified ordinal of the target column ({0}) is outside the range of the column count ({1}) of table {2}", new object[] { mapping.DestinationOrdinal, targetColumns.Count, this.tableName })); string columnName = (string)(targetColumns[mapping.DestinationOrdinal]["COLUMN_NAME"]); builder.Append(columnName.Replace("`", "``")); } else builder.Append(mapping.DestinationColumn.Replace("`", "``")); builder.Append("`"); } builder.Append(") VALUES ("); for (int i = 0; i < mappings.Count; i++) { if (i != 0) builder.Append(", "); builder.Append("?"); } builder.Append(")"); } string sqlString = builder.ToString(); #if DEBUG System.Diagnostics.Trace.WriteLine("NuoDbBulkLoader::WriteToServer: "+sqlString); #endif if (this.connection.State != ConnectionState.Open) this.connection.Open(); using (NuoDbCommand command = new NuoDbCommand(sqlString, this.connection)) { command.Prepare(); int totalSize = 0; // do the check for out-of-range values just once foreach (NuoDbBulkLoaderColumnMapping mapping in mappings) { if (mapping.SourceColumn == null && mapping.SourceOrdinal < 0 || mapping.SourceOrdinal > feeder.FieldCount) throw new IndexOutOfRangeException(String.Format("The specified ordinal of the source column ({0}) is outside the range of the column count ({1})", new object[] { mapping.SourceOrdinal, feeder.FieldCount })); } while (true) { EncodedDataStream dataStream = new RemEncodedStream(connection.InternalConnection.protocolVersion); dataStream.startMessage(Protocol.ExecuteBatchPreparedStatement); dataStream.encodeInt(command.handle); int batchCount = 0; for (; batchCount < this.batchSize && feeder.MoveNext(); batchCount++) { dataStream.encodeInt(command.Parameters.Count); if (mappings.Count == 0) { for (int i = 0; i < feeder.FieldCount; i++) { dataStream.encodeDotNetObject(feeder[i]); } } else { foreach (NuoDbBulkLoaderColumnMapping mapping in mappings) { if (mapping.SourceColumn == null) { dataStream.encodeDotNetObject(feeder[mapping.SourceOrdinal]); } else { dataStream.encodeDotNetObject(feeder[mapping.SourceColumn]); } } } } // the iterator hasn't found any more data to import, let's break out if (batchCount == 0) break; dataStream.encodeInt(-1); dataStream.encodeInt(batchCount); totalSize += batchCount; #if DEBUG System.Diagnostics.Trace.WriteLine("NuoDbBulkLoader::WriteToServer: sending a batch of " + batchCount + " rows"); #endif this.connection.InternalConnection.sendAndReceive(dataStream); string firstViolationString = ""; int firstViolation = 0; bool haserrors = false; for (int i = 0; i < batchCount; i++) { int result = dataStream.getInt(); if (result == EXECUTE_FAILED) { if (this.connection.InternalConnection.protocolVersion >= Protocol.PROTOCOL_VERSION6) { int status = dataStream.getInt(); string s = dataStream.getString(); // Only take the first constraint violation. if (NuoDbSqlCode.FindCode(status).SQLState.Equals("CONSTRAINT_ERROR") && firstViolation == 0) { firstViolation = status; firstViolationString = s; } } haserrors = true; } } if (this.connection.InternalConnection.protocolVersion >= Protocol.PROTOCOL_VERSION3) { long txnId = dataStream.getLong(); int nodeId = dataStream.getInt(); long commitSequence = dataStream.getLong(); this.connection.InternalConnection.setLastTransaction(txnId, nodeId, commitSequence); } if (handlers.Count != 0) { BatchProcessedEventHandler[] tmpArray = new BatchProcessedEventHandler[handlers.Count]; handlers.CopyTo(tmpArray); BatchProcessedEventArgs args = new BatchProcessedEventArgs(); args.BatchSize = batchCount; args.TotalSize = totalSize; args.HasErrors = haserrors; foreach (BatchProcessedEventHandler h in tmpArray) { h.Invoke(this, args); } } if (haserrors) { if (firstViolation == 0) { throw new NuoDbSqlException("", NuoDbSqlCode.FindError("BATCH_UPDATE_ERROR")); } else { throw new NuoDbSqlException(firstViolationString, NuoDbSqlCode.FindError("BATCH_UPDATE_ERROR")); } } } } }
protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior) { string trimmedSql = CommandText.TrimStart(null); if (CommandType == CommandType.Text && !trimmedSql.StartsWith("SELECT ", StringComparison.InvariantCultureIgnoreCase) && !trimmedSql.StartsWith("CALL ", StringComparison.InvariantCultureIgnoreCase) && !trimmedSql.StartsWith("EXECUTE ", StringComparison.InvariantCultureIgnoreCase)) { #if DEBUG System.Diagnostics.Trace.WriteLine("The statement is not a SELECT: redirecting to ExecuteNonQuery"); #endif // If the command was already prepared, it will be prepared again, in order to enable the generatingKeys option int count = ExecuteUpdate(true); NuoDbDataReader reader = generatedKeys != null ? generatedKeys : new NuoDbDataReader(connection, -1, null, this, false); reader.UpdatedRecords = count; return(reader); } #if DEBUG System.Diagnostics.Trace.WriteLine("NuoDbCommand.ExecuteDbDataReader(" + CommandText + ", " + behavior + ")"); #endif checkConnection(); EnsureStatement(false); EncodedDataStream dataStream = new RemEncodedStream(connection.InternalConnection.protocolVersion); if (CommandType == CommandType.StoredProcedure) { InvokeStoredProcedure(false); dataStream.startMessage(Protocol.GetResultSet); dataStream.encodeInt(handle); connection.InternalConnection.sendAndReceive(dataStream); return(createResultSet(dataStream, true)); } bool readColumnNames = true; if (isPrepared) { dataStream.startMessage(Protocol.ExecutePreparedQuery); dataStream.encodeInt(handle); putParameters(dataStream); if (connection.InternalConnection.protocolVersion >= Protocol.PROTOCOL_VERSION8) { /* if (columnNames != null) * { * dataStream.encodeInt(Protocol.SkipColumnNames); * readColumnNames = false; * } * else */ { dataStream.encodeInt(Protocol.SendColumnNames); } } } else { dataStream.startMessage(Protocol.ExecuteQuery); dataStream.encodeInt(handle); dataStream.encodeString(CommandText); } connection.InternalConnection.sendAndReceive(dataStream); return(createResultSet(dataStream, readColumnNames)); }
internal int ExecuteBatch(DataFeeder feed, int maxBatchSize = Int32.MaxValue) { checkConnection(); if (isPrepared) { Close(); } Prepare(false); EncodedDataStream dataStream = new RemEncodedStream(connection.InternalConnection.protocolVersion); dataStream.startMessage(Protocol.ExecuteBatchPreparedStatement); dataStream.encodeInt(handle); int batchCount = 0; while (batchCount < maxBatchSize && feed.MoveNext()) { batchCount++; dataStream.encodeInt(feed.FieldCount); for (int i = 0; i < feed.FieldCount; i++) { dataStream.encodeDotNetObject(feed[i]); } } // the iterator hasn't found any more data to import, let's break out if (batchCount > 0) { dataStream.encodeInt(-1); dataStream.encodeInt(batchCount); connection.InternalConnection.sendAndReceive(dataStream); bool hasErrors = false; string errorMessage = string.Empty; for (int i = 0; i < batchCount; i++) { int result = dataStream.getInt(); if (result < 0) { if (connection.InternalConnection.protocolVersion >= Protocol.PROTOCOL_VERSION6) { int sqlCode = dataStream.getInt(); string message = dataStream.getString(); errorMessage = AppendError(errorMessage, message, i); } hasErrors = true; } } if (connection.InternalConnection.protocolVersion >= Protocol.PROTOCOL_VERSION3) { long txnId = dataStream.getLong(); int nodeId = dataStream.getInt(); long commitSequence = dataStream.getLong(); connection.InternalConnection.setLastTransaction(txnId, nodeId, commitSequence); } if (hasErrors) { throw new NuoDbSqlException(errorMessage, NuoDbSqlCode.FindError("BATCH_UPDATE_ERROR")); } } return(batchCount); }
private void Prepare(bool generatingKeys) { checkConnection(); Close(); StringBuilder sqlString = new StringBuilder(sqlText.Length); NuoDbDataParameterCollection newParams = new NuoDbDataParameterCollection(); int state = 0; string curParamName = ""; bool inSingleQuotes = false, inDoubleQuotes = false, inSmartQuotes = false; foreach (char c in sqlText) { if (c == '\'' && !(inDoubleQuotes || inSmartQuotes)) { inSingleQuotes = !inSingleQuotes; state = 0; sqlString.Append(c); continue; } else if (c == '\"' && !(inSingleQuotes || inSmartQuotes)) { inDoubleQuotes = !inDoubleQuotes; state = 0; sqlString.Append(c); continue; } else if (c == '`' && !(inSingleQuotes || inDoubleQuotes)) { inSmartQuotes = !inSmartQuotes; state = 0; sqlString.Append(c); continue; } if (inSingleQuotes || inDoubleQuotes || inSmartQuotes) { sqlString.Append(c); continue; } if (c == '?') state = 1; else if (c == '@') state = 2; else if (state == 1) { if (c == '.') state = 2; else { // either add a new parameter, or carry over the user-provided one if (parameters.Count > newParams.Count) newParams.Add(parameters[newParams.Count]); else newParams.Add(new NuoDbParameter()); state = 0; sqlString.Append("?"); sqlString.Append(c); } } else if (state == 2) { if ((c >= 'A' && c <= 'Z') || (c >= 'a' && c <= 'z') || c == '_' || (curParamName.Length > 0 && ((c >= '0' && c <= '9') || c == '-'))) { curParamName += c; } else { // if the user-provided parameters have a value for this name, preserve it if (parameters.Contains(curParamName)) newParams.Add(parameters[curParamName]); else { NuoDbParameter p = new NuoDbParameter(); p.ParameterName = curParamName; newParams.Add(p); } sqlString.Append("?."); sqlString.Append(curParamName); sqlString.Append(c); curParamName = ""; state = 0; } } else { state = 0; sqlString.Append(c); } } // handle the case where the SQL statement ended while parsing a parameter if (state == 1) { // either add a new parameter, or carry over the user-provided one if (parameters.Count > newParams.Count) newParams.Add(parameters[newParams.Count]); else newParams.Add(new NuoDbParameter()); sqlString.Append("?"); } else if (state == 2) { // if the user-provided parameters have a value for this name, preserve it if (parameters.Contains(curParamName)) newParams.Add(parameters[curParamName]); else { NuoDbParameter p = new NuoDbParameter(); p.ParameterName = curParamName; newParams.Add(p); } sqlString.Append("?."); sqlString.Append(curParamName); } EncodedDataStream dataStream = new RemEncodedStream(connection.InternalConnection.protocolVersion); if (generatingKeys) { dataStream.startMessage(Protocol.PrepareStatementKeys); dataStream.encodeInt(generatingKeys ? 1 : 0); } else dataStream.startMessage(Protocol.PrepareStatement); dataStream.encodeString(sqlString.ToString()); connection.InternalConnection.sendAndReceive(dataStream); handle = dataStream.getInt(); connection.InternalConnection.RegisterCommand(handle); int numberParameters = dataStream.getInt(); // a prepared DDL command fails to execute if (numberParameters != 0 || CommandText.TrimStart(null).Substring(0, 6).ToUpper().Equals("SELECT")) { parameters = newParams; isPrepared = true; isPreparedWithKeys = generatingKeys; } else { Close(); isPrepared = false; } }
protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior) { if (!CommandText.TrimStart(null).Substring(0, 6).ToUpper().Equals("SELECT")) { #if DEBUG System.Diagnostics.Trace.WriteLine("The statement is not a SELECT: redirecting to ExecuteNonQuery"); #endif // If the command was already prepared, it will be prepared again, in order to enable the generatingKeys option ExecuteUpdate(true); return generatedKeys != null ? generatedKeys : new NuoDbDataReader(connection, -1, null, this, false); } #if DEBUG System.Diagnostics.Trace.WriteLine("NuoDbCommand.ExecuteDbDataReader(" + CommandText + ", " + behavior + ")"); #endif checkConnection(); EnsureStatement(false); bool readColumnNames = true; EncodedDataStream dataStream = new RemEncodedStream(connection.InternalConnection.protocolVersion); if (isPrepared) { dataStream.startMessage(Protocol.ExecutePreparedQuery); dataStream.encodeInt(handle); putParameters(dataStream); if (connection.InternalConnection.protocolVersion >= Protocol.PROTOCOL_VERSION8) { /* if (columnNames != null) { dataStream.encodeInt(Protocol.SkipColumnNames); readColumnNames = false; } else */ { dataStream.encodeInt(Protocol.SendColumnNames); } } } else { dataStream.startMessage(Protocol.ExecuteQuery); dataStream.encodeInt(handle); dataStream.encodeString(sqlText); } connection.InternalConnection.sendAndReceive(dataStream); return createResultSet(dataStream, readColumnNames); }
private int InvokeStoredProcedure(bool generatingKeys) { if (connection.InternalConnection.protocolVersion < Protocol.PROTOCOL_VERSION12) throw new NuoDbSqlException(String.Format("server protocol {0} doesn't support prepareCall", connection.InternalConnection.protocolVersion)); EncodedDataStream dataStream = new RemEncodedStream(connection.InternalConnection.protocolVersion); dataStream.startMessage(Protocol.ExecuteCallableStatement); dataStream.encodeInt(handle); putParameters(dataStream); // check how many OUT parameters we have int outParameters = 0; for (int i = 0; i < parameters.Count; i++) { DbParameter param = parameters[i]; if (param.Direction == ParameterDirection.InputOutput || param.Direction == ParameterDirection.Output) outParameters++; } dataStream.encodeInt(outParameters); for (int i = 0; i < parameters.Count; i++) { DbParameter param = parameters[i]; if (param.Direction == ParameterDirection.InputOutput || param.Direction == ParameterDirection.Output) { dataStream.encodeInt(i); dataStream.encodeInt(NuoDbConnectionInternal.mapDbTypeToJavaSql(param.DbType)); dataStream.encodeInt(-1); } } connection.InternalConnection.sendAndReceive(dataStream); int result = dataStream.getInt(); updateRecordsUpdated(dataStream); updateLastCommitInfo(dataStream, generatingKeys); outParameters = dataStream.getInt(); for (int i = 0; i < outParameters; i++) { int index = dataStream.getInt(); parameters[index].Value = dataStream.getValue(connection.InternalConnection.sqlContext).Object; } return updateCount; }
private void Prepare(bool generatingKeys) { checkConnection(); Close(); StringBuilder sqlString = new StringBuilder(CommandText.Length); NuoDbDataParameterCollection newParams = new NuoDbDataParameterCollection(); int state = 0; string curParamName = ""; bool inSingleQuotes = false, inDoubleQuotes = false, inSmartQuotes = false; foreach (char c in CommandText) { if (c == '\'' && !(inDoubleQuotes || inSmartQuotes)) { inSingleQuotes = !inSingleQuotes; state = 0; sqlString.Append(c); continue; } else if (c == '\"' && !(inSingleQuotes || inSmartQuotes)) { inDoubleQuotes = !inDoubleQuotes; state = 0; sqlString.Append(c); continue; } else if (c == '`' && !(inSingleQuotes || inDoubleQuotes)) { inSmartQuotes = !inSmartQuotes; state = 0; sqlString.Append(c); continue; } if (inSingleQuotes || inDoubleQuotes || inSmartQuotes) { sqlString.Append(c); continue; } if (c == '?') state = 1; else if (c == '@') state = 2; else if (state == 1) { if (c == '.') state = 2; else { // either add a new parameter, or carry over the user-provided one if (parameters.Count > newParams.Count) newParams.Add(parameters[newParams.Count]); else newParams.Add(new NuoDbParameter()); state = 0; sqlString.Append("?"); sqlString.Append(c); } } else if (state == 2) { if ((c >= 'A' && c <= 'Z') || (c >= 'a' && c <= 'z') || c == '_' || (curParamName.Length > 0 && ((c >= '0' && c <= '9') || c == '-'))) { curParamName += c; } else { // if the user-provided parameters have a value for this name, preserve it if (parameters.Contains(curParamName)) newParams.Add(parameters[curParamName]); else if (parameters.Contains("@"+curParamName)) newParams.Add(parameters["@" + curParamName]); else { NuoDbParameter p = new NuoDbParameter(); p.ParameterName = curParamName; newParams.Add(p); } sqlString.Append("?."); sqlString.Append(curParamName); sqlString.Append(c); curParamName = ""; state = 0; } } else { state = 0; sqlString.Append(c); } } // handle the case where the SQL statement ended while parsing a parameter if (state == 1) { // either add a new parameter, or carry over the user-provided one if (parameters.Count > newParams.Count) newParams.Add(parameters[newParams.Count]); else newParams.Add(new NuoDbParameter()); sqlString.Append("?"); } else if (state == 2) { // if the user-provided parameters have a value for this name, preserve it if (parameters.Contains(curParamName)) newParams.Add(parameters[curParamName]); else if (parameters.Contains("@" + curParamName)) newParams.Add(parameters["@" + curParamName]); else { NuoDbParameter p = new NuoDbParameter(); p.ParameterName = curParamName; newParams.Add(p); } sqlString.Append("?."); sqlString.Append(curParamName); } string nuodbSqlString = sqlString.ToString().TrimStart(null); // if we are given just the name of the stored procedure, retrieve the number of parameters and generate the full command if(CommandType == CommandType.StoredProcedure && !nuodbSqlString.StartsWith("EXECUTE ", StringComparison.InvariantCultureIgnoreCase) && !nuodbSqlString.StartsWith("CALL ", StringComparison.InvariantCultureIgnoreCase)) { char[] quotes = new char[] { '"' }; string[] parts = nuodbSqlString.Split(new char[] { '.' }); DataTable paramTable = null; if (parts.Length == 2) paramTable = NuoDbConnectionInternal.GetSchemaHelper(connection, "ProcedureParameters", new string[] { null, parts[0].Trim(quotes), parts[1].Trim(quotes), null }); else { NuoDbConnectionStringBuilder builder = new NuoDbConnectionStringBuilder(connection.ConnectionString); string schema = builder.Schema; if(schema.Length==0) schema = "USER"; paramTable = NuoDbConnectionInternal.GetSchemaHelper(connection, "ProcedureParameters", new string[] { null, schema, parts[0].Trim(quotes), null }); } int numParams = 0; foreach (DataRow row in paramTable.Select("PARAMETER_DIRECTION <> 3", "ORDINAL_POSITION ASC")) { int ordinal = row.Field<int>("ORDINAL_POSITION"); if (ordinal != ++numParams) throw new NuoDbSqlException(String.Format("Internal error: unexpected ordering of the parameters of the procedure {0}", nuodbSqlString)); int direction = row.Field<int>("PARAMETER_DIRECTION"); ParameterDirection paramDirection; switch (direction) { case 1: paramDirection = ParameterDirection.Input; break; case 2: paramDirection = ParameterDirection.InputOutput; break; case 4: paramDirection = ParameterDirection.Output; break; default: throw new NuoDbSqlException(String.Format("Internal error: unexpected parameter type for procedure {0}", nuodbSqlString)); } // either add a new parameter, or carry over the user-provided one string paramName = row.Field<string>("PARAMETER_NAME"); if (parameters.Contains(paramName)) newParams.Add(parameters[paramName]); else if (parameters.Contains("@" + paramName)) newParams.Add(parameters["@" + paramName]); else if (parameters.Count > newParams.Count) { if (parameters[newParams.Count].ParameterName.Length == 0) parameters[newParams.Count].ParameterName = paramName; newParams.Add(parameters[newParams.Count]); } else { NuoDbParameter p = new NuoDbParameter(); p.ParameterName = paramName; newParams.Add(p); } newParams[newParams.Count - 1].DbType = NuoDbConnectionInternal.mapJavaSqlToDbType(row.Field<int>("PARAMETER_DATA_TYPE")); newParams[newParams.Count - 1].Direction = paramDirection; } StringBuilder strBuilder = new StringBuilder("EXECUTE "); strBuilder.Append(nuodbSqlString); strBuilder.Append("("); for (int i = 0; i < numParams; i++) { if (i != 0) strBuilder.Append(","); strBuilder.Append("?"); } strBuilder.Append(")"); nuodbSqlString = strBuilder.ToString(); } if (nuodbSqlString.StartsWith("EXECUTE ", StringComparison.InvariantCultureIgnoreCase) || nuodbSqlString.StartsWith("CALL ", StringComparison.InvariantCultureIgnoreCase)) { if(connection.InternalConnection.protocolVersion < Protocol.PROTOCOL_VERSION12) throw new NuoDbSqlException(String.Format("server protocol {0} doesn't support prepareCall", connection.InternalConnection.protocolVersion)); EncodedDataStream dataStream = new RemEncodedStream(connection.InternalConnection.protocolVersion); dataStream.startMessage(Protocol.PrepareCall); dataStream.encodeString(nuodbSqlString); connection.InternalConnection.sendAndReceive(dataStream); handle = dataStream.getInt(); connection.InternalConnection.RegisterCommand(handle); int numberParameters = dataStream.getInt(); for (int i = 0; i < numberParameters; i++) { int direction = dataStream.getInt(); String name = dataStream.getString(); switch(direction) { case 0: newParams[i].Direction = ParameterDirection.Input; break; case 1: newParams[i].Direction = ParameterDirection.InputOutput; break; case 2: newParams[i].Direction = ParameterDirection.Output; break; } if(newParams[i].ParameterName.Length == 0) newParams[i].ParameterName = name; } parameters = newParams; isPrepared = true; isPreparedWithKeys = generatingKeys; } else { EncodedDataStream dataStream = new RemEncodedStream(connection.InternalConnection.protocolVersion); if (generatingKeys) { dataStream.startMessage(Protocol.PrepareStatementKeys); dataStream.encodeInt(generatingKeys ? 1 : 0); } else dataStream.startMessage(Protocol.PrepareStatement); dataStream.encodeString(nuodbSqlString); connection.InternalConnection.sendAndReceive(dataStream); handle = dataStream.getInt(); connection.InternalConnection.RegisterCommand(handle); int numberParameters = dataStream.getInt(); // a prepared DDL command fails to execute if (numberParameters != 0 || nuodbSqlString.StartsWith("SELECT ", StringComparison.InvariantCultureIgnoreCase)) { parameters = newParams; isPrepared = true; isPreparedWithKeys = generatingKeys; } else { Close(); isPrepared = false; } } }
internal int ExecuteBatch(DataFeeder feed, int maxBatchSize = Int32.MaxValue) { checkConnection(); if (isPrepared) { Close(); } Prepare(false); EncodedDataStream dataStream = new RemEncodedStream(connection.InternalConnection.protocolVersion); dataStream.startMessage(Protocol.ExecuteBatchPreparedStatement); dataStream.encodeInt(handle); int batchCount = 0; while (batchCount < maxBatchSize && feed.MoveNext()) { batchCount++; dataStream.encodeInt(feed.FieldCount); for (int i = 0; i < feed.FieldCount; i++) { dataStream.encodeDotNetObject(feed[i]); } } // the iterator hasn't found any more data to import, let's break out if (batchCount > 0) { dataStream.encodeInt(-1); dataStream.encodeInt(batchCount); connection.InternalConnection.sendAndReceive(dataStream); bool hasErrors = false; string errorMessage = string.Empty; for (int i = 0; i < batchCount; i++) { int result = dataStream.getInt(); if (result < 0) { if (connection.InternalConnection.protocolVersion >= Protocol.PROTOCOL_VERSION6) { int sqlCode = dataStream.getInt(); string message = dataStream.getString(); errorMessage = AppendError(errorMessage, message, i); } hasErrors = true; } } if (connection.InternalConnection.protocolVersion >= Protocol.PROTOCOL_VERSION3) { long txnId = dataStream.getLong(); int nodeId = dataStream.getInt(); long commitSequence = dataStream.getLong(); connection.InternalConnection.setLastTransaction(txnId, nodeId, commitSequence); } if (hasErrors) throw new NuoDbSqlException(errorMessage, NuoDbSqlCode.FindError("BATCH_UPDATE_ERROR")); } return batchCount; }