Sybase DB query
Forum rules
Do not post any licensing information in this forum.
Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Do not post any licensing information in this forum.
Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Sybase DB query
This may be a stupid question but is there anyway to query a Sybase DB with powershell and if so can you give me a code example.
Re: Sybase DB query
You can use ODBC to query it. Search for Sybase ODBC with Net FRamework and you will find many example.
Sybase also supports Net so post on Sybase forums for code samples.
http://infocenter.sybase.com/help/index ... CCCCCA.htm
Sybase also supports Net so post on Sybase forums for code samples.
http://infocenter.sybase.com/help/index ... CCCCCA.htm
Re: Sybase DB query
So looking around and reading found a way to use ODBC. But I cant seem to get it to work. I keep getting the error below. I know the driver is there. I did a test connection of the DB.
ERROR: Exception calling "Open" with "0" argument(s): "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified" SelectStringFromMultiFIle.ps1 (23, 1): ERROR: At Line: 23 char: 1 ERROR: + $conn.open() ERROR: + ~~~~~~~~~~~~ ERROR: + CategoryInfo : NotSpecified: (:) [], MethodInvocationException ERROR: + FullyQualifiedErrorId : OdbcException ERROR: SELECT * FROM dbo.***** System.Data.DataSet ERROR: Exception calling "Fill" with "1" argument(s): "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified" SelectStringFromMultiFIle.ps1 (30, 1): ERROR: At Line: 30 char: 1 ERROR: + $da.fill($ds) ERROR: + ~~~~~~~~~~~~~ ERROR: + CategoryInfo : NotSpecified: (:) [], MethodInvocationException ERROR: + FullyQualifiedErrorId : OdbcException ERROR:
Code: Select all
$query = "SELECT * FROM dbo.******"
$conn = New-Object System.Data.Odbc.OdbcConnection
$conn.ConnectionString = "driver={Adaptive Server Enterprise};dsn=servername;db=DBName;na=,PORT=Port#;uid=*****;pwd=*******;"
$conn.open()
$cmd = new-object System.Data.Odbc.OdbcCommand($query, $conn)
$cmd.CommandTimeout = 30
write-host $query
$ds = New-Object system.Data.DataSet
$da = New-Object system.Data.odbc.odbcDataAdapter($cmd)
write-host $ds
$da.fill($ds)
$ds.Tables[0] | out-gridview
$conn.close()
Re: Sybase DB query
Driver is installed. But I am still getting the error.
- Attachments
-
- 2017-08-24 13_46_52-SysWOW64.png (35.59 KiB) Viewed 5416 times
Re: Sybase DB query
So just to verify are you saying what I have written is correct. I just have incorrect drivers?
Re: Sybase DB query
You are using the enterprise driver and not the ODBC driver. To use the ODBC driver you need to specify a DSN. Post in Sybase forum to learn how to use the Sybase ODBC driver. You may also have to install the ODBC driver as a second install.
\Open the ODBC manager from control panel/admin tools to verify the Sybase ODBC driver.
\Open the ODBC manager from control panel/admin tools to verify the Sybase ODBC driver.
Re: Sybase DB query
After looking into this there was nothing wrong with my Drivers. It was the script itself. If any one needs to know how to do this please see below. DSN setup is not needed. You do need the correct drivers though. In my case it was Sybase ASE which I already had.
Code: Select all
$query = "SELECT * FROM dbo.Table WHERE user LIKE `"mike%`""
$conn = New-Object System.Data.Odbc.OdbcConnection
$conn.ConnectionString = "Driver={Adaptive Server Enterprise};server=servername;db=dbName;port=#;uid=username;pwd=password;"
$conn.open()
$cmd = new-object System.Data.Odbc.OdbcCommand($query, $conn)
$cmd.CommandTimeout = 30
$ds = New-Object system.Data.DataSet
$da = New-Object system.Data.odbc.odbcDataAdapter($cmd)
[void]$da.fill($ds)
$ds.Tables[0]
$conn.close()
Re: Sybase DB query
I fail to see the difference between the first code and the second. The first script clearly states the driver is not available.
Both scripts examples are identical with the exception of adding an adapter in the second.
The ACE driver sold work with either ODBC or OLEDB. Yes you can use a "DSN-less" connection which is just a SN that is fully specified.
This is why I recommended posting on the Sybase forums. Each vendor will have slightly different behaviors and their forums will have users with experience.
Here is a good place to learn ADO.Net: https://docs.microsoft.com/en-us/dotnet ... t-overview
Both scripts examples are identical with the exception of adding an adapter in the second.
The ACE driver sold work with either ODBC or OLEDB. Yes you can use a "DSN-less" connection which is just a SN that is fully specified.
This is why I recommended posting on the Sybase forums. Each vendor will have slightly different behaviors and their forums will have users with experience.
Here is a good place to learn ADO.Net: https://docs.microsoft.com/en-us/dotnet ... t-overview