Some questions about Data API

Hi, we just purchased the PRO license recently and we wonder if can we run
(i) Raw SQL statements
(ii) or execute stored procedures

against our MsSQL and how?

Thanks!

Hello @Boon_Tan ,

We do not support any of the options you asked about.

Regards, Nazar

Hi Nazar,

Thanks for the reply.

Does backendless support getting data from a database view?

@Boon_Tan ,

Yes, Backendless supports getting data from Database View.

Regards,
Nazar

Hi Nazar,

Thanks for the answer.

is it done using “Load Table Objects”? I tried to enter my view’s name as the table name and it doesn’t work.

Hello @Boon_Tan,

yes, it should be done using the Load Table Objects block and it should work.
Please make sure you have no typo in the name of the view. If it still doesn’t work, please provide us with your appId and a simple page where we can reproduce it.

Regards,
Stanislaw

Hi Stanislaw,

It still doesn’t work. Please find the attached screenshots and error message.

We are using the PRO version and it’s on-prem and it’s not available to the public.

Let me know how can I provide you the access.

Cheers,
Jacky



Hi @Boon_Tan,

how did you create a view?
When we talked about the ability to receive data from views, we meant those views that are created directly in the console.

Hi Stanislaw,

We are using the PRO version and we have the ability to connect backendless to our own MsSQL database.

I’m talking about the database views of our MsSQL server. We are not using a Backendless database to store our data.

2022-11-09_11-10

@Boon_Tan did do you see your view in the left menu? Could you try to reconnect your DB and try one more time?

Hi Dima,

Refresh doesn’t work. I have to remove it and recreate the connection, then the views appear in the list, is there a better way?

Note: We tried to re-save the connection as well and got an error message “The system has encountered an error.”, the following is the details

Backendless encountered an error while handling the request. Error id is 4DAB09CB-58A3-A92C-FFCB-ACF45D3A3E00. You can find the error in the bl-server logs
null
java.lang.NullPointerException
at java.base/java.util.concurrent.ConcurrentHashMap.putVal(ConcurrentHashMap.java:1011)
at java.base/java.util.concurrent.ConcurrentHashMap.put(ConcurrentHashMap.java:1006)
at java.base/java.util.Properties.put(Properties.java:1340)
at com.backendless.dc.model.dto.DataConnectorDTO.initFrom(DataConnectorDTO.java:33)
at com.backendless.dc.management.DataConnectorService.lambda$updateDataConnector$3(DataConnectorService.java:202)
at com.backendless.datamodel.application.dao.AppJpaTransaction.executeSync(AppJpaTransaction.java:134)
at com.backendless.datamodel.application.dao.AppJpaTransaction.executeSync(AppJpaTransaction.java:203)
at com.backendless.datamodel.application.dao.AppJpaTransaction.lambda$execute$0(AppJpaTransaction.java:46)
at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1700)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:829)
Suppressed: java.util.concurrent.ExecutionException: java.lang.NullPointerException
at java.base/java.util.concurrent.CompletableFuture.reportGet(CompletableFuture.java:395)
at java.base/java.util.concurrent.CompletableFuture.get(CompletableFuture.java:1999)
at com.backendless.async.AsyncUtils.safeGet(AsyncUtils.java:52)
at com.backendless.dc.management.DataConnectorService.updateDataConnector(DataConnectorService.java:177)
at controllers.console.dc.DataConnectorsController.lambda$updateDataConnector$3(DataConnectorsController.java:113)
at com.backendless.async.BackendlessExecutorService.lambda$submit$0(BackendlessExecutorService.java:75)
… 4 more

Hi. We will try to check that part of code [BKNDLSS-30376].
But you should to understand that the investigation is complicated by the fact that we cannot reproduce your environment on our side and we have no access to your installation.

I would like to go back on a few steps and clarify why did you want to attach a second MySQL DMBS? Is it possible that you will use only one db for your Backendless Pro installation? It significantly simplify the further configuration and save you time on maintenance. In such a case Backendless will use your DB for its internal work also.

According to the problem.
After the scanning of the external DB do you see all the tables/view you want to work with?

Try without the db name in the field table name.

Hi Oleg,

After the scanning of the external DB do you see all the tables/view you want to work with?
Answer: We refreshed the connection and didn’t see the view. I have to remove the connection and add it back again then it appeared. Re-saving the connection threw the error I mentioned in the previous post.

Try without the db name in the field table name .
Answer: if the view is not shown in the list, removing the db name don’t work.

We have a small fix for the error mentioned above ( java.lang.NullPointerException). It should appear on production with the next release.

But if to talk about general workflow:

  • firstly you attach external db;
  • then Backendless scans it;
  • you should see tables/view;
  • you should use tables and view without db name prefix.

What about my suggestion relative to the using only one DBMS? Does it real scenario for you? What about my suggestion about trial to use one DBMS?