Sybase DB query

Ask your PowerShell-related questions, including questions on cmdlet development!
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.
This topic is 6 years and 6 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
sekou2331
Posts: 318
Last visit: Sat Oct 28, 2023 7:46 am

Sybase DB query

Post by sekou2331 »

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.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Sybase DB query

Post by jvierra »

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
User avatar
sekou2331
Posts: 318
Last visit: Sat Oct 28, 2023 7:46 am

Re: Sybase DB query

Post by sekou2331 »

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()
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Sybase DB query

Post by jvierra »

You have to install the drivers first.
User avatar
sekou2331
Posts: 318
Last visit: Sat Oct 28, 2023 7:46 am

Re: Sybase DB query

Post by sekou2331 »

Driver is installed. But I am still getting the error.
Attachments
2017-08-24 13_46_52-SysWOW64.png
2017-08-24 13_46_52-SysWOW64.png (35.59 KiB) Viewed 5416 times
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Sybase DB query

Post by jvierra »

Is that the ODBC driver? Post in Sybase forum for help with their providers.
User avatar
sekou2331
Posts: 318
Last visit: Sat Oct 28, 2023 7:46 am

Re: Sybase DB query

Post by sekou2331 »

So just to verify are you saying what I have written is correct. I just have incorrect drivers?
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Sybase DB query

Post by jvierra »

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.
User avatar
sekou2331
Posts: 318
Last visit: Sat Oct 28, 2023 7:46 am

Re: Sybase DB query

Post by sekou2331 »

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()
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Sybase DB query

Post by jvierra »

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
This topic is 6 years and 6 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked