Problems with Copy-and-Paste

Users have been reporting problems with the copy and paste functions in their web browsers. If you experience this, it may help to log out of Virtual DBeaver, clear the cache in your web browser, and log back in. Also, the Chrome web browser works best with Virtual DBeaver on Windows, and the Safari web browser works best on Macs.

Log in to your Virtual DBeaver account

To use your Virtual DBeaver account:

1. Open a web browser window and go to the Virtual DBeaver URL -

   https://rdweb.wvd.microsoft.com/arm/webclient/index.html

2. Choose "Virtual DBeaver"

3. At the "Access local resources" prompt, click Allow
4. At the "Enter your credentials" prompt, enter your full Cornell email address in the Username field and your email password in the Password field, then click Submit.
5. Click the DBeaver shortcut on the desktop to open DBeaver. If this is your first time connecting, please follow the instructions to "Configure Your Connection to the Reporting Database in Virtual DBeaver." 
6. From the SQL Editor menu, choose New SQL script

Select the Report Query to Run

To select the report query you would like to run, go to the FOLIO Canned Reports Directory.

1. Click the query code link (e.g., CR100)
2. Click the sql file to open it
3. Click Raw in the top right corner of the window
4. Click Ctrl+A then Ctrl+C to select and copy all the text of the query
5. Return to your Virtual DBeaver window
6. Click Ctrl+V to paste the text of the query into the New SQL script window
7. From the File menu, select Save to save this query in DBeaver with a filename and the ".sql" file extension
8. From the SQL Editor menu, choose Execute SQL Script to run your query
9. Review your results in the bottom half of your window

Select Filters

Filters allow you to refine your query to give you results that are limited by certain criteria, such as location. To add filters:

1. Return to the query in your Virtual DBeaver window.
2. Look for the WITH statement near the beginning of the query
3. In the SELECT statement under WITH, enter filters within the single quote marks
4. Run your query and review the results in the bottom half of the window
5. Continue adjusting your filters as needed using the same approach
6. From the File menu, select Save to save this query in DBeaver with a filename and the ".sql" file extension
7. Just above your query, make sure the ldp_cornell data source has been selected
   
   
8. Review your results in the bottom half of your window
9. From the SQL Editor menu, choose Execute SQL Script to run your query
10. Review your results in the bottom half of your window

Filter Selection Example

Here is an example of setting date and location filters in the CR100 loans_and_renewals_counts query. 

1. Follow steps above to Select the Report Query to Run, and choose the CR100 query
2. Return to the query in your Virtual DBeaver window
3. Look for the WITH statement near the beginning of the query
4. In the SELECT statement under WITH, look for the start and end date parameters to set the date
   
   
5. To show results for the period of 6/1/21 through 6/15/21, go to the SELECT statement under WITH and enter those start and end dates within the single quote marks
   
   
6. To show results for just the Olin library location, go to the SELECT statement under WITH and enter Olin in the single quote marks next to items_effective_location_filter
   
   
   
   
7. Just above your query, make sure the ldp_cornell data source has been selected
   
   
   
   
8. Right-click on the name of the default script (here, the name is "Script-1"), then choose Rename File
   
   
9. Give your file a name with the .sql extension, such as "loans_and_renewals_counts_olin.sql" 
10. From the SQL Editor menu, choose Execute SQL Script to run your query
11. Review your results in the bottom half of your window

Export Results to CSV

You can export your query results to CSV if you would like to use them in Excel. To export your results:

1. Return to the query in your Virtual DBeaver window
2. From the SQL Editor menu, choose Execute SQL Script to run your query
3. Right-click on any part of the results set shown in the bottom right side of the window and choose Export data...
   
   
   
   
4. Choose Export to CSV file(s)
   
   
   
   
5. On Extraction Settings, click Next. On Format Settings, click Next. 
6. On Output Settings, click the orange folder icon and navigate to your C:\Users\[netid]\OneDrive - Cornell University\Desktop folder. The Directory field should populate with this path. If you do not have a One Drive - Cornell University folder, choose your Desktop folder instead, and continue to follow the set of instructions below for using the Share feature to send yourself the file. If you would like to know more about why you could not find One Drive, please contact CUL Desktop Services. 
7. In the File name pattern field, enter a filename for the report you are running.
8. If you want a timestamp automatically appended to the filename in your output, include the TimeStamp pattern you prefer (yyyyMMddHH) in the TimeStamp pattern field. If you do not wish to include a TimeStamp Pattern, simply erase the settings in this field. Click Next when your settings are complete.
   
   
   
   
9. On the Confirm window, click Proceed to start the data export. 
10. Close or minimize your DBeaver window so that you can see the desktop of your Virtual DBeaver environment.
11. Your exported file should appear in 2 places (which are actually the same place through the magic of Windows virtual environments): on the Desktop of your Virtual DBeaver environment and, if you open File Explorer outside of Virtual DBeaver and browse, on your One Drive - Cornell University/Desktop folder
    
          
    
    

12. Another way to move your exported file is to right-click on the file in Virtual DBeaver, choose Share, enter your email address in the "To:" field, and press Send. You can then open your email on your main computer and save the attached file to wherever you want on that computer. This is the best option for Mac users, since they do not typically use One Drive.
    
             
    
    
    

13. Open your CSV file with Excel, then Save it with the .xls Excel file type

Find Your Saved Query to Run Again

Once you have saved your query, you can return to that query to run it again. To find it:

1. From the Window menu, choose Project Explorer
2. From the Project - General pane, open the Scripts folder
3. Choose your saved query from the scripts in this folder
4. Double-click on the file name to open the query

Some tips for working in Virtual DBeaver (DRAFT):

• Enter/exit full screen: When in DBeaver on virtual desktop, to make all of your screen/any popup windows fully visible, click on the double arrow icon in the upper right corner of the screen (in the black band). This will also allow you to: scroll and copy more efficiently; see the task bar of the virtual desktop to view files exported from DBeaver, etc.; and see all options in popup windows. When you want to access the task bar of your own computer again, click on the double arrow icon to toggle back to a slightly minimized view.
• Saving your work: When writing SQL, be sure to save your work.  You can use Ctrl-S, right click and click on save, or click on the on the file tab and then click on save.
• Viewing latest local table results: When you run a report that saves tables to a local schema, right click on the “tables” icon of the local schema and click on “Refresh” to see the latest data. Alternatively, use F5.
• Restoring/minimizing Database Navigator and Projects windows: If you mistakenly close your Database Navigator or Projects window, click on the Window Tab, and click on “Database Navigator” and “Project Explorer” to get them back. You can minimize or restore those windows using the icons at the top of the window.
• Running parts of an SQL script: In a set of queries or within a query, you can run a query/subquery by highlighting what you want to run and clicking on the execute SQL script icon.
• Stopping a query run: If you mistakenly run a query or set of queries you don’t want to run (or run in full): expand your screen; double click on the green flashing bar in the lower right; click on the red square in the window that opens; and then click on “stop” in the window that opens.