Skip to content

Must declare the scalar variable "@P1100". #3

Open
@quirrel1002

Description

@quirrel1002

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.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions