Cannot delete database from DataSHIELD

Support Beginner Support
1

Hello Everyone,

I added multiple databases to the DataSHIELD instance of ours for “import” usage, but for one of the databases, the “Unregister” option is missing, although no projects is connected to it (I deleted all projects). Also, from this database I could not import any tables to the project, as I encountered the following error: java.lang.StringIndexOutOfBoundsException: String index out of range: -1

I’d appreciate any help from your side. Thanks a lot :slight_smile:

2

Hi again,

Regarding your previous question and this one, I would not import database data directly (it is a bit tricky as the ‘import’ database SQL schema could be more complex than expected). Instead you could go with an import R script or use an even more elegant method by connecting directly to database with resources.

Regards
Yannick

3

Hi Yannick,

thanks a lot for your response. Since then, I have played around a little with the resources and now can access and use resources from R Server. However, I can only create the resource manually through UI (which is not that practical as we might have dozens of tables/resources per project). I was trying to create resources via R (using opal.resource_create) but it was unsuccessful. Here is my code (all lines are run fine except the last line. See comment in code for the exact error):

o2 <- opal.login(username = "opal-admin", password = 'admin-pass', url = 'https://myopalserver.com')

# create an Opal file based resource
opal.resource_create(o2, "test", "MS_R", 
                     url = "mysql://<DB-URL>:3306/DB1/Table1",identity = 'db-username', secret = 'db-pass')

# to test the resource assignment and its resolution
opal.assign.resource(o2, "client", "test.MS_R")
opal.execute(o2, "class(client)")

# by running the following run, I get following error: "Error: [Client error: (400) Bad Request] Error : Failed to connect: Access denied for user 'rserver'@'<some ip range>' (using password: YES)"
opal.assign.script(o2, "data", quote(as.resource.data.frame(client)))

Do you have an idea what could be wrong? Is it possible at all what I am doing? Thanks again

4

Would it be because you assign the resource “project1.resource1” instead of the one you just created which is “test.MS_R” ?

5

Hi, thank you for your quick reply :slight_smile: Actually, no. In that case you would get some error like “resource already exist” or something. But my error is on the last line “Error: [Client error: (400) Bad Request] Error : Failed to connect: Access denied for user ‘rserver’@‘’ (using password: YES)”

I think it tries to connect to mysql database with “rserver” user even though I have set db-username while creating the resource. I also tried to adjust Mysql credentials via UI after creating the resource via R code, but it did not change anything

6

But you are still assigning the wrong resource at this line:

opal.assign.resource(o2, "client", "project1.resource1")

It should be

opal.assign.resource(o2, "client", "test.MS_R")
7

ah, ok. Sorry, it was my mistake when I was trying to rename original names when writing my question in forum. But in real code, both lines are the same! So I think that would not be the problem. I will edit the code in question now

8

Ok then use the other resource creation function to which you can provide the same entries as in the UI:

opal.resource_extension_create(o, "test", "MS_R", 
                                provider = "resourcer", 
                                factory = "sql", 
                                parameters = list(
                                  driver = "mysql",
                                  host = "<DB-URL>",
                                  port = "3306",
                                  db = "DB1",
                                  table = "Table1"
                                ),
                                credentials = list(
                                  username = "db-username", 
                                  password = "db-pass")
)

Regards
Yannick

1 Like
9

Worked like a charm! Thanks a lot!