Read data from external database

Questions
1

Silverstripe Version: 4 & 5

Question:

Hi, How can I connect a external database and query the data to show in page? I tried but it does not work. Can you guys give me some advise?

Besides, I noticed I can’t return the results with my code.
How can I improve my code?

class ProductPageController extends PageController

{

    public function index()

    {

        $userCountry = Security::getCurrentUser()->Country->Name;

        $countryItem = Country::get()->filter('Name', $userCountry)->first();

        $dbConfig = array(

                'type' => 'PostgreSQLDatabase',

                'server' => 'localhost',

                'username' => 'vv',

                'password' => 'cc!',

                'database' => 'gg',

                'port' => 5432,

            );

        // save the default config for SS backend database

        $defaultConfig = DB::getConfig()

        // connect external database

        DB::connect($dbConfig);

        $stations = DB::query("SELECT * FROM stations")->fetchAll();

        // switch back to SS backend database

        DB::connect($defaultConfig)

        $countryInfo = [

            "CountryName" => $countryItem->Name,

            "CountryCode" => $countryItem->Code,

            "Latitude" => $countryItem->Latitude,

            "Longitude" => $countryItem->Longitude,

            "Stations" => $stations,

        ];

        return $countryInfo;

    }

}
2

It seems like you’re trying to connect to an external PostgreSQL database and fetch some data to display on a Silverstripe page. There are a few issues and improvements that can be made in your code:

  1. Error Handling: Ensure you have proper error handling in case the database connection or query fails.
  2. Database Connection: Make sure the database connection is properly established and closed after the query.
  3. Returning Data: You’re returning data directly from the controller’s index method. In Silverstripe, typically you render data within a template. However, if you want to return data directly from the controller, you should use JsonResponse or similar methods.
  4. Security: Avoid directly using input data in your queries to prevent SQL injection attacks. However, if Security::getCurrentUser()->Country->Name is sanitized and trusted, it should be safe.

Here’s an improved version of your code:

use SilverStripe\Control\Controller;
use SilverStripe\Security\Security;
use SilverStripe\ORM\DB;
use SilverStripe\Control\HTTPResponse_Exception;

class ProductPageController extends PageController
{
    public function index()
    {
        try {
            $userCountry = Security::getCurrentUser()->Country->Name;
            $countryItem = Country::get()->filter('Name', $userCountry)->first();

            if (!$countryItem) {
                throw new HTTPResponse_Exception("Country not found", 404);
            }

            $dbConfig = [
                'type' => 'PostgreSQLDatabase',
                'server' => 'localhost',
                'username' => 'vv',
                'password' => 'cc!',
                'database' => 'gg',
                'port' => 5432,
            ];

            // Save the default config for SS backend database
            $defaultConfig = DB::getConfig();

            // Connect external database
            DB::connect($dbConfig);

            $stations = DB::query("SELECT * FROM stations")->fetchAll();

            // Switch back to SS backend database
            DB::connect($defaultConfig);

            $countryInfo = [
                "CountryName" => $countryItem->Name,
                "CountryCode" => $countryItem->Code,
                "Latitude" => $countryItem->Latitude,
                "Longitude" => $countryItem->Longitude,
                "Stations" => $stations,
            ];

            // Render data in a template or return JSON response
            // Example: $this->renderWith('ProductPage', $countryInfo);
            return $this->customise($countryInfo)->render();
        } catch (\Exception $e) {
            // Handle exceptions
            return $e->getMessage();
        }
    }
}

In this code:

  • I’ve added error handling using try-catch blocks.
  • I’ve returned a 404 error if the country is not found.
  • I’ve used $this->customise() to pass data to the template.
  • I’ve encapsulated the database connection and query in try-catch blocks to handle any potential errors.