× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



here is my version. works in mysql 5.7

JSON_OBJECT is kind of a weird syntax. Can I just pass in a json
formatted string? {propName:'vlu', prop2:123}

call objectDefn_select( json_object('objName','e', 'objType', 'function'))

Create procedure objectDefn_select(
in inWhere json
)
Language SQL

BEGIN

declare inObjName varchar(256);
declare inObjType varchar(10);

set inObjName = coalesce(json_unquote(json_extract( inWhere ,
'$.objName')), '');
set inObjType = coalesce(json_unquote(json_extract( inWhere ,
'$.objType')), '');

select a.objName, a.databaseName, a.objType, a.object_id,
routine_exists( a.objName ) routineExists,
a.codeText
from ObjectDefn a
where ( inObjName = '' or lower(a.objName) like
concat('%',trim(lower(inObjname)), '%'))
and ( inObjType = '' or a.objType = inObjType ) ;

END



On Fri, Dec 13, 2019 at 12:26 PM Tim Fathers <X700-IX2J@xxxxxxxxxxx> wrote:

I meant to add that this was the MariaDB syntax not MySQL, I have a
feeling it's slightly different between the two.

"The pattern I follow is to have a stored procedure for
each table in my application. cusms_select( ), orderHeader_select( ) ...
Then the input parms are used when selecting rows from the table. Which
works ok when only a few parms are used. Using a single JSON parameter
makes it less intrusive to add an additional WHERE parameter."

I'm doing something similar, except the SP implements a RESTful CRUD+
webservice over the file, with request and response information being
passed in and out as JSON objects and the request and response bodies as
BLOBs which are interpreted/generated according to the content-type/accepts
headers.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.