Further Info for users of the MySQL Pipe

App Development
1

Last week I posted my experiences with the MySQL pipe, and if you are try to use the pipe it might be worth a read …

At the end I said that I had reported the fact that the Update pipe did not work, and I thank @Tim and @Moe for pitching in to help.

In the end I succeeded in getting the Pipe to work, but only by modifying a lot of the settings and ignoring parts of the manual. If you are planning or trying to use this Pipe it may be worth a read so that you don’t have to put in the hours I did.

I finally have this working using the original Update Pipe - but only after I have made some substantial changes to the Pipe as shipped AND ignored some of the info that is given on the Pipe help page .

Basically the concept that was used in the Pipe, as shipped and in the help page, describe Updates that are created using a {recordData} field which is a combination of field and value. This is a flawed idea in two ways - firstly it doesn’t work at all, and secondly because its not ever going to give the felxibility we’d need to develop an App.

If you think about a record update, you can target the Database and the field with custom field values that are fixed for each run. But the Index row that you are targetting and the data value you are passing are likely to be record variables. Therefore a combined {recordData} field that has both target field and value will need to be preprocessed at runtime somehow for the {recordData} to hold anything useful… for example you might have to create a new formula field to concatenate the text needed to create the {recordData} string. This is not a great approach.

What I did was to separate out the fieldName and fieldValue into (at least) two new Parameter fields, and changed the Request to reflect that (see below).

What I also realised is that Numeric Value fields need to presented differently to TextValue fields, and so I have a Pipe that passes both a field/numeric pair and field/txt pair at same time. (NB: sometimes the Pipe runs if only one of the field/value pairs in present, but always include a numeric field).

I created new Parameters fieldName1, fieldName2,numValue1,txtValue2
and editde the Request Data part from {{recordData}} to {“{fieldName1}”:{numValue1}, “{fieldName2}”:“{txtValue2}”}

It works and I am happy. But I now realise that some of the other Pipes in this MySQL set are suboptimal too. For example the ‘Filter’ pipes use a Parameter called ‘filter’. This value for filter is actually made up of 3 descrete elements that are even listed in the help page - filterField,filterOperator,filteredValue Combining these into a single field called {filter} does not seem right, and it would be better to have each of these as Parameter in their own right.

That would mean … “action” : “/records/{tableName}?filter={filter}”
Becomes … … “action” : “/records/{tableName}?filter={filterField},{filterOperator},{filteredValue}”

I hope that this is clear and helps you too.

2

Martin, thanks so much for this post. Great insight and certainly we need to make this pipe and the docs better.

However, i wanted to show you potentially a better way to do this by explaning how to proxy from Tadabase Pipes to your Joomla database.

Was a bit much to write up, so I made you a video:

Here is the PHP file I used in my example. Remember this can be done in nearly any language/framework. Drop this into ChatGPT and it can do the conversion for you in seconds.

Here is my PHP file from my demo:

<?php

header('Content-type: application/json');

$host = "your_hostname"; 
$dbUsername = 'your_username';
$dbPassword = 'your_very_secure_password';
$dbName = 'database_name';

// Create database connection
$conn = new mysqli($host, $dbUsername, $dbPassword, $dbName);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} else {
    // echo "Connected successfully";
}

$data = file_get_contents('php://input');

// Your query
$query = $data;

$result = $conn->query($query);

// Create database connection
$conn = new mysqli($host, $dbUsername, $dbPassword, $dbName);

// Check connection
if ($conn->connect_error) {
    die(json_encode(["error" => "Connection failed: " . $conn->connect_error]));
}

// Determine the type of query
$queryType = strtoupper(explode(' ', trim($data))[0]);

// Execute the query
$result = $conn->query($data);

if ($result) {
    switch ($queryType) {
        case 'SELECT':
            // For SELECT queries, return the fetched data
            $output = [];
            while ($row = $result->fetch_assoc()) {
                $output[] = $row;
            }
            echo json_encode($output);
            break;
        case 'INSERT':
            // For INSERT queries, return the ID of the inserted record
            echo json_encode(["insert_id" => $conn->insert_id]);
            break;
        case 'UPDATE':
        case 'DELETE':
            // For UPDATE and DELETE queries, return the number of affected rows
            echo json_encode(["affected_rows" => $conn->affected_rows]);
            break;
        case 'CREATE':
            // For CREATE queries, just return a success message
            echo json_encode(["message" => "Query executed successfully"]);
            break;
        default:
            // If the query type is not recognized
            echo json_encode(["error" => "Unrecognized query type"]);
    }
} else {
    // If the query failed, return an error message
    echo json_encode(["error" => "Query failed: " . $conn->error]);
}

// Close the connection
$conn->close();

?>

I’d be happy to help further if there’s anything I can assist with.

3

Thanks Moe, that’s fabulous.

And I’m sure there are others in the Tadabase community, like me, for whom this hard. I had wondered how to create my own Pipe, as this will indeed allow me to create as many query variants as I need. ‘I had wondered’ but had no clue how to do it. I will certainly follow your example and get back to you if I need help.

One area where I know I already have an issue and am not sure if there is an easy answer, is what to do when then database returns and array (a table even), and what I’d like to do is show it. I know this what you call a databridge, but In the absence of a databridge for MySQL I wonder if there is an easy way to display (for example) the array of names and emails you demoed in a simple table?

Regards, and thanks for the stellar support,
Martin

4

Thanks Moe I now have this working, and its great to be able to create and use any query I want SELECT and UPDATE our CMS system from with our Tadabase App.

I have even managed work with ChatGPT and create a javascript to be able to render the resultant Json, from queries, as regular HTML tables.

But one concern I have is the Proxy script.

It uses no additional security, and so anyone who knew or found the URL of the proxy script has a backdoor straight into MySQL. I know that the Pipe script has various authentication options, but don’t understand how I can use any of them in this scenario. I wonder if you could advise?

Regards
Martin