CAP - Problem with dynamic entity generation and content population

[#1]

I am trying to dynamically create a cds entity in the SQLITE database and then populate it with values.

• create an entity based on String and compile it into model - Works
• create a table in SQL Lite - Also works, but really ugly at the moment
• When I try to prepare the Insert with "INSERT.into(User,..." It's missing an object somewhere. (See code and error below

Anybody any ideas. I am probably missing something simple, but I just don't see it.

I would also appreciate if anybody has a better way of creating the entities in SQLlite.

  // Establish connection to DB
      let dbSqlLite = await cds.connect('db');

      // Create a new entity
      let userEntity = `
      namespace riz.inno;
      entity User {
        userId: String(100);
        payGrade: LargeString;
        dateOfBirth :Date;
        promotionAmount: Decimal(10,2);
      }`

      // Compile model
      let cdsModel = cds.compile(userEntity)

      // --- There might be an easier way to persist the cds model in the sqllite db, but it's not transparent to me at this point
      // Export Model to SQL String
      let table2Create = await cds.compile(cdsModel).to.sql({ dialect: 'sqlite', as: 'str' })

      // Split string into table specific statements
      let myTables = table2Create.split(';')

      // Loop through each table and execute the create table statement 
      
      for (let z=0; z < myTables.length; z++) {
         let singleTableCreate = myTables[z]
       
        // Trim the whitespace
        singleTableCreate = singleTableCreate.trim()

        // Check if create exists
        if (singleTableCreate.length > 0) {

          // Concatenate the table create with semicolon
          singleTableCreate += ';'
          // Run the create table
          let dbResult = await dbSqlLite.run(singleTableCreate)
          return dbResult;
        }
        else {
          return 0;
        }

      }

      // --- Just check to see if table was created successfully
      let sqlliteAllTables = "SELECT  name FROM  sqlite_master WHERE type ='table' AND name NOT LIKE 'sqlite_%'";
      let dbReturnSelectAll = await dbSqlLite.run(sqlliteAllTables)
      console.log('List of SQL Server tables: ', dbReturnSelectAll)

      // determine custom meta data 
      let {User} = cdsModel.entities

      // Begin transaction
      let tx = dbSqlLite.tx(req)

      let myInsert = INSERT.into(User, [
        { userId: 'test', payGrade: 'Wuthering Heights', promotionAmount: 12.12 }
      ])

      let test = await tx.run(myInsert)
      console.log("Db insert Result ", test)

      let commitResponse = await tx.commit(req);
      console.log("Commit Response: ", commitResponse)

when I run this inside of my handler, I receive the following error

[cds] - TypeError: Cannot read property 'elements' of undefined
    at SQLiteDatabase._handler [as _input] (C:\Users\MartinStenzig\AppData\Roaming\npm\node_modules\@sap\cds-dk\node_modules\@sap\cds\libx\_runtime\db\generic\input.js:189:16)
    at SQLiteDatabase.dispatch (C:\Users\MartinStenzig\AppData\Roaming\npm\node_modules\@sap\cds-dk\node_modules\@sap\cds\lib\serve\Service-dispatch.js:38:53)
    at async AdminService.<anonymous> (C:\_projects\capLinkedExample\srv\handlers\admin.js:73:18)
    at async next (C:\Users\MartinStenzig\AppData\Roaming\npm\node_modules\@sap\cds-dk\node_modules\@sap\cds\lib\serve\Service-dispatch.js:55:17)
    at async AdminService.dispatch (C:\Users\MartinStenzig\AppData\Roaming\npm\node_modules\@sap\cds-dk\node_modules\@sap\cds\lib\serve\Service-dispatch.js:53:10)
    at async _invokeFunction (C:\Users\MartinStenzig\AppData\Roaming\npm\node_modules\@sap\cds-dk\node_modules\@sap\cds\libx\_runtime\cds-services\adapter\odata-v4\handlers\read.js:44:18)
    at async C:\Users\MartinStenzig\AppData\Roaming\npm\node_modules\@sap\cds-dk\node_modules\@sap\cds\libx\_runtime\cds-services\adapter\odata-v4\handlers\read.js:441:16 {
  id: '1105573',
  level: 'ERROR',
  timestamp: 1632329111256
}
[INTERNAL ERROR] TypeError: Cannot read property 'elements' of undefined
    at SQLiteDatabase._handler [as _input] (C:\Users\MartinStenzig\AppData\Roaming\npm\node_modules\@sap\cds-dk\node_modules\@sap\cds\libx\_runtime\db\generic\input.js:189:16)
    at SQLiteDatabase.dispatch (C:\Users\MartinStenzig\AppData\Roaming\npm\node_modules\@sap\cds-dk\node_modules\@sap\cds\lib\serve\Service-dispatch.js:38:53)
    at async AdminService.<anonymous> (C:\_projects\capLinkedExample\srv\handlers\admin.js:73:18)
    at async next (C:\Users\MartinStenzig\AppData\Roaming\npm\node_modules\@sap\cds-dk\node_modules\@sap\cds\lib\serve\Service-dispatch.js:55:17)
    at async AdminService.dispatch (C:\Users\MartinStenzig\AppData\Roaming\npm\node_modules\@sap\cds-dk\node_modules\@sap\cds\lib\serve\Service-dispatch.js:53:10)
    at async _invokeFunction (C:\Users\MartinStenzig\AppData\Roaming\npm\node_modules\@sap\cds-dk\node_modules\@sap\cds\libx\_runtime\cds-services\adapter\odata-v4\handlers\read.js:44:18)
    at async C:\Users\MartinStenzig\AppData\Roaming\npm\node_modules\@sap\cds-dk\node_modules\@sap\cds\libx\_runtime\cds-services\adapter\odata-v4\handlers\read.js:441:16

[Daniel7]

Just reproduced that → the error seems to be a bug in @sap/cds 5.4.

It does work with 5.5 which was released 3 days ago, with this important change:

• Never do a tx.commit() on a managed tx, i.e. one obtained through cds/srv.tx(req)
• I know that's not well documented → we're working on it; to be released next week
• Good news: you don't need to call cds/srv.tx(req) anymore ... actually your code could be reduced to:

Create tables:

let ddl = cds.compile(cdsModel).to.sql({ dialect: 'sqlite' })
await dbSqlite.run (ddl) //> runs all create statements

Fill in data:

await db.create(User, { 
  userId: 'test', payGrade: 'Wuthering Heights', promotionAmount: 12.12 
})

[martinstenzig]

Fantastic! I will keep my eyes open for the new documentation.