Do we have stored procedure in Couchbase i dont want to write N1QL inline query in my code

SQL++
1

Hi is there any way i can write my function in Couchbase and invoke that from code .NET SDK.
Ex- Search a city by postal code without writing inline N1QL query just invoke stored procedure and pass postal code as parameter and we should get the city.

2

Hi @kumas16,

The closest thing to that right now in Couchbase is probably User-Defined Functions (UDFs). You can try these out in the 6.5 beta release of Couchbase Server, but please note that this is a Developer Preview feature only.

3

You can implement it via INLINE functions, released in Couchbase 6.5 (dev-preview)

4

For production cases, you can also PREPARE a statement with the actual code as a parameter and execute that statement with various bind values.

5

Can we pass bind values dynamically to prepare statement for ex-
PREPARE select meta().id,adP2dNa,p1dNa FROM TEST WHERE adPriPslCd = ‘75038’
is there any way i can pass adPriPslCd dynamically from code using .NET SDK? if yes could you please share the code snippet for the same.

6

https://docs.couchbase.com/dotnet-sdk/2.7/n1ql-query.html#prepare-stmts

7

Checkout Couchbase 6.5, currently in Beta. This is a developer preview feature now:
https://docs.couchbase.com/server/6.5/n1ql/n1ql-language-reference/userfun.html

8

Hi Shani,

Expanding on Keshav’s comment about functions I want to point out that there are two (2) variants of N1QL JavaScript functions available in the 6.5 DP.

The first type are simple inline functions as Keshav pointed out (think of pure math with no intermediate variables)

The second type allows you to build custom libraries for much more complex external functions (as in normal JavaScript with local variables). Below I’ll give you some examples of both types.

1) An example of an inline N1QL JavaScript function (basic):

Step 1 via the N1QL install an inline JavaScript function called feet_to_meters

CREATE FUNCTION feet_to_meters() { args[0] * 0.3048 };

Step 2 use your new function.

Given three feet is almost a meter

EXECUTE FUNCTION feet_to_meters(3);

You should see the following result:

0.9144000000000001

2) An example of an inline N1QL JavaScript function (complex):

CREATE FUNCTION inlineDistKmFromLatLon(lon1, lat1, lon2, lat2) {
  6371 * 2 * atan2(sqrt(
    (
      sin(((lat2-lat1) * (3.14159265359/180))/2) * 
      sin(((lat2-lat1) * (3.14159265359/180))/2) +
      cos((3.14159265359/180)*(lat1)) * cos((3.14159265359/180)*(lat2)) *
      sin(((lon2-lon1) * (3.14159265359/180))/2) * 
      sin(((lon2-lon1) * (3.14159265359/180))/2)
    )
 
  ), sqrt(1-
    (
      sin(((lat2-lat1) * (3.14159265359/180))/2) * 
      sin(((lat2-lat1) * (3.14159265359/180))/2) +
      cos((3.14159265359/180)*(lat1)) * cos((3.14159265359/180)*(lat2)) *
      sin(((lon2-lon1) * (3.14159265359/180))/2) * 
      sin(((lon2-lon1) * (3.14159265359/180))/2)
    )
  ))
};

Step 2 use your new inline function inlineDistKmFromLatLon.

Given two lon/lat city pairs:

Los Angles: -118.6925973,34.0207289,
New York: -74.1201062,40.6976633,

EXECUTE FUNCTION inlineDistKmFromLatLon( -118.6925973,34.0207289, -74.1201062,40.6976633);

You should see the following result:

3965.4458119106316

3) An example of an external N1QL JavaScript function, same as 2) above, note this involves multiple steps:

Step 1 create a payload with you JavaScript function for a library called math with function name externalDistKmFromLatLon via a Linux shell.

cat <<EOF > ./my_functon.json
{
  "name" : "math",
  "functions" : [ {
    "name" : "externalDistKmFromLatLon",
    "code": "function externalDistKmFromLatLon(lon1, lat1, lon2, lat2) {
    var PI = 3.14159265359;
    var deg2rad = PI/180;
    var R = 6371; /* Radius of the earth in km */
    var dLat = deg2rad*(lat2-lat1);
    var dLon = deg2rad*(lon2-lon1);
    var a = Math.sin(dLat/2) * Math.sin(dLat/2) +
            Math.cos(deg2rad*(lat1)) * Math.cos(deg2rad*(lat2)) * 
            Math.sin(dLon/2) * Math.sin(dLon/2) ;
    var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
    var d = R * c; /* Distance in km */
    return d ; }"
  } ]
}
EOF

Step 2 install the above payload into the library math (this loads the file you just created into the cluster)

curl -v -X POST \
  http://localhost:8093/functions/v1/libraries/math \
  -u admin:jtester \
  -H 'content-type: application/json' \
  -d @./my_functon.json

Step 3 in N1QL bind the math library function externalDistKmFromLatLon into N1QL as a function called JSgeoDistKm

CREATE FUNCTION JSgeoDistKm() LANGUAGE JAVASCRIPT AS { "math", "externalDistKmFromLatLon"};

Step 4 use your new function JSgeoDistKm via your alias.

Given two lon/lat city pairs:

Los Angles: -118.6925973,34.0207289,
New York: -74.1201062,40.6976633,

EXECUTE FUNCTION JSgeoDistKm(-118.6925973, 34.0207289, -74.1201062, 40.6976633);

You should see the following result (identical to the earlier inline function):

3965.4458119106316

Note the this external library form (directly above) is much more readable and should be faster than the internal form due to eliminating repetitive calculations via the use of local variables.

For more information refer to the following:
https://docs.couchbase.com/server/6.5/n1ql/n1ql-language-reference/userfun.html
https://docs.couchbase.com/server/6.5/n1ql/n1ql-language-reference/createfunction.html#examples
https://docs.couchbase.com/server/6.5/n1ql/n1ql-rest-api/functions.html

1 Like