Multiple DBs

[cgeorge063]

I have not worked with multiple dbs in one ux component. I have an alpha dao string connected to the initial database I need to use, but I'm a little perplexed how to incorporate multiple. I created a separate alpha dao connection string thinking that might solve the problem, but it is not helping. I'm open to zoom or discord if it would be easier to see the problem.

[madtowng]

I use several DBs on a regular basis, and sometimes even multiple DB servers.
I'm willing to help. I can do zoom, but prefer quick assist and telephone if you're
somewhere in North America. sixzeroeight-345-twosixnineseven.
If you want to give me a hint at the code you have so far, that'll make things a bit easier.

Gregg

[Devforia]

Here's an example that could help if you're trying to do the multiple connections over XBasic. If you're referring to the List Controls, then yep you just need a separate connection string for your other DB connection and point the List's Data Source to it :)

function ExampleFunction as c (e as p)
    on error goto example_function_error_handler
        Dim sql as c
        Dim cn as sql::Connection

        '-- Open the first database connection
        if .not. cn.Open("::name::db1CnString") then
            error_generate("Could not connect to the database.")
        end if

        '-- Perform a DB query on the first connection
        sql = <<%str%
        SELECT * FROM example_table
        %str%

        if .not. cn.Execute(sql) then
            error_generate("Could not query the database.")
        end if

        '-- Close the connection now that we're done working with that connection
        cn.Close()

       '-- Now open the second DB connection
        if .not. cn.Open("::name::db2connectionstring") then
            error_generate("Could not connect to the database.")
        end if

        '-- Perform a DB query on the second connection
        sql = <<%str%
            SELECT * FROM example_table
        %str%

        if .not. cn.Execute(sql) then
            error_generate("Could not query the database.")
        end if

      '-- Again, make sure to close the connection now that we're finished.
      cn.Close()

      ExampleFunction = "Success"
   end
   example_function_error_handler:
      ExampleFunction = js_escape(error_text_get())
      exit function
   end
end function

[lgrupido]

Lookup "Dynamic Connection String" in Alpha Help. They have a reasonable example there.

But basically, you setup a regular connection string as you have now.

When a user logs in, modify the connection string to fit the db your user needs, then use the new connection string (Stored in a session variable) throughout the system.

[madtowng]

The only thing you need to different is include the database in the sql statement.
There is no need to close the connection between sql calls, nor is there a need for a 2nd connection string.
Feel free to contact me if you need or want more help.

Gregg

sqlA = <<%txt%
select * from db1.example_table
%txt%

sqlB = <<%txt%
select * from db2.example_table
%txt%

[Devforia]

The only thing you need to different is include the database in the sql statement.
There is no need to close the connection between sql calls, nor is there a need for a 2nd connection string.
Feel free to contact me if you need or want more help.

Gregg

sqlA = <<%txt%
select * from db1.example_table
%txt%

sqlB = <<%txt%
select * from db2.example_table
%txt%

That's true, but how about if the other database is on a separate server? For example, you have a MariaDB database and a SQL Server database hosted on separate servers. You'd need a separate connection string to accomplish this, correct?

[madtowng]

As an afterthought, if the query fails, you will probably want to return cn.callresult.text as the error message. It is "usually" a bit more helpful
and "should" reduce debug time.

[madtowng]

That is true, and part of what I was trying to find out from the code you had so far.
You can have multiple connections open at the same time ( cn1 and cn2).

[Devforia]

I used to return cn.CallResult.Text but it returns information about the SQL server such as the database name, table, etc. Since I often return JS to display the error text to the user, I don’t like to expose that data and only access it in my debugger. I usually use a unique error message in my conditionals so I know where it failed, or I’ll return error_line_number_get and then debug to see the CallResult.

Lots of ways to approach the error handling, I just wanted to show an example function in the event that they had a use case of two connections on separate servers via XBasic moreso than best error handling practices :P

[madtowng]

It depends on the need. I have one project that has the order info on server A, but the file image on Server B.
That project needs both connections open simultaneously (at least for the way I have it set up right now).

[cgeorge063]

madtowng I have essentially what Devforia shared in my xbasic code already. My separate DB is listed when the code checks for session variable, but it is clearly not connecting. The error I'm getting is:

Error executing code for Custom data source: 'Error getting organizations info: Not currently connected to Database'

The two databases I'm trying to use are on the same server.

[madtowng]

Hi CG,

We know what the error is, but I can't tell you the cause of the error without seeing your code.
It seems this should be a quick fix.

[cgeorge063]

madtowng Sure thing. I pm'd you.