0

I'm currently working with Powershell and PostgreSQL and I encountered weird results. I also tried searching for similar questions but couldn't find one.

So, below is my Powershell script config for PostgreSQL which I got from here: Connect to remote PostgreSql database using Powershell 

$MyServer = "MyIP"
$MyPort  = "5432"
$MyDB = "MyDB"
$MyUid = "MyUser"
$MyPass = "MyPass"

$DBConnectionString = "Driver={PostgreSQL UNICODE(x64)};Server=$MyServer;Port=$MyPort;Database=$MyDB;Uid=$MyUid;Pwd=$MyPass;"
$DBConn = New-Object System.Data.Odbc.OdbcConnection;
$DBConn.ConnectionString = $DBConnectionString;
$DBConn.Open();
$DBCmd = $DBConn.CreateCommand();
$DBCmd.CommandText = "SET SCHEMA 'MySchema'; SELECT * FROM Myclients;";
$DBCmd.ExecuteReader();
$DBConn.Close();

It's a pretty straightforward SELECT query, nothing fancy. Connection is working but the SELECT results returns this: 

FieldCount
----------
        12
        12
        12
        12
        12
        12
        12
        12
        12
        12

Not really sure why it happen. Maybe someone can enlighten me. Thanks in advance!

L Vincent
  • 23
  • 1
  • 5
  • I think I figure this out. You explicitly need to use the dataset command and adapter. So, this will answer the problem above. After opening, you add the following: $DBCmd= New-object System.Data.Odbc.OdbcCommand($query,$conn) $ds = New-Object system.Data.DataSet (New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null $conn.close() $ds.Tables[0] I'll just keep this question for others who might encounter the same problem. – L Vincent May 28 '20 at 00:54
  • 1
    Even better would be if you wrote this as an answer rather than a comment. – Laurenz Albe May 28 '20 at 05:45

0 Answers0