# Problem retrieving annotations from sqlite3

**URL:** <https://pdfjs.community/t/problem-retrieving-annotations-from-sqlite3/1617>\
**Category:** Technical Support\
**Created:** [April 21, 2022, 7:38am UTC](https://pdfjs.community/t/problem-retrieving-annotations-from-sqlite3/1617 "2022-04-21T07:38:42Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![abel.manzanera](https://avatars.discourse-cdn.com/v4/letter/a/ea5d25/32.png) [@abel.manzanera](https://pdfjs.community/u/abel.manzanera)\
**Post date:** [April 21, 2022, 7:38am UTC](https://pdfjs.community/t/problem-retrieving-annotations-from-sqlite3/1617/1 "2022-04-21T07:38:42Z")

</div>

**Which product are you using?**  
PDF.js Express Plus

**PDF.js Express Version**  
Latest from npm

**Detailed description of issue**  
i’m use this scripts:

> **[GitHub - PDFTron/webviewer-annotations-sqlite3-sample: WebViewer sample that...](https://github.com/PDFTron/webviewer-annotations-sqlite3-sample)**
>
> WebViewer sample that shows saving annotations to an SQLite3 database in Node.js - GitHub - PDFTron/webviewer-annotations-sqlite3-sample: WebViewer sample that shows saving annotations to an SQLite...

with pdftron with no problems. I have modified the original code so that it creates a database per user and a table per pdf.  
When i try to use with pdf.jsExpress i can save the annotations, create the table, create the database per user but i can’t restore the annotations when refresh or view with other web navigator.

there’re a annotationsHandler.js:

* * *

const fs = require(‘fs’);  
const SQLite3 = require(‘sqlite3’).verbose();  
const TABLE = ‘annotations’;

module.exports = (app) =\> {

// Handle POST request sent to ‘/server/annotationHandler.js’  
app.post(’/server/annotationHandler.js’, (req, res) =\> {  
const documentId = req.query.documentId;  
const nif = req.query.nif;  
var nifDB = ‘/var/www/html/WebViewer/annotations/server/db/’+nif+’.db’;

```
 // Create a database if it doesn't exist
if (!fs.existsSync(nifDB)) {
  console.log('creem la bbdd '+nifDB);
  fs.writeFileSync(nifDB, '');
} 

const annotationId = JSON.parse(req.body).annotationId;
const xfdfString = JSON.parse(req.body).xfdfString.replace(/\'/g, `''`); // To escape single quote character in SQLite

const db = new SQLite3.Database(nifDB);
db.run(`CREATE TABLE IF NOT EXISTS ${TABLE} (documentId TEXT, annotationId TEXT PRIMARY KEY, xfdfString TEXT)`);
db.serialize(() => {
  const isDeleteCommand = /<delete>(.*)<\/delete>/s.test(xfdfString);

  let query;
  if (isDeleteCommand) {
    // Instead of saving the delete command, we can remove the row from the database
    query = `DELETE FROM ${TABLE} WHERE annotationId = '${annotationId}'`;
  } else {
    // Save document ID, annotation ID and XFDF string to database
    query = `INSERT OR REPLACE INTO ${TABLE} VALUES ('${documentId}', '${annotationId}', '${xfdfString}')`;
  }

  db.run(query, err => {
    if (err) {
      res.status(500);
    } else {
      res.status(200);
    }
    res.end();
  });
});
db.close();

```

});

// Handle GET request sent to ‘/server/annotationHandler.js’  
app.get(’/server/annotationHandler.js’, (req, res) =\> {  
const documentId = req.query.documentId;  
const nif = req.query.nif;  
var nifDB = ‘/var/www/html/WebViewer/annotations/server/db/’+nif+’.db’;  
const db = new SQLite3.Database(nifDB);  
// Read from the database and send the rows as a response  
db.all(`SELECT annotationId, xfdfString FROM ${TABLE} WHERE documentId = '${documentId}'`, (err, rows) =\> {  
if (err) {  
res.status(204);  
} else {  
res.header(‘Content-Type’, ‘application/json’);  
res.status(200).send(rows);  
}  
res.end();  
});  
db.close();  
});  
}

* * *

The annotationsHandler is called by the index.js that i paste here:

* * *

var viewerElement = document.getElementById(‘viewer’);  
const queryString = window.location.search;  
const urlParams = new URLSearchParams(queryString);  
var pdf = urlParams.get(‘pdf’);  
var nif = urlParams.get(‘nif’);  
var DOCUMENT\_ID = urlParams.get(‘pdfId’);  
WebViewer({  
path: ‘lib’,  
initialDoc: '[http://estapl02.uab.cat/client/llibres/’+pdf](http://estapl02.uab.cat/client/llibres/'+pdf),  
documentXFDFRetriever: async () =\> {  
const rows = await loadxfdfStrings(DOCUMENT\_ID);  
return JSON.parse(rows).map(row =\> row.xfdfString);  
}  
}, viewerElement).then(instance =\> {  
instance.UI.setLanguage(‘es’);  
instance.UI.setTheme(‘dark’);  
instance.UI.disableElements([‘downloadButton’]);  
instance.UI.disableElements([‘printButton’]);  
instance.UI.disableElements([‘toolbarGroup-Shapes’]);  
instance.UI.disableElements([‘toolbarGroup-Edit’]);  
instance.UI.disableElements([‘toolbarGroup-Insert’]);  
instance.UI.disableElements([‘toolbarGroup-Forms’]);  
instance.UI.disableElements([‘toolbarGroup-FillAndSign’]);

var docViewer = instance.docViewer;  
var annotManager = docViewer.getAnnotationManager();

// Save when annotation change event is triggered (adding, modifying or deleting of annotations)  
annotManager.addEventListener(‘annotationChanged’, function(annots, action, options) {  
// If the event is triggered by importing then it can be ignored  
// This will happen when importing the initial annotations from the server or individual changes from other users  
if (options.imported) return;

```
annotManager.exportAnnotCommand().then(function (xfdfStrings) {
  annots.forEach(function(annot) {
    savexfdfString(DOCUMENT_ID, annot.Id, xfdfStrings);
  });
});

```

});  
});

// Make a POST request with document ID, annotation ID and XFDF string  
var savexfdfString = function(documentId, annotationId, xfdfString) {  
return new Promise(function(resolve) {  
fetch(`/server/annotationHandler.js?documentId=${documentId}&nif=${nif}`, {  
method: ‘POST’,  
body: JSON.stringify({  
annotationId,  
xfdfString  
})  
}).then(function(res) {  
if (res.status === 200) {  
resolve();  
}  
});  
});  
};

// Make a GET request to get XFDF string  
var loadxfdfStrings = function(documentId) {  
return new Promise(function(resolve) {  
fetch(`/server/annotationHandler.js?documentId=${documentId}&nif=${nif}`, {  
method: ‘GET’  
}).then(function(res) {  
if (res.status === 200) {  
res.text().then(function(xfdfStrings) {  
resolve(xfdfStrings);  
});  
}  
});  
});  
};

* * *

---

<div class="post-metadata">

**Author:** ![Logan](https://yyz1.discourse-cdn.com/flex031/user_avatar/pdfjs.community/logan/32/13_2.png) [@Logan](https://pdfjs.community/u/Logan)\
**Post date:** [April 27, 2022, 2:22pm UTC](https://pdfjs.community/t/problem-retrieving-annotations-from-sqlite3/1617/3 "2022-04-27T14:22:49Z")

</div>

Hey there!

PDF.js Express does not support `documentXFDFRetriever` at the moment. Instead, just load and import your annotations when the viewer loads like so:

```javascript
WebViewer({}, ele).then(instance => {
 
   instance.Core.documentViewer.addEventListener('documentLoaded', async () => {
    const JSON = await loadxfdfStrings()
    JSON.parse(rows).forEach(row => {
        instance.Core.annotationManager.importAnnotationCommand(row.xfdfString);
    })
    
  })   

})

```

Thanks!  
Logan

---

<div class="post-metadata">

**Author:** ![abel.manzanera](https://avatars.discourse-cdn.com/v4/letter/a/ea5d25/32.png) [@abel.manzanera](https://pdfjs.community/u/abel.manzanera)\
**Post date:** [April 28, 2022, 1:18pm UTC](https://pdfjs.community/t/problem-retrieving-annotations-from-sqlite3/1617/4 "2022-04-28T13:18:59Z")

</div>

Thanks Logan, that’s works now but with a little problem.  
The first time that you tri to make a coment or a line (underline) the console show this error  
PayloadTooLargeError: request entity too large  
at readStream (/var/www/html/WebViewer/annotations/node\_modules/raw-body/index.js:156:17)  
at getRawBody (/var/www/html/WebViewer/annotations/node\_modules/raw-body/index.js:109:12)  
at read (/var/www/html/WebViewer/annotations/node\_modules/body-parser/lib/read.js:79:3)  
at textParser (/var/www/html/WebViewer/annotations/node\_modules/body-parser/lib/types/text.js:86:5)  
at Layer.handle [as handle\_request] (/var/www/html/WebViewer/annotations/node\_modules/express/lib/router/layer.js:95:5)  
at trim\_prefix (/var/www/html/WebViewer/annotations/node\_modules/express/lib/router/index.js:328:13)  
at /var/www/html/WebViewer/annotations/node\_modules/express/lib/router/index.js:289:7  
at Function.process\_params (/var/www/html/WebViewer/annotations/node\_modules/express/lib/router/index.js:348:12)  
at next (/var/www/html/WebViewer/annotations/node\_modules/express/lib/router/index.js:280:10)  
at expressInit (/var/www/html/WebViewer/annotations/node\_modules/express/lib/middleware/init.js:40:5)

and don’t save nothing, the second time the comment is saved, if you wait a minut or change the tool for the annotations make the same, the first time this error and the second time save the annotation.

Any ideas??

---

<div class="post-metadata">

**Author:** ![Logan](https://yyz1.discourse-cdn.com/flex031/user_avatar/pdfjs.community/logan/32/13_2.png) [@Logan](https://pdfjs.community/u/Logan)\
**Post date:** [April 28, 2022, 4:17pm UTC](https://pdfjs.community/t/problem-retrieving-annotations-from-sqlite3/1617/5 "2022-04-28T16:17:16Z")

</div>

Hi there,

This appears to be an issue on your server and not related to PDF.js Express. you probably need to increase the max body size your server allows.

Thanks!  
Logan

---

<div class="post-metadata">

**Author:** ![Logan](https://yyz1.discourse-cdn.com/flex031/user_avatar/pdfjs.community/logan/32/13_2.png) [@Logan](https://pdfjs.community/u/Logan)\
**Post date:** [April 28, 2022, 4:17pm UTC](https://pdfjs.community/t/problem-retrieving-annotations-from-sqlite3/1617/6 "2022-04-28T16:17:19Z")

</div>