Must declare the scalar variable "@P1100".

[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.

[ndepomereu]

Hi, This type of INSERT syntax is not supported in AceQL for now:

insert into table (col1, col2, col3) 
    values (1, 2, 3), (4,5,6), (7,8,9)

You must use the classical INSERT with one row, then call one command.ExecuteNonQueryAsync() per row:

string sql = "insert into customer values " + "" +
        "(@customer_id, @customer_title, @fname, " +
        "@lname, @addressline, @town, @zipcode, @phone)";

AceQLCommand command = new AceQLCommand(sql, connection);
command.Parameters.AddWithValue("@customer_id", 1);
command.Parameters.AddWithValue("@customer_title", "Sir");
command.Parameters.AddWithValue("@fname", "Doe");
command.Parameters.AddWithValue("@lname", "John");
command.Parameters.Add(new AceQLParameter("@addressline", "1 Madison Ave"));
command.Parameters.AddWithValue("@town", "New York");
command.Parameters.AddWithValue("@zipcode", "NY 10010");
command.Parameters.AddWithValue("@phone", "+1 (212) 586-71XX");

int rows = await command.ExecuteNonQueryAsync();

I will give a look on how to implement it in a future AceQL version.

[ndepomereu]

I leave it open, if you want to discuss how important is for you?

[quirrel1002]

hello, thanks for the quick response. I will also need to have a look how the performance impact is in changing from 10 to 1 row save at once. For now the idea was to save network travel time by saving more rows at once. Theoretical maximum would be 190 rows at once (because of SQL parameter limit). I will check actual impact and report back.

After first test the impact is measurable, for a use case with low number of rows to inster, it also has some 20-25% increase in query execution time when instertion 1 instead of 10 rows (which works also not officially supported). So it is not urgent from my site but highly welcome. I wonder what the effort would be to extend/add that feature?

[quirrel1002]

hello, I am curious if there is any update on this?

[ndepomereu]

Hi, This is still not planned but should be on a future release.

[ndepomereu]

Hi, A bulk insert syntax will be added in C# next 5.0 version planned this August. See this thread for details: https://github.com/kawansoft/AceQL.Client2/issues/11

[ndepomereu]

Hi,

The new C# Client SDK version 7.0 now supports multiple rows insert. (It requires upgrading the AceQL Server version to version 8.0.) It uses a specialized syntax close to JDBC. See user documentation.