Description
Hello,
I have an issue adding several rows into a database.
when I execute the following code:
string sql2 = "INSERT INTO dbo.Storage " +
"(PackageInstanceId, PackageId, TypeId, MajorVersion, MinorVersion, RefreshVersion, BuildVersion, CommitId, " +
"ComponentId, Guid, PackageName) " +
"VALUES(@PackageInstanceId0, @PackageId0, @TypeId0, @MajorVersion0, @MinorVersion0, @RefreshVersion0, @BuildVersion0, @CommitId0, " +
"@ComponentId0, @Guid0, @PackageName0), " +
"(@PackageInstanceId1, @PackageId1, @TypeId1, @MajorVersion1, @MinorVersion1, @RefreshVersion1, @BuildVersion1, @CommitId1, " +
"@ComponentId1, @Guid1, @PackageName1)," +
"(@PackageInstanceId2, @PackageId2, @TypeId2, @MajorVersion2, @MinorVersion2, @RefreshVersion2, @BuildVersion2, @CommitId2, " +
" @ComponentId2, @Guid2, @PackageName2)," +
"(@PackageInstanceId3, @PackageId3, @TypeId3, @MajorVersion3, @MinorVersion3, @RefreshVersion3, @BuildVersion3, @CommitId3, " +
"@ComponentId3, @Guid3, @PackageName3)," +
"(@PackageInstanceId4, @PackageId4, @TypeId4, @MajorVersion4, @MinorVersion4, @RefreshVersion4, @BuildVersion4, @CommitId4, " +
"@ComponentId4, @Guid4, @PackageName4)," +
"(@PackageInstanceId5, @PackageId5, @TypeId5, @MajorVersion5, @MinorVersion5, @RefreshVersion5, @BuildVersion5, @CommitId5, " +
"@ComponentId5, @Guid5, @PackageName5)," +
"(@PackageInstanceId6, @PackageId6, @TypeId6, @MajorVersion6, @MinorVersion6, @RefreshVersion6, @BuildVersion6, @CommitId6," +
" @ComponentId6, @Guid6, @PackageName6)," +
"(@PackageInstanceId7, @PackageId7, @TypeId7, @MajorVersion7, @MinorVersion7, @RefreshVersion7, @BuildVersion7, @CommitId7, " +
"@ComponentId7 , @Guid7, @PackageName7)," +
"(@PackageInstanceId8, @PackageId8, @TypeId8, @MajorVersion8, @MinorVersion8, @RefreshVersion8, @BuildVersion8, @CommitId8," +
" @ComponentId8, @Guid8, @PackageName8)," +
"(@PackageInstanceId9, @PackageId9, @TypeId9, @MajorVersion9, @MinorVersion9, @RefreshVersion9, @BuildVersion9, @CommitId9, " +
"@ComponentId9, @Guid9, @PackageName9), " +
"(@PackageInstanceId10, @PackageId10, @TypeId10, @MajorVersion10, @MinorVersion10, @RefreshVersion10, @BuildVersion10, @CommitId10, " +
"@ComponentId10, @Guid10, @PackageName10)";
AceQLCommand command = new AceQLCommand(sql2, connection);
command.Parameters.AddWithValue("@PackageInstanceId0", 9999999);
command.Parameters.AddWithValue("@PackageId0", 15796);
command.Parameters.AddWithValue("@TypeId0", 1);
command.Parameters.AddWithValue("@MajorVersion0", 1);
command.Parameters.AddWithValue("@MinorVersion0", 0);
command.Parameters.AddWithValue("@RefreshVersion0", 0);
command.Parameters.AddWithValue("@BuildVersion0", 1);
command.Parameters.AddWithValue("@CommitId0", 0);
command.Parameters.AddWithValue("@ComponentId0", "HKLM");
command.Parameters.AddWithValue("@Guid0", "390e78ce-f16c-4669-b53d-0fd89d07e393");
command.Parameters.AddWithValue("@PackageName0", "BTAceQL");
[... 9 more "rows" delcared here]
command.Parameters.AddWithValue("@PackageInstanceId10", 9999999);
command.Parameters.AddWithValue("@PackageId10", 15796);
command.Parameters.AddWithValue("@TypeId10", 1);
command.Parameters.AddWithValue("@MajorVersion10", 1);
command.Parameters.AddWithValue("@MinorVersion10", 0);
command.Parameters.AddWithValue("@RefreshVersion10", 0);
command.Parameters.AddWithValue("@BuildVersion10", 1);
command.Parameters.AddWithValue("@CommitId10", 0);
command.Parameters.AddWithValue("@ComponentId10", "HKLM");
command.Parameters.AddWithValue("@Guid10", "390e78ce-f16c-4669-b53d-0fd89d07e393");
command.Parameters.AddWithValue("@PackageName10", "BTAceQL")
command.Prepare();
await command.ExecuteReaderAsync();
I get the following error: "errorType: 1 / reason: Must declare the scalar variable \"@P1100\". / httpStatusCode: BadRequest"
the trace in aceQL server part is:
Jun 12, 2020 8:52:59 PM org.kawanfw.sql.servlet.sql.LoggerUtil log
WARNING: Prepared Statement Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Must declare the scalar variable "@P1100". - SQL order: INSERT INTO dbo.GuidStorage (PackageInstanceId, PackageId, TypeId, MajorVersion, MinorVersion, RefreshVersion, BuildVersion, CommitId, ComponentId, Guid, PackageName) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (?0, ?0, ?0, ?0, ?0, ?0, ?0, ?0, ?0, ?0, ?0) - parms:{1=INTEGER, 2=INTEGER, 3=INTEGER, 4=INTEGER, 5=INTEGER, 6=INTEGER, 7=INTEGER, 8=INTEGER, 9=VARCHAR, 10=VARCHAR, 11=VARCHAR, 12=INTEGER, 13=INTEGER, 14=INTEGER, 15=INTEGER, 16=INTEGER, 17=INTEGER, 18=INTEGER, 19=INTEGER, 20=VARCHAR, 21=VARCHAR, 22=VARCHAR, 23=INTEGER, 24=INTEGER, 25=INTEGER, 26=INTEGER, 27=INTEGER, 28=INTEGER, 29=INTEGER, 30=INTEGER, 31=VARCHAR, 32=VARCHAR, 33=VARCHAR, 34=INTEGER, 35=INTEGER, 36=INTEGER, 37=INTEGER, 38=INTEGER, 39=INTEGER, 40=INTEGER, 41=INTEGER, 42=VARCHAR, 43=VARCHAR, 44=VARCHAR, 45=INTEGER, 46=INTEGER, 47=INTEGER, 48=INTEGER, 49=INTEGER, 50=INTEGER, 51=INTEGER, 52=INTEGER, 53=VARCHAR, 54=VARCHAR, 55=VARCHAR, 56=INTEGER, 57=INTEGER, 58=INTEGER, 59=INTEGER, 60=INTEGER, 61=INTEGER, 62=INTEGER, 63=INTEGER, 64=VARCHAR, 65=VARCHAR, 66=VARCHAR, 67=INTEGER, 68=INTEGER, 69=INTEGER, 70=INTEGER, 71=INTEGER, 72=INTEGER, 73=INTEGER, 74=INTEGER, 75=VARCHAR, 76=VARCHAR, 77=VARCHAR, 78=INTEGER, 79=INTEGER, 80=INTEGER, 81=INTEGER, 82=INTEGER, 83=INTEGER, 84=INTEGER, 85=INTEGER, 86=VARCHAR, 87=VARCHAR, 88=VARCHAR, 89=INTEGER, 90=INTEGER, 91=INTEGER, 92=INTEGER, 93=INTEGER, 94=INTEGER, 95=INTEGER, 96=INTEGER, 97=VARCHAR, 98=VARCHAR, 99=VARCHAR, 100=INTEGER, 101=INTEGER, 102=INTEGER, 103=INTEGER, 104=INTEGER, 105=INTEGER, 106=INTEGER, 107=INTEGER, 108=VARCHAR, 109=VARCHAR, 110=VARCHAR, 111=INTEGER, 112=INTEGER, 113=INTEGER, 114=INTEGER, 115=INTEGER, 116=INTEGER, 117=INTEGER, 118=INTEGER, 119=VARCHAR, 120=VARCHAR, 121=VARCHAR} - values: [9999999, 15796, 1, 1, 0, 0, 1, 0, HKLM, 390e78ce-f16c-4669-b53d-0fd89d07e393, BTAceQL, 9999999, 15796, 1, 1, 0, 0, 1, 0, HKLM, 390e78ce-f16c-4669-b53d-0fd89d07e393, BTAceQL, 9999999, 15796, 1, 1, 0, 0, 1, 0, HKLM, 390e78ce-f16c-4669-b53d-0fd89d07e393, BTAceQL, 9999999, 15796, 1, 1, 0, 0, 1, 0, HKLM, 390e78ce-f16c-4669-b53d-0fd89d07e393, BTAceQL, 9999999, 15796, 1, 1, 0, 0, 1, 0, HKLM, 390e78ce-f16c-4669-b53d-0fd89d07e393, BTAceQL, 9999999, 15796, 1, 1, 0, 0, 1, 0, HKLM, 390e78ce-f16c-4669-b53d-0fd89d07e393, BTAceQL, 9999999, 15796, 1, 1, 0, 0, 1, 0, HKLM, 390e78ce-f16c-4669-b53d-0fd89d07e393, BTAceQL, 9999999, 15796, 1, 1, 0, 0, 1, 0, HKLM, 390e78ce-f16c-4669-b53d-0fd89d07e393, BTAceQL, 9999999, 15796, 1, 1, 0, 0, 1, 0, HKLM, 390e78ce-f16c-4669-b53d-0fd89d07e393, BTAceQL, 9999999, 15796, 1, 1, 0, 0, 1, 0, HKLM, 390e78ce-f16c-4669-b53d-0fd89d07e393, BTAceQL, 9999999, 15796, 1, 1, 0, 0, 1, 0, HKLM, 390e78ce-f16c-4669-b53d-0fd89d07e393, BTAceQL]
Jun 12, 2020 8:52:59 PM org.kawanfw.sql.servlet.sql.LoggerUtil log
WARNING: com.microsoft.sqlserver.jdbc.SQLServerException: Must declare the scalar variable "@P1100"."
=> When I add only 10 rows to the table, the query works as expected. Also I can execute the same statement in SQL management studio against a MS SQL database without issue. So I am not sure if there might be a parsing problem in aceQL Client or Server part?
Any hint is very welcome.