Advanced Find or Create (Example)

This is an extended example that creates an advanced and interactive lookup using a stored procedure. This same procedure is called repeatedly by dbFront with input from the self-created custom dialogue and responses from the web requests until the user is satisfied or the task is complete.

This 300-line procedure is much simpler than it looks. You can see this example in action on the demo environment. Simply log in and click the button labelled: ISBN Catalog: Book Lookup

NOTE: This example depends on functionality found in dbFront 1.4.1.

Steps:

On its initial run, this procedure tells dbFront to prompt for an ISBN.
If an ISBN is received, then query for the book, and if found, redirect the user to the record.
If no book is found, ask the user if it should query Open Library or Google to look for results.
If requested, ask dbFront to get the results of a WebRequest and process the results.
If the book is found, prompt the user with the book details and ask to "Save to Database".
Allow the user to query either Open Library or Google again to compare the results.
If "Save To Database" is clicked, then save the details and redirect the user to the new entry.
If no book was found, then show the user an error message and offer Manual Entry.
If the user clicks "Cancel," then exit the dialogue.

ISBN Catalog Table

The first requirement is to create an ISBN Catalog Table. This is used for the initial search and also to store any newly created entries.

CREATE TABLE [dbo].[ISBNCatalog](

		    [ISBN] [varchar](13) NOT NULL,

		    [Title] [varchar](256) NULL,

		    [SubTitle] [varchar](256) NULL,

		    [Description] [varchar](2048) NULL,

		    [Publisher] [varchar](256) NULL,

		    [Authors] [varchar](2048) NULL,

		    [CoverPageUrl] [varchar](1024) NULL,

		    [WebPageUrl] [varchar](1024) NULL,

		    [RequestStatus] [varchar](50) NULL,

		    [ISBNResult] [nvarchar](max) NULL,

		 CONSTRAINT [PK_ISBNCATALOG] PRIMARY KEY CLUSTERED ( [ISBN] ASC )

		)

The Procedure

Below is the full procedure, but with the code sections clearly delimited and explained. The procedure contains three main sections:

Initialization: Constants and Inputs,
Processing: Process specific requests (button clicks or web request results)
Finalize: Prepare the dialogue, redirect or exit.

1. Initialization

1.1. Parameters

The procedure is created with two special parameters @dbfHeader and @dbfData that allow dbFront to pass the state information and the results from the previous request. For more details see: API Fields

The first section within the procedure does a quick check to ensure that the procedure is given JSON data as expected.

CREATE PROCEDURE [dbo].[spISBNLookupUI] (@dbfHeader nvarchar(MAX) = null, @dbfData nvarchar(MAX) = null)

		AS

		BEGIN

		    SET NOCOUNT ON;

		    IF (ISJSON(@dbfHeader,object) = 0) BEGIN

		        ;THROW 51000, 'The Header JSON is invalid', 1; 

		        return;

		    END ELSE IF (ISJSON(@dbfData,object) = 0) BEGIN

		        ;THROW 51000, 'The Data JSON is invalid', 1;

		        return;

		    END;

1.2. Parse Header Fields

The next block of code parses the JSON header @dbfHeader and extracts the status information. In T-SQL, using OPENJSON is ideal since it allows multiple elements to be extracted with a single read. It is also important to be aware that OPENJSON can handle larger values. JSON_VALUE() will return NULL for any values larger than 4000 characters.

    -- Extract the dbFront Header fields.

		    DECLARE @handle as nvarchar(100);

		    DECLARE @requestType as nvarchar(100);

		    DECLARE @requestSource as nvarchar(2048);

		    DECLARE @eventMessage as nvarchar(100);

		    DECLARE @eventStatus as nvarchar(100);

		    DECLARE @responseStatus nvarchar(7);

		    DECLARE @debug as bit;

		    SELECT 

		        @handle = IsNull(j.handle, lower(Format(CAST(CRYPT_GEN_RANDOM(32) AS bigint),'X'))),

		        @requestType = j.requestType,

		        @requestSource = j.requestPath,

		        @eventStatus = j.eventStatus,

		        @eventMessage = j.eventMessage,

		        @responseStatus = j.responseStatus,

		        @debug = j.debug

		    FROM OPENJSON(@dbfHeader) WITH (

		        handle varchar(100) '$.Handle',

		        requestType varchar(100) '$.Request.Type',

		        requestPath varchar(100) '$.Request.Source',

		        eventMessage varchar(100) '$.Message',

		        eventStatus varchar(100)  '$.Status',

		        responseStatus varchar(7) '$.Response.StatusCode',

		        debug bit '$.Response.Debug'

		    ) j;

1.3. Field and Button Constants

Using specific field and button constants may seem more verbose, but it enables field names and button prompts to be safely changed without breaking the procedure.

    -- Button Constants

		    DECLARE @BUTTON_LOOKUP_LOCAL as varchar(32) = 'Database Lookup';

		    DECLARE @BUTTON_QUERY_OPENLIBRARY as varchar(32) = 'Query OpenLibrary';

		    DECLARE @BUTTON_QUERY_GOOGLE as varchar(32) = 'Query Google';

		    DECLARE @BUTTON_SAVE_ENTRY as varchar(32) = 'Save To Database';

		    DECLARE @BUTTON_CANCEL as varchar(32) = 'Cancel';

		    -- Field Constants

		    DECLARE @FORMFIELD_ISBNNumber as varchar(32) = 'ISBN Number';

		    DECLARE @FORMFIELD_InventoryCount as varchar(32) = 'Inventory Count';

		    DECLARE @FORMFIELD_Title as varchar(32) = 'Title';

		    DECLARE @FORMFIELD_SubTitle as varchar(32) = 'Subtitle';

		    DECLARE @FORMFIELD_Description as varchar(32) = 'Description';

		    DECLARE @FORMFIELD_Authors as varchar(32) = 'Authors';

		    DECLARE @FORMFIELD_Publisher as varchar(32) = 'Publisher';

		    DECLARE @FORMFIELD_CoverUrl as varchar(32) = 'Cover Url';

		    DECLARE @FORMFIELD_WebPageUrl as varchar(32) = 'Page Url';

		    DECLARE @FORMFIELD_ReponseData as varchar(32) = 'Response Data';

		    DECLARE @FORMFIELD_ReponseStatus as varchar(32) = 'Response Status';

		    DECLARE @FORMFIELD_ReponseSource as varchar(32) = 'Hidden-ResponseSource';

1.4. Parse the Form Fields from the JSON data

Although OPENJSON is more efficient, I choose to use JSON_VALUE because it allows me to use the predefined field name constants. It is only for the ResponseData field that I revert to OPENJSON since the field can exceed 4000 characters.

I use TRY_CAST instead of TRY CATCH when validating input because TRY CATCH causes issues with resuming transactions.

NOTE: If @formData is null, then the variables are simply initialized as defined.

    -- WARNING WARNING WARNING: Avoid TSQL Error Handling, use try_cast to validate any input.

		    -- Extract the form values returned to the procedure.

		    DECLARE @formData as nvarchar(MAX) = JSON_QUERY(@dbfData,'$.FormData');

		    DECLARE @fieldISBNNumber as nvarchar(50) =

		                        replace(replace(trim(JSON_VALUE(@formData,concat('$."', @FORMFIELD_ISBNNumber,'"'))),' ',''),'-','');

		    DECLARE @fieldInventoryCount as int =

		                        try_cast(JSON_VALUE(@formData,concat('$."', @FORMFIELD_InventoryCount, '"')) as int);

		    DECLARE @fieldTitle as nvarchar(50) =

		                        trim(JSON_VALUE(@formData,concat('$."', @FORMFIELD_Title,'"')));

		    DECLARE @fieldSubTitle as nvarchar(50) =

		                        trim(JSON_VALUE(@formData,concat('$."', @FORMFIELD_SubTitle,'"')));

		    DECLARE @fieldDescription as nvarchar(1024) =

		                        trim(JSON_VALUE(@formData,concat('$."', @FORMFIELD_Description,'"')));

		    DECLARE @fieldAuthors as nvarchar(1024) =

		                        trim(JSON_VALUE(@formData,concat('$."', @FORMFIELD_Authors,'"')));

		    DECLARE @fieldPublisher as nvarchar(1024) =

		                        trim(JSON_VALUE(@formData,concat('$."', @FORMFIELD_Publisher,'"')));

		    DECLARE @fieldCoverPageUrl as nvarchar(1024) =

		                        trim(JSON_VALUE(@formData,concat('$."', @FORMFIELD_CoverUrl,'"')));

		    DECLARE @fieldWebPageUrl as nvarchar(1024) =

		                        trim(JSON_VALUE(@formData,concat('$."', @FORMFIELD_WebPageUrl,'"')));

		    DECLARE @fieldResponseStatus as nvarchar(7) =

		                        trim(JSON_VALUE(@formData,concat('$."', @FORMFIELD_ReponseStatus,'"')));

		    DECLARE @fieldResponseSource as nvarchar(12) =

		                        trim(JSON_VALUE(@formData,concat('$."', @FORMFIELD_ReponseSource,'"')));

		    DECLARE @fieldResponseData as nvarchar(MAX);  -- JSON_VALUE() fails when more than 4000 characters.

		    SELECT 

		        @fieldResponseData = data

		    FROM OPENJSON(@dbfData,'$.FormData') WITH (

		        -- WARNING: Can't use a variable... Ensure it matches @FORMFIELD_ReponseData

		        data nvarchar(MAX) '$."Response Data"'  

		    );

1.5. Final Initialization

The following section specifies the final initialization, including defining the request stages and determining which button was clicked.

    -- WebRequest Params used for both request and key extraction.

		    DECLARE @WEBREQUEST_PARAM_GOOGLE varchar(200) = '?q=isbn:';

		    DECLARE @WEBREQUEST_PARAM_OPENLIBRARY varchar(200) = '&bibkeys=ISBN:';

		    -- Stage Constants

		    DECLARE @STAGE_LOOKUP_DATABASE as int = 0;

		    DECLARE @STAGE_LOOKUP_ONLINE_DIALOG as int = 1;

		    DECLARE @STAGE_ACTION_SENT as int = 2;

		    DECLARE @STAGE_SAVE_RESULTS as int = 3;

		    DECLARE @STAGE_SAVE_COMPLETE as int = 4;

		    -- Working Status Output fields.

		    DECLARE @message as varchar(1024) = null;

		    DECLARE @errorMessage as varchar(1024) = null;

		    DECLARE @openURL as varchar(1024) = null;

		    DECLARE @buttonClicked as varchar(100) =

		                      case when @requestType = 'DialogButton' then @requestSource else null end;

		    -- Default Stage

		    DECLARE @stage as int = @STAGE_LOOKUP_DATABASE;

2. Processing

The first run of the procedure falls through to the bottom, where it draws the dialogue and asks for an ISBN. Subsequent runs of the procedure respond to specific user actions.

2.1. Button Click - Local Search

The first possible action is the local lookup. If the ISBN is found, then set the @openURL variable to the destination URL; otherwise, prepare the error message.

    IF @buttonClicked = @BUTTON_LOOKUP_LOCAL BEGIN

		        IF (@fieldISBNNumber is null)

		            SET @errorMessage = 'Please enter/scan a ISBN number to search';

		        IF (@errorMessage is null) BEGIN

		            IF exists(SELECT * FROM [dbo].[ISBNCatalog] i where i.ISBN = @fieldISBNNumber) BEGIN

		                SET @openURL = concat('default.aspx?k={%databasekey%}&t=ISBNCatalog#["',@fieldISBNNumber,'"]');

		                SET @message = 'Book Found.';

		            END ELSE BEGIN

		                SET @errorMessage = 'The ISBN Number was not found in the Database.';

		                SET @stage = @STAGE_LOOKUP_ONLINE_DIALOG;

		            END;

		        END;

2.2. Button Click - Online Search

These two checks test if the Open Library or the Google search was requested. If so, then it will return with a WebRequest action that tells dbFront to submit a WebRequest for data. For more details, see: WebRequest Action

    END ELSE IF @buttonClicked = @BUTTON_QUERY_OPENLIBRARY BEGIN

		        SELECT

		            'WebRequest' as Action

		            , concat('https://openlibrary.org/api/books?jscmd=data&format=json',

		                        @WEBREQUEST_PARAM_OPENLIBRARY, @fieldISBNNumber) as Url

		        set @stage = @STAGE_ACTION_SENT ;

		    END ELSE IF @buttonClicked = @BUTTON_QUERY_GOOGLE BEGIN

		        SELECT

		            'WebRequest' as Action

		            , concat('https://www.googleapis.com/books/v1/volumes',

		                        @WEBREQUEST_PARAM_GOOGLE, @fieldISBNNumber) as Url;

		        set @stage = @STAGE_ACTION_SENT ;

2.3. Process WebRequests Responses

This section is called when dbFront returns the WebRequest data to the procedure. The procedure then parses the results into the Form Fields.

    END ELSE IF @requestType = 'WebRequest' BEGIN

		        -- Parse the JSON WebRequest results

		        IF charindex('openlibrary.org', @requestSource) > 0 BEGIN

		            -- Extracted the key from requestPath (the webrequest url), the result might be empty.

		            set @fieldISBNNumber = SUBSTRING(

		                        @requestSource,

		                        CHARINDEX(@WEBREQUEST_PARAM_OPENLIBRARY, @requestSource)

		                         + LEN(@WEBREQUEST_PARAM_OPENLIBRARY),

		                        LEN(@requestSource));

		            -- Get the Key from first element

		            DECLARE @isbnPath as varchar(30) = null;

		            SELECT @isbnPath = concat('$."ISBN:', @fieldISBNNumber,'"');

		            -- Get the rest of the fields

		            set @fieldTitle = JSON_VALUE(@dbfData, concat(@isbnPath,'.title'));

		            set @fieldAuthors = (

		                Select STRING_AGG( author_name ,', ') 

		                From  OpenJSON(JSON_QUERY(@dbfData, concat(@isbnPath,'.authors')),'$')

		                With(author_name nvarchar(256) '$.name'));

		            set @fieldPublisher = (

		                Select STRING_AGG( publisher_name ,', ') 

		                From  OpenJSON(JSON_QUERY(@dbfData, concat(@isbnPath,'.publishers')),'$')

		                With(publisher_name nvarchar(256) '$.name'));

		            set @fieldCoverPageUrl = JSON_VALUE(@dbfData, concat(@isbnPath,'.cover.large'));

		            set @fieldWebPageUrl = JSON_VALUE(@dbfData, concat(@isbnPath,'.url'));

		            set @fieldResponseStatus = @responseStatus;

		            set @fieldResponseData = @dbfData;

		            set @fieldResponseSource = 'OpenLibrary';

		            set @stage = @STAGE_SAVE_RESULTS;

		        END ELSE IF charindex('googleapis.com', @requestSource) > 0 BEGIN

		            -- Extracted the key from requestPath (the webrequest url), the result might be empty.

		            set @fieldISBNNumber = SUBSTRING(

		                        @requestSource,

		                        CHARINDEX(@WEBREQUEST_PARAM_GOOGLE, @requestSource)

		                         + LEN(@WEBREQUEST_PARAM_GOOGLE),

		                        LEN(@requestSource));

		            DECLARE @googlePath as varchar(30) = '$.items[0].volumeInfo';

		            set @fieldTitle = JSON_VALUE(@dbfData, concat(@googlePath,'.title'));

		            set @fieldSubTitle = JSON_VALUE(@dbfData, concat(@googlePath,'.subtitle'));

		            set @fieldDescription = JSON_VALUE(@dbfData, concat(@googlePath,'.description'));

		            Select @fieldAuthors = STRING_AGG( value ,', ') 

		                From  OpenJSON(JSON_QUERY(@dbfData, concat(@googlePath,'.authors')),'$');

		            set @fieldPublisher = JSON_VALUE(@dbfData, concat(@googlePath,'.publisher'));

		            -- The Google coverpage is rejected in an IFrame so you much set the URL Display type to "Image"

		            set @fieldCoverPageUrl = JSON_VALUE(@dbfData, concat(@googlePath,'.imageLinks.thumbnail'));

		            set @fieldWebPageUrl = JSON_VALUE(@dbfData, concat(@googlePath,'.previewLink'));

		            set @fieldResponseStatus = @responseStatus;

		            set @fieldResponseData = @dbfData;

		            set @fieldResponseSource = 'Google';

		            set @stage = @STAGE_SAVE_RESULTS;

		        END ELSE BEGIN

		            set @errorMessage = 'Unrecognized JSON response.';

		        END;

		        -- Validate the WebRequest results.

		        IF @errorMessage is null BEGIN

		            IF @fieldTitle is null BEGIN

		                set @errorMessage = concat('No Book information found at ',@fieldResponseSource);

		            END;

		        END;

2.4. Button Click - Cancel

At any point, the user can click on the Cancel button to abandon the search. The procedure could return nothing, and then the process will exit and return with the Action Buttons' default success message. Or you can return a Close action, which allows you to specify a custom message and refresh instructions. See: Close Action

    END ELSE IF @buttonClicked = @BUTTON_CANCEL BEGIN

		        select 'close' as action, 'Lookup cancelled.' as message, 'screen' as refresh;

		        SET @stage = @STAGE_ACTION_SENT;

2.5. Button Click - Save Entry

If the user clicks "Save Entry" then insert the new record and set up the new @openURL so that the procedure can direct the user to the new record.

NOTE: Nothing is currently done with the inventory count, but that is because it is part of another

    END ELSE IF @buttonClicked = @BUTTON_SAVE_ENTRY BEGIN

		        INSERT INTO [dbo].[ISBNCatalog]

		            ([ISBN],[Title],[SubTitle],[Description],[Authors],[Publisher],

		             [CoverPageUrl],[WebPageUrl],[ISBNResult],[RequestStatus])

		        VALUES

		            (@fieldISBNNumber,@fieldTitle,@fieldSubTitle,@fieldDescription,@fieldAuthors,@fieldPublisher,

		            @fieldCoverPageUrl,@fieldWebPageUrl,@fieldResponseData,@fieldResponseStatus);

		        SET @openURL = concat('default.aspx?k={%databasekey%}&t=ISBNCatalog#["',@fieldISBNNumber,'"]');

		        SET @message = 'Save Complete!';

		        SET @stage = @STAGE_SAVE_COMPLETE;

		    END;

3. Finalize

3.1. Early Exit

If the Action was already sent, then exit the procedure.

    IF (@stage = @STAGE_ACTION_SENT) BEGIN

		        return;

3.2. Redirect User and Exit

If the @openURL variable was set, then exit and tell dbFront to redirect the user to the specified URL. See: OpenUrl

    END ELSE IF (@openURL is not null) BEGIN

		        select 'openurl' action, @openUrl url, @message as Message, @handle as Handle;

3.3. Display Dialogue and Exit

If the procedure reaches this point, then show the user a dialogue and prompt for any missing information or allow the user to click available buttons.

This closing select statement tells dbFront to draw a dialogue with the specified form fields and buttons. The [Fields] field is set to "next" which tells dbFront to look for the field list in the following result set. See: OpenDialog

    END ELSE BEGIN

		        DECLARE @headerHtml varchar(max) = concat(

		            '<h1>Book Lookup: ',

		            CASE 

		                WHEN @fieldISBNNumber is null THEN 'Scan or Enter the ISBN Number'

		                WHEN @stage = @STAGE_LOOKUP_ONLINE_DIALOG THEN 'Online Query'

		                ELSE 'Query Again or Complete Manually'

		            END,

		            '</h1>',

		            '<h3>See: <a href="https://www.dbFront.com/procedureapi#examples" target="_blank">Procedure API</a> for implementation details</h3>',

		            '<hr>');

		        DECLARE @coverHtml nvarchar(MAX) = CASE WHEN @fieldCoverPageUrl is null THEN null ELSE concat(

		            '<td style="width:1px;vertical-align:top"><img src="',@fieldCoverPageUrl,'" alt="Book Cover Image" style="max-width:250px; max-height:300px;"/></td>'

		        ) END;

		        DECLARE @messageHtml nvarchar(MAX) = concat(

		            '<table style="width:100%"><tr>'

		            , @coverHtml

		            , '<td>{%FormFields%}</td>'

		            , '<h2 style="color:red">', @errorMessage,'</h2>'

		            );

		        SELECT

		            'OpenDialog' as Action

		            , concat(

		                @headerHtml,

		                @messageHtml) as Message

		            , 'fullScreenDialog' as ClassList

		            , 'next' as Fields

		            , concat(

		                '{"', @BUTTON_CANCEL,'":null'

		                , case when @stage = @STAGE_LOOKUP_DATABASE then concat(',"', @BUTTON_LOOKUP_LOCAL,'":null') end

		                , case when @stage = @STAGE_LOOKUP_ONLINE_DIALOG or  @stage = @STAGE_SAVE_RESULTS

		                           then concat(',"', @BUTTON_QUERY_OPENLIBRARY,'":null') end

		                , case when @stage = @STAGE_LOOKUP_ONLINE_DIALOG or  @stage = @STAGE_SAVE_RESULTS

		                           then concat(',"', @BUTTON_QUERY_GOOGLE,'":null') end

		                , case when @stage = @STAGE_SAVE_RESULTS then concat(',"', @BUTTON_SAVE_ENTRY,'":null') end

		                ,'}') as Buttons

		            , @handle as Handle;

In this case, I use a separate result set to list out the fields. It is possible to list out the fields inline, but if you need to specify the current field values or other attributes, then you will need to use a separate result set. See: OpenDialog

        -- Return the List of Fields

		        DECLARE @hiddenFieldFlags varchar(32) = case when @debug > 0 then 'Readonly' else 'Hidden' end;

		        DECLARE @showDetailFields int =

		                case when @requestType = 'WebRequest' or @stage = @STAGE_SAVE_RESULTS then 1 else 0 end;

		        SELECT

		            1 as FieldOrder,

		            @FORMFIELD_ISBNNumber AS FieldCaption, 

		            'varchar(50)' as FieldType, 

		            cast(@fieldISBNNumber as varchar) as FieldValue, 

		            case when @fieldISBNNumber is null or @fieldISBNNumber='' then 'Required' else 'Readonly' end as FieldOptions

		        UNION SELECT 2, @FORMFIELD_Title, 'varchar(50)', @fieldTitle, 'Required' WHERE @showDetailFields = 1

		        UNION SELECT 3, @FORMFIELD_SubTitle, 'varchar(50)', @fieldSubTitle, null WHERE @showDetailFields = 1

		        UNION SELECT 4, @FORMFIELD_Description, 'varchar(50)', @fieldDescription, null WHERE @showDetailFields = 1

		        UNION SELECT 5, @FORMFIELD_Authors, 'varchar(1024)', @fieldAuthors, null WHERE @showDetailFields = 1

		        UNION SELECT 6, @FORMFIELD_Publisher, 'varchar(1024)', @fieldPublisher, null WHERE @showDetailFields = 1

		        UNION SELECT 7, @FORMFIELD_CoverUrl, 'varchar(1024)', @fieldCoverPageUrl, null WHERE @showDetailFields = 1

		        UNION SELECT 8, @FORMFIELD_WebPageUrl, 'varchar(1024)', @fieldWebPageUrl, null

		                     WHERE @showDetailFields = 1

		        UNION SELECT 9, @FORMFIELD_ReponseData, 'varchar(MAX)', @fieldResponseData, @hiddenFieldFlags

		                     WHERE @showDetailFields = 1

		        UNION SELECT 10, @FORMFIELD_ReponseStatus, 'varchar(7)', @fieldResponseStatus, @hiddenFieldFlags

		                     WHERE @showDetailFields = 1

		        UNION SELECT 11, @FORMFIELD_ReponseSource, 'varchar(12)', @fieldResponseSource, @hiddenFieldFlags

		                     WHERE @showDetailFields = 1

		        UNION SELECT 12, @FORMFIELD_InventoryCount, 'int', cast(@fieldInventoryCount as varchar), null

		        ORDER BY FieldOrder; -- The Field Sequence is unpredictable without a sort.

		    END

		END;

dbFront Configuration

Once the table and the procedure are configured, there is only a small bit of dbFront configuration. All of the business logic and custom UI are defined in the stored procedure.

1. Create a new RunProcedure Action Button for the spISBNLookupUI stored procedure with the following settings:

Main Tab
- Caption: "Book Lookup"
- Action Type: Run Procedure
Procedure Tab
- Procedure: "spISBNLookupUI"
- Parameters: {dbfsp_header%}, {dbfsp_data%}
- Output: Automatic

2. Update the Cover Page Url field properties so that it displays the Cover Page link as an inline image:

Main Tab
- Subtype: URL
Url Tab
- Display: Image

Usage

The Procedure API allows you to add highly customized and complex business logic and user interactions to your database applications while leaving the technical aspect of web development to dbFront.