Convert Stored Procedures

On this page

About this Task

Before you Begin
Steps
Examples
Next Steps
Learn More

You can import and convert your SQL stored procedures to MongoDB code with the query converter. If you are migrating from Microsoft SQL Server, you can also import and convert SQL Server functions. The query converter considers the mapping rules and schema transformations defined in your project when converting your SQL code.

About this Task

The query converter uses AI technology which may not be able to convert long or complex queries, triggers, packages, or stored procedures. Some queries may not be converted correctly while others may not be converted at all. For more information, see AI and Data Usage Information.
The query converter uses the relational schema, the MongoDB schema, and the mapping rules in your current project to determine how the queries should be converted. Conversions may fail or be incorrect if the queries reference tables that are not in your relational schema or if they are not mapped to MongoDB collections.
Converted queries, triggers, views, packages, and stored procedures are saved in your project and persist through project import and exports.
SQL queries are limited to 40,000 text characters.

You can view the history of previous conversions in the left-hand Query Converter pane. Each conversion has an icon indicating the result of the conversion. If an object does not have an icon next to it, a conversion has not been attempted yet:

Icon	Description
Spinner	The conversion is now being executed.
Green check mark	The conversion was successful.
Red exclamation mark	The conversion failed on last attempt.

Before you Begin

MongoDB does not have an official synonym object type for the SQL stored procedure. Instead, MongoDB supports custom JavaScript and driver code that hosts the database logic. Part of your application modernization journey is to choose the programming language and hosting option that best serves your application needs. You can host your converted stored procedure code in your application or with MongoDB App Services Functions.
Always review and test the code generated by query converter before deploying it in a production environment.

Steps

Navigate to the query converter pane

From the Code Generation tab, click the Query Converter pane.

Open the query converter view

If it is your first time using the query converter in your project, click Import From Database.
If your project already has converted SQL code, click the Manage Database Objects button on the left-hand pane.

Connect to your relational database

Specify the connection details to your source database.
For details, see Relational Database Connection Strings.
Click Connect.

Select stored procedures

On the Import Database Objects modal, click the icon next to Database.
Click the icon next to your schema.
Click the icon next to Stored Procedures.
Tip
To toggle stored procedures for conversion, click the icon next to the stored procedure's name.
Click Save.
The code for each stored procedure in your database schema is imported into your project and is visible in the Query Converter pane under Stored Procedures.

Convert and test code

Click a stored procedure's name from the left-hand pane under Stored Procedures.
The code displays in the Imported Stored Procedure pane.
Select a Target Language from the drop-down.
Query converter supports stored procedure conversions to Javascript, C#, and Java.
Note
If you use Java, you can also select Return a domain object where possible. When selected, the query converter includes strongly typed Java entities based on POJO classes if the query only operates on a single MongoDB collection.
Click the Convert button. Wait for the query converter to convert your code.
The converted MongoDB code displays on the Converted MongoDB Query pane.
If the query converter has errors, you can view the details in the Converted MongoDB Query pane.
Click the icon on the Converted MongoDB Query pane to copy the MongoDB code to your clipboard.
Test and validate the generated MongoDB code.
Tip
You can use the Filter text box to filter queries, stored procedures, triggers, packages, and views based on object name and SQL syntax.

Examples

The query converter can convert your stored procedures to Javascript, C#, or Java code. The examples below show conversions to each language.

JavaScript

The following example shows a MySQL stored procedure converted to JavaScript:

BEGIN
   DECLARE v_rentals INT;
   DECLARE v_out       INT;
   SELECT COUNT(*) INTO v_rentals
   FROM rental
   WHERE inventory_id = p_inventory_id;
   IF v_rentals = 0 THEN
      RETURN TRUE;
   END IF;
   SELECT COUNT(rental_id) INTO v_out
   FROM inventory LEFT JOIN rental USING(inventory_id)
   WHERE inventory.inventory_id = p_inventory_id
   AND rental.return_date IS NULL;
   IF v_out > 0 THEN
      RETURN FALSE;
   ELSE
      RETURN TRUE;
   END IF;
END

async function query(db, p_inventory_id) {
const v_rentals = await db.collection('rental').countDocuments(
   { inventoryId: p_inventory_id }
);
if (v_rentals === 0) {
   return true;
}
const v_out = await db.collection('inventory').aggregate([
   {
      $lookup: {
      from: 'rental',
      localField: 'inventoryId',
      foreignField: 'inventoryId',
      as: 'rentalData'
      }
   },
   {
      $match: {
      inventoryId: p_inventory_id,
      'rentalData.returnDate': { $exists: false }
      }
   },
   {
      $count: 'rentalId'
   }
   ]).toArray();
   if (v_out.length > 0 && v_out[0].rentalId > 0) {
      return false;
   } else {
      return true;
   }
   }

C#

The following example shows a MySQL stored procedure converted to C#:

BEGIN
      DECLARE v_customer_id INT;
      DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL;
      SELECT customer_id INTO v_customer_id
      FROM rental
      WHERE return_date IS NULL
      AND inventory_id = p_inventory_id;
      RETURN v_customer_id;
   END

async Task<int?> Query(IMongoDatabase db)
{
   var rentalCollection = db.GetCollection<BsonDocument>("rental");
   var filter = Builders<BsonDocument>.Filter.Eq("inventoryId", p_inventory_id) & Builders<BsonDocument>.Filter.Eq("returnDate", null);
   var result = await rentalCollection.Find(filter).FirstOrDefaultAsync();
   return result != null ? result["customerId"].AsInt32 : (int?)null;
}

Java

The following example shows a MySQL stored procedure converted to Java:

BEGIN
   SELECT inventory_id
   FROM inventory
   WHERE film_id = p_film_id
   AND store_id = p_store_id
   AND NOT inventory_in_stock(inventory_id);
   SELECT COUNT(*)
   FROM inventory
   WHERE film_id = p_film_id
   AND store_id = p_store_id
   AND NOT inventory_in_stock(inventory_id)
   INTO p_film_count;
END

void query(MongoDatabase db) {
   int p_film_id = 0;
   int p_store_id = 0;
   int p_film_count = 0;
   MongoCollection<Document> inventoryCollection = db.getCollection("inventory");
   Bson filter = Filters.and(Filters.eq("filmId", p_film_id), Filters.eq("storeId", p_store_id), Filters.not(inventory_in_stock("inventoryId")));
   FindIterable<Document> result = inventoryCollection.find(filter);
   for (Document doc : result) {
      System.out.println(doc.getInteger("inventoryId"));
   }
   p_film_count = (int) inventoryCollection.countDocuments(filter);
}

Next Steps

Learn More

Back

Convert Queries

Convert Oracle Packages