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 · Accepted solution]

Btw. you can also use cds.deploy(). For example, try this in cds repl:

let m = CDL`entity Foo { key ID:UUID; bar : Association to Bar; }; entity Bar { key ID:UUID; }`
let db = await cds.connect.to('sqlite::memory:')
await cds.deploy(m).to(db)

await db.read('sqlite_master')

let [ bar ] = await db.create('Bar',{})
await db.create ('Foo',{ bar })
await db.read('Foo')

[martinstenzig · Accepted solution]

daniel.hutzel , the deployment of the DB works great now, but I still have issues with the create. I tried the following:

   this.on('tryDbAccess', async (req) => {
      
      console.log('-- Start Test ---')

      let entityName = 'User'
    
      let cdlString = `entity ${entityName} {
        key userId : String(100);
        payGrade : LargeString;
        dateOfBirth : Date;
        promotionAmount : Double;
      };`
    
      let entityList = [
        {
          userId: 'abc1',
          payGrade: 'Test',
          promotionAmount: 2313.23
        },
        {
          userId: 'abc2',
          payGrade: 'Test 2',
          promotionAmount: 1213.43
        }
      ]
    
      let myNewCsn = cds.compile(cdlString)
    
      const db = await cds.connect.to('db')
    
      // Create tables based on CSN
      await cds.deploy(myNewCsn).to(db)
    
      console.log('NewCDL: ', JSON.stringify(myNewCsn))
    
      let entity = myNewCsn.definitions[entityName]
    
      console.log('Entity (user) is:', entity)
    
      await db.create(entity, entityList)
      let t = await db.read(entity)
      console.log('Content of user is', t)
    

    })
<br>

and receive a "[cds] - TypeError: Cannot read property 'userId' of undefined".

What am I missing?

[martinstenzig · Accepted solution]

Ok, I found a solution, but think there is probably a better way. At the moment I am adding the custom entity level cds to the global model by doing the following

cds.model.definitions[entityName] = myNewCsn[entityName]  

Is there a more 'proper' way of doing this or is this 'the" way?

[KM11 · Accepted solution]

martin.stenzig3

Hi Martin

Could you help me with the GIT project of the dynamic entity generation, since you have worked on it,

Thanks

Kanika

[martinstenzig · Accepted solution]

Anything specific you are looking for as I don't have a built out example but use it in a bigger context. Happy to setup a call and talk through it.

[KM11 · Accepted solution]

Hi martin.stenzig3

Entity kind of remains undefined for me.

Not sure if it deploys and while doing a read at the entity it gives me an error invalid table name: Could not find table/view V_TEST in schema CAC93ACB4A8A470AA92DEF91AE7BF8EC.

const db = await cds.connect.to("db");

let myNewCsn = cds.compile(cdlString)

await cds.deploy(myNewCsn).to(db)

console.log('NewCDL: ', JSON.stringify(myNewCsn))

let entity = myNewCsn.definitions[entityName]

cds.model.definitions[entityName] = entity;

let t = await db.read(entity)

console.log('Entity (user) is:', entity)
Entity (user) is: entity {
kind: 'entity',
'@cds.persistence.exists': true,
elements: [Object: null prototype]
{ CODE: string { '@title': 'CODE: CODE', key: true, type: 'cds.String', length: 3 },
TEXT: string { '@title': 'TEXT: TEXT', key: true, type: 'cds.String', length: 10 }
} }

Also how this statement mentioned in your previous comment helps?

cds.model.definitions[entityName] = myNewCsn[entityName]

Thanks

Kanika

[martinstenzig · Accepted solution]

Kanika,

can you refresh my memory, what are you trying to do?

In my case, I wanted to utilize CAP tools at runtime as I did not know what the exact definition of my entity is when I start up my CAP application. My specific case is a connection to SF. In that case you might have different meta data depending on the SF instance.

Regards,

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