Open connection with connection name doesn't work

[rmjoia]

When I try to open a connection, at least for PostgreSQL with the parameter -ConnectionName doesn't error, but doesn't open the connection, only if I remove it and use the default name.

Also, despite I get the warning to use credentials instead of -UserName and -Password, -Credentials is not on the Wiki

[mithrandyr]

@rmjoia can you provide an example of what you are trying to do and what the error message is?

[rmjoia]

There's no error message, just doesn't connect. Same command as without -ConnectionName...

On Thu 16 Mar 2023, 12:47 Mithrandyr, @.***> wrote:

@rmjoia https://github.com/rmjoia can you provide an example of what you are trying to do and what the error message is?

— Reply to this email directly, view it on GitHub https://github.com/mithrandyr/SimplySql/issues/113#issuecomment-1471894144, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABXTC3BJBZ27LQQF2WG2G4DW4MDXPANCNFSM6AAAAAAV5BYNDE . You are receiving this because you were mentioned.Message ID: @.***>

[mithrandyr]

@rmjoia -- What I was looking for was a powershell script that reproduced the issue.

# open PostgreSQL connection and show that the connection is open (will receive warning about -username/-password)
Open-PostGreConnection -UserName someUser -Password somePassword
Show-SqlConnection

#open PostgreSQL connection using a credential object and show that the connection is open
Open-PostGreConnection -Credential (Get-Credential)
Show-SqlConnection

#open a named PostgreSQL connection using a credential object and show that the connection is open
Open-PostGreConnection -Credential (Get-Credential) -ConnectionName alpha
Show-SqlConnection -ConnectionName alpha

I hope the above examples are helpful. Please let me know what you discover.

[rmjoia]

Hi, yeah I got it, the thing is that there's nothing fancy that I'm doing. It's just simple usage.

            Open-PostGreConnection `
                -Server $hostname `
                -Database $databaseName `
                -Credential ([pscredential]::new($databaseUser, (ConvertTo-SecureString -Force -AsPlainText $PGPassword)))

just by using

            Open-PostGreConnection `
                -ConnectionName $connectionName `
                -Server $hostname `
                -Database $databaseName `
                -Credential ([pscredential]::new($databaseUser, (ConvertTo-SecureString -Force -AsPlainText $PGPassword)))          

If it helps, the server is in Azure

In this particular case, removing the -ConnectionName $connectionName ` was enough to be able to connect, hence why I'm letting you know.

[mithrandyr]

@rmjoia -- the server being in azure should be an issue. When you say that it doesn't connect when you use the -ConnectionName parameter (of Open-PostGreConnection) how do you know? Do you run either Show-SqlConnection -ConnectionName $connectionName or Show-SqlConnection -All or Get-SqlConnection -ConnectionName $connectionName?

Also are you using WindowsPowershell 5.1 or PowerShell 7?

I'm not able to reproduce this issue, but there is fundamentally no difference in how a connection to Postgre is made, regardless of whether you use -ConnectionName or not. Actually, when you don't pass in -ConnectionName, then it defaults to "default" -- so the logic of the module is always using a ConnectionName.

Please try passing in "alpha" (hardcoded) for the connection name and then running show-sqlconnection alpha after wards:

Open-PostGreConnection -ConnectionName alpha
Show-SqlConnection -ConnectionName alpha

[rmjoia]

I used Show-SqlConnection without the -All parameter. Now I tested it again, and indeed, using it, or querying it with the parameter or with the -ConnectionName parameter lists it, but when I invoke a query, it returns an error.

When I try to query, it doesn't pick the other name, I'm thinking that's because it uses the "default" name? I didn't look into the "code" yet... and apparently, I can't use the -ConnectionName parameter with the Invoke-SqlQuery command.

My powershell version:

With alpha is the same, when I try to get the connection without the name or invoke a query, I get the error that no connection was found... only default works. It's no dealbreaker for me, but I just wanted to let you know.

[mithrandyr]

@rmjoia I think i see the problem -- if you open a connection and name it 'test', then every other command in the module, you will have to specify 'test' as the connectionName.

Also - the error you are receiving is because you didn't specify -ConnectionName you specified --ConnectionName (notice you used 2 dashes, not one).

Basically, the module allows you have create as many connections as you need, so long as you use a unique name for each connection and then to invoke commands against all of those connections. However, if you only need to have a single connection open, then you can ignore the connectionName parameter (everything will default to using 'default' as the connection name.

So in your example, here is what you need to do

#Create a postgre connection and name it 'test'
Open-PostGreConnection -ConnectionName "test" #add the rest of the parameters...

#execute a query against the newly opened connection, remember, since the connection is named 'test', then invoke-sqlquery, and every other cmdlet will require you to specify -ConnectionName "test" in order for those commands to be executed against that connection.
Invoke-SqlQuery -ConnectionName "test" -Query "Select datname FROM pg_database"

[rmjoia]

But, as I also mentioned before and provided screenshot, invoke sqlquery doesnt accept that parameter... unless i had a typo...

On Mon 20 Mar 2023, 18:54 Mithrandyr, @.***> wrote:

@rmjoia https://github.com/rmjoia I think i see the problem -- if you open a connection and name it 'test', then every other command in the module, you will have to specify 'test' as the connectionName.

Basically, the module allows you have create as many connections as you need, so long as you use a unique name for each connection and then to invoke commands against all of those connections. However, if you only need to have a single connection open, then you can ignore the connectionName parameter (everything will default to using 'default' as the connection name.

So in your example, here is what you need to do

Create a postgre connection and name it 'test'Open-PostGreConnection -ConnectionName "test" #add the rest of the parameters...

execute a query against the newly opened connection, remember, since the connection is named 'test', then invoke-sqlquery, and every other cmdlet will require you to specify -ConnectionName "test" in order for those commands to be executed against that connection.Invoke-SqlQuery -ConnectionName "test" -Query "Select datname FROM pg_database"

— Reply to this email directly, view it on GitHub https://github.com/mithrandyr/SimplySql/issues/113#issuecomment-1476767692, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABXTC3EMNDWEXIXW6U7TNQTW5CRV3ANCNFSM6AAAAAAV5BYNDE . You are receiving this because you were mentioned.Message ID: @.***>

[mithrandyr]

Yes - you had a typo -- use used --ConnectionName not -ConnectionName you used 2 dashes instead of 1 dash.

[rmjoia]

I'll give it another go tomorrow and I'll let you know. thanks for all the support!

[rmjoia]

Sorry @mithrandyr works fine, my bad. 🫣🙄sorry for making you waste your time.