ctigeek / invokequerypowershellmodule Goto Github PK

How to make connection to firebird database

Hi,

Great module, one question,

With Invoke-Sqlcmd you can specify an imput file e.g. ( -InputFile "\myfile.sql" ) can you do this with your module?

Thanks
Jon

just trying to use the Invoke-MySqlQuery and it doesn't like the string for the query:

Invoke-MySqlQuery -SqlQuery 'SELECT * FROM v_computers' -Credential $credentials -Database MyDB -Server MyServer

Error:

Invoke-MySqlQuery : Cannot bind parameter 'SqlQuery'. Cannot convert the "SELECT * FROM v_computers" value of type "System.String" to type "InvokeQuery.SqlQuery".
At line:1 char:29
+ Invoke-MySqlQuery -SqlQuery 'SELECT * FROM v_computers' -Credential $ ...
+                             ~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Invoke-MySqlQuery], ParameterBindingException
    + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,InvokeQuery.InvokeMySqlQuer

unfortunately it doesn't work in powershell under linux environment.
Error: Socket.IOControl handles Windows-specific control codes and is not supported on this platform

How to reproduce issue
When I using the -ConnectionString parameter with e.g. Invoke-SqlServerQuery

Invoke-SqlServerQuery -ConnectionString $connectionstring -Sql $sql

I get an error

Invoke-SqlServerQuery : Object reference not set to an instance of an object.

Workaround
Feed the respected values from the connectionstring as separate parameters

Invoke-SqlServerQuery -Server $server -Database $database -Credential $credentials -Sql $sql

• Tested on version 1.0.1
• Bug is valid even when using the query item created by New-SqlQuery instead of raw sql
• Didn't test with other cmdlets

We are trying to access a postgress database from powershell (7.3) as follows:
$server=
$db =
$table =
$user=
$pwd=
$connectionString = "Host=$server;Username=$user;Password=$pwd;Database=$db";
$query ="select * from $table"
$results = Invoke-PostgreSqlQuery -ConnectionString $connectionString -sql $query -Verbose

We end up with the following:
VERBOSE: Using the following connection string: Host=;Username=xxxxxxxxxx;Password=xxxxxxxxxx;Database=
VERBOSE: Opening connection.
VERBOSE: Processed 0 queries in 5 milliseconds.
VERBOSE: Complete.
Invoke-PostgreSqlQuery: \test6.ps1:11:12
Line |
11 | $results = Invoke-PostgreSqlQuery -ConnectionString $connectionString …
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Authentication method not supported (Received: 10)

Does anyone know how to get past this error. We are using postrgess 13.10

Thanks in advance

Are there any plans to push latest version to PSGallery?

If you pipe in multiple sql queries, and include the ExpectedRowCount property, you would expect the value included with the property to match the number of rows modified by all sql statements. That is not the case and results in a transaction rollback.

$sql1 = "update table1 set column1 ='value1' where pk = 1;"
$sql2 = "update table1 set column1 = 'value2' where pk = 2;"
##this will result in a rollback
($sql1,$sql2) | Invoke-SqlServerQuery -ExpectedRowCount 2 -CUD

How does it work?

$db = 'c:\Users\gebruiker\AppData\Roaming\Mozilla\Firefox\Profiles\ptk96eju.default-1565324776760514\places.sqlite'
$sql = "SELECT * FROM moz_bookmarks;"
Invoke-SqliteQuery -Sql $sql -Database $db -Verbose

:

VERBOSE: Using the following connection string: Data Source=.\localhost;Initial Catalog=c:\Users\gebruiker\AppData\Roaming\Mozilla\Firefox\Profiles\ptk96eju.default-1565324776760514\places.sqlite;
VERBOSE: Opening connection.
VERBOSE: Connection to database is open.
VERBOSE: Running query number 1
VERBOSE: Running the following query: SELECT * FROM moz_bookmarks;
VERBOSE: Performing the operation "Run Query:`SELECT * FROM moz_bookmarks;`" on target "Database server".
VERBOSE: Closing connection.
VERBOSE: Processed 1 queries in 5 milliseconds.
VERBOSE: Complete.
Invoke-SqliteQuery : SQL logic error or missing database
no such table: moz_bookmarks
At C:\sqlite-query.ps1:7 char:1
+ Invoke-SqliteQuery -Sql $sql -Database $db -Verbose -Server .\localho ...
+ ~~
```~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Invoke-SqliteQuery], SQLiteException
    + FullyQualifiedErrorId : System.Data.SQLite.SQLiteException,InvokeQuery.InvokeSqliteQuery

Invoke-PostgreSqlQuery -Credential $Credential -Server $Server -Database $DB -Sql $QueryStr;
Invoke-PostgreSqlQuery : Keyword not supported: data source
Parameter name: keyword
At line:1 char:1

• Invoke-PostgreSqlQuery -Credential $Credential -Server $Server -Datab ...

•   + CategoryInfo          : NotSpecified: (:) [Invoke-PostgreSqlQuery], ArgumentException
    + FullyQualifiedErrorId : System.ArgumentException,InvokeQuery.InvokePostgreSqlQuery
  

Hi

When running the New-SqliteDatabase command the following error is given:
New-SqliteDatabase : Unable to load DLL 'SQLite.Interop.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E).
This is fixed by copying the SQLite.Interop.dll file into the folder 'C:\Program Files\WindowsPowerShell\Modules\InvokeQuery\1.0.2' - ie the file is missing from the folder after installing the module

I would like the ability to choose the port to connect to. I use local and remote MySQL databases through a jump box (bastion host, passthrough, etc), and I have that mapped to a different port. Being able to choose the port would allow me to use Invoke-MySqlQuery with it. Thanks!

Howdy. I've run into a problem on a MySQL server I'd like to use invoke-mysqlquery for. Using a login that involves a semicolon or backtick appears to cause errors. On the backtick I get a simple 'access denied', though I can work around it by adding 1 extra backtick (which probably means you'd need 1 extra backtick per actual backtick). On the one with multiple special characters, I get the below error, probably related to the semicolon in the password, but could be the equals sign.

I'm unsure how the connection string creator handles it, but I would expect you could put the password in double quotes and have it work.

Thanks in advance!

$secpasswd = ConvertTo-SecureString "g@=_;adbgw$yuv&" -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential ("abcdefgh", $secpasswd)
$server = "localhost"
$table = "``TABLES``"
$query = "select count(*) from $table";
$result = (Invoke-MySqlQuery -sql $query -Database $db -Credential $mycreds -Verbose -Server $server )

I get the following error. (note that it shows the partial password, which shows that it's due to it misinterpreting the semicolon):

VERBOSE: Using the following connection string: Data Source=localhost;Initial Catalog=INFORMATION_SCHEMA;User ID=abcdefgh;Password=xxxxxxxxxx;adbgw&;
VERBOSE: Opening connection.
Invoke-MySqlQuery : Format of the initialization string does not conform to specification starting at index 95.
At line:14 char:12
+ $result = (Invoke-MySqlQuery -sql $query -Database $db -Credential $mycreds -Ver ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Invoke-MySqlQuery], ArgumentException
    + FullyQualifiedErrorId : System.ArgumentException,InvokeQuery.InvokeMySqlQuery