ctigeek / invokequerypowershellmodule Goto Github PK
View Code? Open in Web Editor NEWA Powershell module of Cmdlets for querying most types of databases.
Home Page: https://www.powershellgallery.com/packages/InvokeQuery
License: MIT License
A Powershell module of Cmdlets for querying most types of databases.
Home Page: https://www.powershellgallery.com/packages/InvokeQuery
License: MIT License
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
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
New-SqlQuery
instead of raw sqlInvoke-PostgreSqlQuery -Credential $Credential -Server $Server -Database $DB -Sql $QueryStr;
Invoke-PostgreSqlQuery : Keyword not supported: data source
Parameter name: keyword
At line:1 char:1
+ CategoryInfo : NotSpecified: (:) [Invoke-PostgreSqlQuery], ArgumentException
+ FullyQualifiedErrorId : System.ArgumentException,InvokeQuery.InvokePostgreSqlQuery
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
How to make connection to firebird database
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!
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
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
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
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
Are there any plans to push latest version to PSGallery?
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
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
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.