Skip to content

Commit 5bced82

Browse files
MarkMpnMarkMpn
committed
Error handling
1 parent bfa0fca commit 5bced82

6 files changed

Lines changed: 260 additions & 63 deletions

File tree

‎MarkMpn.Sql4Cds.Engine.Tests/MarkMpn.Sql4Cds.Engine.Tests.csproj‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -135,6 +135,9 @@
135135
<Content Include="Resources\OPENROWSET_TabNewline.csv">
136136
<CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
137137
</Content>
138+
<Content Include="Resources\OPENROWSET_Errors.csv">
139+
<CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
140+
</Content>
138141
</ItemGroup>
139142
<ItemGroup>
140143
<PackageReference Include="Dapper.StrongName">

‎MarkMpn.Sql4Cds.Engine.Tests/OpenRowsetBulkTests.cs‎

Lines changed: 135 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -426,5 +426,140 @@ Icon varchar(100)
426426
}
427427
}
428428
}
429+
430+
[TestMethod]
431+
public void ReadCsvSkipsErrors()
432+
{
433+
var path = Path.Combine(Path.GetDirectoryName(GetType().Assembly.Location), "Resources", "OPENROWSET_Errors.csv");
434+
using (var con = new Sql4CdsConnection(_localDataSources))
435+
using (var cmd = con.CreateCommand())
436+
{
437+
cmd.CommandText = $@"
438+
SELECT * FROM OPENROWSET(BULK '{path}', FORMAT='CSV', FIRSTROW=2)
439+
WITH (
440+
Name varchar(100),
441+
Latitude float,
442+
Longitude float,
443+
Address varchar(max),
444+
Icon varchar(100)
445+
) AS t";
446+
using (var reader = cmd.ExecuteReader())
447+
{
448+
// Check column names
449+
Assert.AreEqual("Name", reader.GetName(0));
450+
Assert.AreEqual("Latitude", reader.GetName(1));
451+
Assert.AreEqual("Longitude", reader.GetName(2));
452+
Assert.AreEqual("Address", reader.GetName(3));
453+
Assert.AreEqual("Icon", reader.GetName(4));
454+
455+
// Both data rows should be skipped due to default MAXERRORS=10
456+
Assert.IsFalse(reader.Read());
457+
}
458+
}
459+
}
460+
461+
[TestMethod]
462+
public void MaxErrors()
463+
{
464+
var path = Path.Combine(Path.GetDirectoryName(GetType().Assembly.Location), "Resources", "OPENROWSET_Errors.csv");
465+
using (var con = new Sql4CdsConnection(_localDataSources))
466+
using (var cmd = con.CreateCommand())
467+
{
468+
cmd.CommandText = $@"
469+
SELECT * FROM OPENROWSET(BULK '{path}', FORMAT='CSV', FIRSTROW=2, MAXERRORS=1)
470+
WITH (
471+
Name varchar(100),
472+
Latitude float,
473+
Longitude float,
474+
Address varchar(max),
475+
Icon varchar(100)
476+
) AS t";
477+
using (var reader = cmd.ExecuteReader())
478+
{
479+
// Check column names
480+
Assert.AreEqual("Name", reader.GetName(0));
481+
Assert.AreEqual("Latitude", reader.GetName(1));
482+
Assert.AreEqual("Longitude", reader.GetName(2));
483+
Assert.AreEqual("Address", reader.GetName(3));
484+
Assert.AreEqual("Icon", reader.GetName(4));
485+
486+
var ex = Assert.Throws<Sql4CdsException>(() => reader.Read());
487+
Assert.AreEqual(4865, ex.Number);
488+
}
489+
}
490+
}
491+
492+
[TestMethod]
493+
public void LogErrors()
494+
{
495+
var path = Path.Combine(Path.GetDirectoryName(GetType().Assembly.Location), "Resources", "OPENROWSET_Errors.csv");
496+
var errorFile = Path.Combine(Path.GetDirectoryName(GetType().Assembly.Location), "Resources", "OPENROWSET_Errors.Errors.csv");
497+
var errorLog = Path.Combine(Path.GetDirectoryName(GetType().Assembly.Location), "Resources", "OPENROWSET_Errors.Errors.ERROR.txt");
498+
499+
if (File.Exists(errorFile))
500+
File.Delete(errorFile);
501+
502+
if (File.Exists(errorLog))
503+
File.Delete(errorLog);
504+
505+
try
506+
{
507+
using (var con = new Sql4CdsConnection(_localDataSources))
508+
using (var cmd = con.CreateCommand())
509+
{
510+
cmd.CommandText = $@"
511+
SELECT * FROM OPENROWSET(BULK '{path}', FORMAT='CSV', FIRSTROW=2, ERRORFILE='{errorFile}')
512+
WITH (
513+
Name varchar(100),
514+
Latitude float,
515+
Longitude float,
516+
Address varchar(max),
517+
Icon varchar(100)
518+
) AS t";
519+
using (var reader = cmd.ExecuteReader())
520+
{
521+
// Check column names
522+
Assert.AreEqual("Name", reader.GetName(0));
523+
Assert.AreEqual("Latitude", reader.GetName(1));
524+
Assert.AreEqual("Longitude", reader.GetName(2));
525+
Assert.AreEqual("Address", reader.GetName(3));
526+
Assert.AreEqual("Icon", reader.GetName(4));
527+
528+
// Both data rows should be skipped due to default MAXERRORS=10
529+
Assert.IsFalse(reader.Read());
530+
}
531+
}
532+
533+
// Error file should contain the same rows as the input file except for the header line
534+
using (var reader = new StreamReader(path))
535+
{
536+
// Skip the first line
537+
reader.ReadLine();
538+
539+
Assert.AreEqual(reader.ReadToEnd(), File.ReadAllText(errorFile));
540+
}
541+
542+
// Error log should contain two lines with the source line numbers and error details
543+
using (var reader = new StreamReader(errorLog))
544+
{
545+
var line = reader.ReadLine();
546+
Assert.AreEqual($"File: {path}, Row: 2, Error: Error converting data type nvarchar to float.", line);
547+
548+
line = reader.ReadLine();
549+
Assert.AreEqual($"File: {path}, Row: 3, Error: Error converting data type nvarchar to float.", line);
550+
551+
line = reader.ReadLine();
552+
Assert.IsNull(line);
553+
}
554+
}
555+
finally
556+
{
557+
if (File.Exists(errorFile))
558+
File.Delete(errorFile);
559+
560+
if (File.Exists(errorLog))
561+
File.Delete(errorLog);
562+
}
563+
}
429564
}
430565
}

‎MarkMpn.Sql4Cds.Engine.Tests/Resources/OPENROWSET_Errors.csv‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
Name,Latitude,Longitude,Address,Icon
2+
Empire State Building,40.748817a,-73.985428,"20 W 34th St, New York, NY 10118","\icons\sol.png"
3+
Statue of Liberty,40.689247b,-74.044502,"Liberty Island, New York, NY 10004","\icons\sol.png"

‎MarkMpn.Sql4Cds.Engine/Ado/Sql4CdsError.cs‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -981,6 +981,16 @@ internal static Sql4CdsError OpenRowsetBulkFileDoesNotExistOrOpen(string filenam
981981
return Create(13822, null, Collation.USEnglish.ToSqlString(filename));
982982
}
983983

984+
internal static Sql4CdsError OpenRowsetBulkMaxErrorsReached(int maxErrors)
985+
{
986+
return Create(4865, null, (SqlInt32)maxErrors);
987+
}
988+
989+
internal static Sql4CdsError OpenRowsetBulkErrorWritingFile(string filename, string error)
990+
{
991+
return Create(4870, null, Collation.USEnglish.ToSqlString(filename), Collation.USEnglish.ToSqlString(error));
992+
}
993+
984994
private static string GetTypeName(DataTypeReference type)
985995
{
986996
if (type is SqlDataTypeReference sqlType)

‎MarkMpn.Sql4Cds.Engine/ExecutionPlan/OpenRowsetBulkNode.cs‎

Lines changed: 84 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@ class OpenRowsetBulkNode : BaseDataNode
3737
/// </summary>
3838
[Category("OpenRowset")]
3939
[Description("The format of the file to load")]
40-
public string Format { get; set; }
40+
public string DataFileFormat { get; set; }
4141

4242
/// <summary>
4343
/// Indicates if the file is to be read as a single value
@@ -101,6 +101,20 @@ class OpenRowsetBulkNode : BaseDataNode
101101
[Description("Specifies a character that is used to escape the quote character in the CSV file")]
102102
public string EscapeChar { get; set; } = "\"";
103103

104+
/// <summary>
105+
/// Specifies the maximum number of syntax errors or nonconforming rows, which can occur before OPENROWSET throws an exception
106+
/// </summary>
107+
[Category("OpenRowset")]
108+
[Description("Specifies the maximum number of syntax errors or nonconforming rows, which can occur before OPENROWSET throws an exception")]
109+
public int MaxErrors { get; set; } = 10;
110+
111+
/// <summary>
112+
/// Specifies the file used to collect rows that have formatting errors
113+
/// </summary>
114+
[Category("OpenRowset")]
115+
[Description("Specifies the file used to collect rows that have formatting errors")]
116+
public string ErrorFile { get; set; }
117+
104118
public override void AddRequiredColumns(NodeCompilationContext context, IList<string> requiredColumns)
105119
{
106120
}
@@ -207,42 +221,67 @@ protected override IEnumerable<Entity> ExecuteInternal(NodeExecutionContext cont
207221
csvConfig.Quote = FieldQuote[0];
208222
csvConfig.Escape = EscapeChar[0];
209223

210-
foreach (var filename in EnumerateFiles(dir, file))
224+
var errors = 0;
225+
226+
using (var errorsWriter = OpenWriter(ErrorFile))
227+
using (var logWriter = OpenWriter(String.IsNullOrEmpty(ErrorFile) ? ErrorFile : Path.ChangeExtension(ErrorFile, ".ERROR.txt")))
211228
{
212-
using (var reader = new StreamReader(OpenFile(filename), GetEncoding()))
213-
using (var csv = new CsvReader(reader, csvConfig))
229+
foreach (var filename in EnumerateFiles(dir, file))
214230
{
215-
var rowNum = 0;
216-
217-
while (csv.Read())
231+
using (var reader = new StreamReader(OpenFile(filename), GetEncoding()))
232+
using (var csv = new CsvParser(reader, csvConfig))
218233
{
219-
rowNum++;
234+
var rowNum = 0;
220235

221-
if (rowNum < FirstRow)
222-
continue;
236+
while (csv.Read())
237+
{
238+
rowNum++;
223239

224-
if (LastRow > 0 && rowNum > LastRow)
225-
break;
240+
if (rowNum < FirstRow)
241+
continue;
226242

227-
var record = new Entity();
243+
if (LastRow > 0 && rowNum > LastRow)
244+
break;
228245

229-
for (var i = 0; i < Schema.Count; i++)
230-
{
231-
var col = Schema[i];
232-
var escapedName = col.ColumnIdentifier.Value.EscapeIdentifier();
233-
var value = csv.GetField(i);
246+
var record = new Entity();
247+
var valid = true;
234248

235-
// TODO: If we're using the RAW code page and loading into a string column, we need to
236-
// re-interpret the bytes we've loaded into the string using the encoding for this column.
249+
try
250+
{
251+
for (var i = 0; i < Schema.Count; i++)
252+
{
253+
var col = Schema[i];
254+
var escapedName = col.ColumnIdentifier.Value.EscapeIdentifier();
255+
var value = csv[i];
237256

238-
// Convert the value to a SqlString, then to the target type
239-
var sqlValue = SqlTypeConverter.NetToSqlType(context.PrimaryDataSource, value, nvarcharmax);
240-
sqlValue = SqlTypeConverter.GetConversion(nvarcharmax, col.DataType)(sqlValue, eec);
257+
// TODO: If we're using the RAW code page and loading into a string column, we need to
258+
// re-interpret the bytes we've loaded into the string using the encoding for this column.
241259

242-
record[escapedAlias + "." + escapedName] = sqlValue;
243-
}
260+
// Convert the value to a SqlString, then to the target type
261+
var sqlValue = SqlTypeConverter.NetToSqlType(context.PrimaryDataSource, value, nvarcharmax);
262+
sqlValue = SqlTypeConverter.GetConversion(nvarcharmax, col.DataType)(sqlValue, eec);
263+
264+
record[escapedAlias + "." + escapedName] = sqlValue;
265+
}
266+
}
267+
catch (Exception ex)
268+
{
269+
errors++;
270+
valid = false;
244271

245-
yield return record;
272+
if (errorsWriter != null)
273+
errorsWriter.Write(csv.RawRecord);
274+
275+
if (logWriter != null)
276+
logWriter.WriteLine($"File: {filename}, Row: {rowNum}, Error: {ex.Message}");
277+
278+
if (errors == MaxErrors)
279+
throw new QueryExecutionException(Sql4CdsError.OpenRowsetBulkMaxErrorsReached(MaxErrors), ex);
280+
}
281+
282+
if (valid)
283+
yield return record;
284+
}
246285
}
247286
}
248287
}
@@ -284,6 +323,22 @@ private Stream OpenFile(string filename)
284323
}
285324
}
286325

326+
private StreamWriter OpenWriter(string filename)
327+
{
328+
if (String.IsNullOrEmpty(filename))
329+
return null;
330+
331+
try
332+
{
333+
var stream = File.Open(filename, FileMode.CreateNew, FileAccess.Write);
334+
return new StreamWriter(stream, Encoding.UTF8);
335+
}
336+
catch (Exception ex)
337+
{
338+
throw new QueryExecutionException(Sql4CdsError.OpenRowsetBulkErrorWritingFile(filename, ex.Message), ex);
339+
}
340+
}
341+
287342
private Encoding GetEncoding()
288343
{
289344
switch (CodePage)
@@ -317,7 +372,7 @@ public override object Clone()
317372
var clone = new OpenRowsetBulkNode();
318373
clone.Filename = Filename;
319374
clone.Alias = Alias;
320-
clone.Format = Format;
375+
clone.DataFileFormat = DataFileFormat;
321376
clone.SingleOption = SingleOption;
322377
clone.Schema = Schema;
323378
clone.FirstRow = FirstRow;
@@ -327,6 +382,8 @@ public override object Clone()
327382
clone.FieldTerminator = FieldTerminator;
328383
clone.FieldQuote = FieldQuote;
329384
clone.EscapeChar = EscapeChar;
385+
clone.MaxErrors = MaxErrors;
386+
clone.ErrorFile = ErrorFile;
330387
return clone;
331388
}
332389
}

0 commit comments

Comments
 (0)