Unable to get createFromLocation option working for Windows

[xenown]

I'm trying to open an existing database using the createFromLocation option, but I just can't get it to work. I've tried

openDatabase({ name: "test", location: 'default', createFromLocation: "1" },
        () => { console.log("db load success!") }, (e) => { console.error("db load failure!"); console.error(e); console.error(e.code) });

which results in the error message being written. The error printed was "Error: Could not open database" followed by a stack trace. I have saved my sqlite file in D:\appname\windows\appname\www and have updated the visual studio solution to "Include In Project" the db file.

I have also tried

openDatabase({ name: "test", location: 'default', createFromLocation: "~Assets/test.sqlite" },
        () => { console.log("db load success!") }, (e) => { console.error("db load failure!"); console.error(e); console.error(e.code) });

which gives the same error.

I've made sure to follow the 4 windows specific setup steps listed on the repo README.

For what its worth, I've been compiling my app only through npx react-native run-windows.

Have I missed a step somewhere?

Expected Behavior

I'd expect the database file to be read.

Current Behavior

It's unable to read the file.

Possible Solution

Steps to Reproduce (for bugs)

Try createFromLocation option with React Native Windows.

Context

Your Environment

• React Native SQLite Storage Version used: 3
• React Native version used: react-native: 0.66.0-rc.1 => 0.66.0-rc.1 react-native-windows: 0.66.0-preview.6 => 0.66.0-preview.6
• Operating System and version (simulator or device): Windows 10 10.0.19042
• IDE used: Visual Studio
• Link to your project:

Debug logs

[MacKenzieHnC]

For the next soul who ends up here:

On Windows the default location ends up in C:\Users\\AppData\Local\Packages\

However, "createFromLocation:1" doesn't seem to save the database at all anywhere (I just searched my entire drive for it and it's not showing up). I'm not sure how that's possible, since you can still write to it in the program. I'll keep looking into this.

@andpor Do you have any idea where it expects the file to be? I've tried every location and combination I can think of with no luck.

[Shidouuu]

@MacKenzieHnC Thank you for this! For me after the random string it was under "LocalState". I don't think it is possible to save the database anywhere other than there, unfortunately.

[MacKenzieHnC]

You're welcome.

There has to be a way though. They wrote and pushed the code for it, so I assume it at least used to work. I'll dig into it again when my app's a little closer to release. It's important to me that this works on Windows.

For anyone else who wants to pursue this, the lead I was working on is that the bundler isn't packaging the databases into ms-appx. The code to retrieve from there is all set up, but getting the db to that folder on build is the problem

Check out this code, line 527 onward to see what I mean. assetPath is whatever you typed into createFromLocation

[MacKenzieHnC]

Okay, I think I figured this out mostly. The database doesn't exist anywhere, because in order for it to copy over, you have to mess with the project file.

So step 1: Put your database somewhere Step 2: add this to windows/<YourProjectName>/<YourProjectName>.vcxproj

...
<ItemGroup>
  <AppxPackagePayload Include="www\MyDB.db">
    <TargetPath>www\MyDB.db</TargetPath>
  </AppxPackagePayload>
</ItemGroup>
...

In my example, I put my DB in windows/<YourProjectName>/www.

Then, the DB is copied over to windows/x64/Debug/<YourAppName>/AppX/www.

I still can't open the db successfully, but this is what I think was tripping up all of us who couldn't find the file at all. The next part should be easy... right?

[Shidouuu]

How did you even find this? Lol

[MacKenzieHnC]

Idk what words I googled, but I figured it was something weird with how VS projects bundle assets, cause I kept finding weird things about using require() with non-image assets, so that's the vein I was following.

Lmk if you figure out how to actually open the db. That's what I'm working on tonight

I just cannot for the life of me figure out what directory the paths you type in React are relative to. I've tried all the ms-appx:///Bundle/<etc.> stuff, and every variation I can think of, but nothing works. Where the heck are we?

Maybe I'm wrong about how to get the files into the bundle...

EDIT: Okay, yeah, it's supposed to be way easier than this. Supposedly, there's a magical folder somewhere you can put your assets in where they'll get auto-bundled into the right location (though I don't think it's the location that this repo expects). It has something to do with windows/<YourAppName>/Bundle/index.windows.bundle, but I'm at a loss as to how to do this

[Shidouuu]

I've long since given up on this but keep it up Captain Ahab!

[MacKenzieHnC]

Okay, using this , I'm finally able to include the file correctly, but it still won't load. My current suspicion is that they changed how URIs work on react-native-windows, cause I can't even load simple test images. If that's the case, then no wonder this code fails. Unfortunately, that probably leaves us SoL, since this repo seems to be abandoned, so I don't think a PR to change it would matter.

If I'm right, I'll probably just end up adopting this project. Hopefully I'm wrong though.

[MacKenzieHnC]

Thank the gods, I was wrong. Still making progress, but there's an Access is Denied error showing up when I run the code in Visual Studio. Something about it trying to reach some out-of-bounds memory. No idea why, since I confirmed that ms-appx:///www/whatever is a valid directory.

[MacKenzieHnC]

Turns out the Access is denied is unrelated. The root error seems to be coming from this line, which throws A method was called at an unexpected time. so it seems like it's an async issue.

But if that's the case, what has changed since the last time this code worked which makes it not work now? I still think this is gonna end up being user error.

I can now officially get it to work by deleting the section where it tries to copy the db, but I hesitate to call that a solution since I don't know why they were trying to copy it instead of just using it as-is.

[MacKenzieHnC]

SOLVED: How to use pre-populated database with react-native-windows

Step 1: Link files

1. Put your database in windows\<ProjectName>\www
2. Open windows\<ProjectName>.sln with Visual Studio
3. Right-click <ProjectName>\Assets in the Solution Explorer
4. Add -> Existing Item and select your database
5. On the right-hand side, make sure to set Content to True

Step 2: Fix SQLitePlugin error

1. Still in Visual Studio, in the Solution for SQLitePlugin, open SQLitePlugin.cpp
2. Find the line that says CopyDbAsync(assetFile, to_hstring(*dbFileName)).GetResults();
3. Replace that entire try/catch with

   try
   {
   CopyDbAsync(assetFile, to_hstring(*dbFileName)).get();
   }
   catch (hresult_error const& ex)
   {
   if (ex.code() == 0x800700B7) 
   {
       // Ignore, CopyDbAsync throws when the file already exists.
   } else
   {
       onFailure("Unable to copy asset file: " + winrt::to_string(ex.message()));
       return;
   }
   }

You should now be able to use createFromLocation = '1' the same as in Android and iOS

Logic explained here.

[Shidouuu]

Splendid job! I look forward to using this if I ever return to the project I was working on.

[MacKenzieHnC]

Thank you ☺️ This was a lot of work lol Made a PR so hopefully this fix won't be necessary for long, but I'm not holding my breath.

[AhmedMustafa505]

createFromLocation: "1"

SOLVED: How to use pre-populated database with react-native-windows

Step 1: Link files

1. Put your database in windows\<ProjectName>\www
2. Open windows\<ProjectName>.sln with Visual Studio
3. Right-click <ProjectName>\Assets in the Solution Explorer
4. Add -> Existing Item and select your database
5. On the right-hand side, make sure to set Content to True

Step 2: Fix SQLitePlugin error

1. Still in Visual Studio, in the Solution for SQLitePlugin, open SQLitePlugin.cpp
2. Find the line that says CopyDbAsync(assetFile, to_hstring(*dbFileName)).GetResults();
3. Replace that entire try/catch with

try
{
    CopyDbAsync(assetFile, to_hstring(*dbFileName)).get();
}
catch (hresult_error const& ex)
{
    if (ex.code() == 0x800700B7) 
    {
        // Ignore, CopyDbAsync throws when the file already exists.
    } else
    {
        onFailure("Unable to copy asset file: " + winrt::to_string(ex.message()));
        return;
    }
}

You should now be able to use createFromLocation = '1' the same as in Android and iOS

Logic explained here.

is there a way I can provide a location outside windows directory ? i.e from C: directory for example.

import SQLite from 'react-native-sqlite-storage';

const db = SQLite.openDatabase({ name: 'AppDb.db', createFromLocation: '1', });

[MacKenzieHnC]

is there a way I can provide a location outside windows directory ? i.e from C: directory for example.

Sort of, but not really. The only way to get permissions would be to use a file picker, and currently the only tool I'm aware of for RNW file pickers is broken.

Further reading on UWP permissions Further reading on file pickers