connecting to Informix db

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.
User avatar
jadamski
Posts: 32
Joined: Thu Apr 03, 2008 12:00 am

connecting to Informix db

Post by jadamski »

I'm trying to setup a connection to our Informix database (IDS 10 on HPUX server). I get an error that really tells me nothing of the cause. Anyone out there have code to connect to an Informix DB? Below is what I have been trying with the error on the open.


[reflection.assembly]::LoadWithPartialName("System.Data") | Out-Null
#connection$readconn = New-Object System.Data.OleDb.OleDbConnection$writeconn = New-Object System.Data.OleDb.OleDbConnection
[string]$connstr = "Provider=Ifxoledbc.2;Password=password;User ID=user1;Data Source=db1@server1;Persist Security Info=true;"
$readconn.connectionstring = $connstr$writeconn.connectionstring = $connstr
$readconn.open()Exception calling "Open" with "0" argument(s): "No error message available, result code: E_FAIL(0x80004005)."At line:1 char:15+ $readconn.open( <<<< )
$writeconn.open()Exception calling "Open" with "0" argument(s): "No error message available, result code: E_FAIL(0x80004005)."At line:1 char:16+ $writeconn.open( <<<< )

User avatar
jadamski
Posts: 32
Joined: Thu Apr 03, 2008 12:00 am

connecting to Informix db

Post by jadamski »

I'm trying to setup a connection to our Informix database (IDS 10 on HPUX server). I get an error that really tells me nothing of the cause. Anyone out there have code to connect to an Informix DB? Below is what I have been trying with the error on the open.


[reflection.assembly]::LoadWithPartialName("System.Data") | Out-Null
#connection$readconn = New-Object System.Data.OleDb.OleDbConnection$writeconn = New-Object System.Data.OleDb.OleDbConnection
[string]$connstr = "Provider=Ifxoledbc.2;Password=password;User ID=user1;Data Source=db1@server1;Persist Security Info=true;"
$readconn.connectionstring = $connstr$writeconn.connectionstring = $connstr
$readconn.open()Exception calling "Open" with "0" argument(s): "No error message available, result code: E_FAIL(0x80004005)."At line:1 char:15+ $readconn.open( <<<< )
$writeconn.open()Exception calling "Open" with "0" argument(s): "No error message available, result code: E_FAIL(0x80004005)."At line:1 char:16+ $writeconn.open( <<<< )

User avatar
donj
Posts: 416
Joined: Thu Nov 16, 2006 9:02 pm

connecting to Informix db

Post by donj »

http://connectionstrings.com/?carrier=informix has some example connection strings - looks like you've got that bit correct which is usually the issue. Beyond that, you're right - that's not terribly helpful. You're sure you have the Informix drivers on your computer?

Do this: Create a new text file named Test.udl (no .txt extension). Double-click it and use the GUI to try and connect - be sure to use "test connection." If you get it to work, open the file in Notepad and copy the connection string out, and try that.

User avatar
jadamski
Posts: 32
Joined: Thu Apr 03, 2008 12:00 am

connecting to Informix db

Post by jadamski »

Don,
Well, that help for the most part. Using the test.udl I found a useful error message (local mismatch). Which means the character set was not specified when the connector was configured, I fixed that and was able to connect using the test.udl. I copied that string (Provider=MSDASQL.1;Persist Security Info=False;User ID=adamski;Data Source=CARS Live) and tried to us it. But now get new errors. :-) isn

User avatar
donj
Posts: 416
Joined: Thu Nov 16, 2006 9:02 pm

connecting to Informix db

Post by donj »

You do need to make sure you're calling it - rather than using the older ODBC drivers, make sure your connection string is pulling the OLE DB drivers directly. It's usually the connection string that tells it which specific driver to call on.

User avatar
jadamski
Posts: 32
Joined: Thu Apr 03, 2008 12:00 am

connecting to Informix db

Post by jadamski »

Well I finally got it to work, so now I have more questions. :-)

The main question is on how to output the information once I get the record. This particular select will retrieve one record that has 28 columns. Now I knew this record had 28 columns but I might not always know, is there a way to display all the names and values of a record without knowing how many columns?

Here is the code I have to get the connection working and retrieve one records and force the output of all columns.

[reflection.assembly]::LoadWithPartialName("System.Data") | Out-Null

#connection
$readconn = New-Object System.Data.OleDb.OleDbConnection
$writeconn = New-Object System.Data.OleDb.OleDbConnection

[string]$connstr = "Provider=IFXOLEDBC.2;Password=xxxx;User ID=xxxx;Data Source=xxxx@xxxxitcp;Persist Security Info=true"

$readconn.connectionstring = $connstr
$readconn.open()

$sql = " SELECT * from id_rec where id=123456"

$readcmd = New-Object system.Data.OleDb.OleDbCommand
$readcmd.connection = $readconn
$readcmd.commandtext = $sql

$reader = $readcmd.executereader()

while ($reader.read()) {
for ($i=0;$i -le 28;$i++){
$reader.getname($i)
$reader.getvalue($i)}
}

$reader.close()

$readconn.close()

jvierra
Posts: 14550
Joined: Tue May 22, 2007 9:57 am
Contact:

connecting to Informix db

Post by jvierra »

The reader reads a rowset with columns. The collections all have a "Count" property that can be looked at for the number of columns. The reader objects all have "FieldCount" property.

I also noticed that -le should ne -lt as it is a zero based collection.

Code: Select all

while($reader.read()) {
  for ($i=0;$i -lt $reader.FieldCount;$i++){
  $reader.getname($i) 
  $reader.getvalue($i)}
}
	
 
	
jvierra2008-04-09 14:38:07

User avatar
jadamski
Posts: 32
Joined: Thu Apr 03, 2008 12:00 am

connecting to Informix db

Post by jadamski »

I assume you meant function GetData not Get-OleTable, or the line $table = GetData $sql $cs should be $table = Get-OleTable.
John

User avatar
jadamski
Posts: 32
Joined: Thu Apr 03, 2008 12:00 am

connecting to Informix db

Post by jadamski »

Ok I was trying to use your idea of a function to get the data from a sql statement and put the output into a object that can then be manipulated.
Well there seems to be enough differences in SQLserver and Informix I can't do as you suggested. As the Informix ODBC I'm using doesn't seem to have a few of the methods that SQLserver has. :( I guess will have to put the output into an object after the call to the database, bummer.
here's my code:
[reflection.assembly]::LoadWithPartialName("System.Data") | Out-Null
function GetData( [string]$sqlText, [string]$connectString) { $da = new-object System.Data.OleDb.OleDbConnection $ds = new-object System.Data.DataSet $conn = new-object System.Data.OleDb.OleDbConnection($connectString) $conn.Open() $cmd = new-object System.Data.OleDb.OleDbCommand($sqlText,$conn) $cmd.CommandType = "Text" $da.SelectCommand = $cmd $ret=$da.Fill($ds) $ds.Tables[0]}
$sql = 'select username, active_email, effective_date from gu_dl_fac_table d, gu_login_rec l where d.id=l.id'$cs = "Provider=IFXOLEDBC.2;Password=xxxx;User ID=xxxx;Data Source=xxxx@xxxx;Persist Security Info=true"
$table = GetData $sql $cs Write-Output $table

ERROR: Property 'SelectCommand' cannot be found on this object; make sure it exists and is settable.ERROR: At line:10 char:9ERROR: + $da.S <<<< electCommand = $cmdERROR: Method invocation failed because [System.Data.OleDb.OleDbConnection] doesn't contain a method named 'Fill'.ERROR: At line:11 char:18ERROR: + $ret=$da.Fill( <<<< $ds)ERROR: Unable to index into an object of type System.Data.DataTableCollection.ERROR: At line:12 char:16ERROR: + $ds.Tables[0 <<<< ]
*** PowerShell Script finished. ***
If I do a get-member on $da & $ds I get the following information:
PS H:> $da | gm
TypeName: System.Data.OleDb.OleDbConnection
Name MemberType Definition---- ---------- ----------add_Disposed Method System.Void add_Disposed(EventHandler value)add_InfoMessage Method System.Void add_InfoMessage(OleDbInfoMessageEventHandler value)add_StateChange Method System.Void add_StateChange(StateChangeEventHandler value)BeginTransaction Method System.Data.OleDb.OleDbTransaction BeginTransaction(), System.Data.OleDb.Ole...ChangeDatabase Method System.Void ChangeDatabase(String value)Close Method System.Void Close()CreateCommand Method System.Data.OleDb.OleDbCommand CreateCommand(), System.Data.Common.DbCommand...CreateObjRef Method System.Runtime.Remoting.ObjRef CreateObjRef(Type requestedType)Dispose Method System.Void Dispose()EnlistDistributedTransaction Method System.Void EnlistDistributedTransaction(ITransaction transaction)EnlistTransaction Method System.Void EnlistTransaction(Transaction transaction)Equals Method System.Boolean Equals(Object obj)GetHashCode Method System.Int32 GetHashCode()GetLifetimeService Method System.Object GetLifetimeService()GetOleDbSchemaTable Method System.Data.DataTable GetOleDbSchemaTable(Guid schema, Object[] restrictions)GetSchema Method System.Data.DataTable GetSchema(), System.Data.DataTable GetSchema(String co...GetType Method System.Type GetType()get_ConnectionString Method System.String get_ConnectionString()get_ConnectionTimeout Method System.Int32 get_ConnectionTimeout()get_Container Method System.ComponentModel.IContainer get_Container()get_Database Method System.String get_Database()get_DataSource Method System.String get_DataSource()get_Provider Method System.String get_Provider()get_ServerVersion Method System.String get_ServerVersion()get_Site Method System.ComponentModel.ISite get_Site()get_State Method System.Data.ConnectionState get_State()InitializeLifetimeService Method System.Object InitializeLifetimeService()Open Method System.Void Open()remove_Disposed Method System.Void remove_Disposed(EventHandler value)remove_InfoMessage Method System.Void remove_InfoMessage(OleDbInfoMessageEventHandler value)remove_StateChange Method System.Void remove_StateChange(StateChangeEventHandler value)ResetState Method System.Void ResetState()set_ConnectionString Method System.Void set_ConnectionString(String value)set_Site Method System.Void set_Site(ISite value)ToString Method System.String ToString()ConnectionString Property System.String ConnectionString {get;set;}ConnectionTimeout Property System.Int32 ConnectionTimeout {get;}Container Property System.ComponentModel.IContainer Container {get;}Database Property System.String Database {get;}DataSource Property System.String DataSource {get;}Provider Property System.String Provider {get;}ServerVersion Property System.String ServerVersion {get;}Site Property System.ComponentModel.ISite Site {get;set;}State Property System.Data.ConnectionState State {get;}
PS H:> $ds | gm
TypeName: System.Data.DataSet
Name MemberType Definition---- ---------- ----------AcceptChanges Method System.Void AcceptChanges()add_Disposed Method System.Void add_Disposed(EventHandler value)add_Initialized Method System.Void add_Initialized(EventHandler value)add_MergeFailed Method System.Void add_MergeFailed(MergeFailedEventHandler value)BeginInit Method System.Void BeginInit()Clear Method System.Void Clear()Clone Method System.Data.DataSet Clone()Copy Method System.Data.DataSet Copy()CreateDataReader Method System.Data.DataTableReader CreateDataReader(), System.Data.DataTableReader C...Dispose Method System.Void Dispose()EndInit Method System.Void EndInit()Equals Method System.Boolean Equals(Object obj)GetChanges Method System.Data.DataSet GetChanges(), System.Data.DataSet GetChanges(DataRowState...GetHashCode Method System.Int32 GetHashCode()GetObjectData Method System.Void GetObjectData(SerializationInfo info, StreamingContext context)GetService Method System.Object GetService(Type service)GetType Method System.Type GetType()GetXml Method System.String GetXml()GetXmlSchema Method System.String GetXmlSchema()get_CaseSensitive Method System.Boolean get_CaseSensitive()get_Container Method System.ComponentModel.IContainer get_Container()get_DataSetName Method System.String get_DataSetName()get_DefaultViewManager Method System.Data.DataViewManager get_DefaultViewManager()get_DesignMode Method System.Boolean get_DesignMode()get_EnforceConstraints Method System.Boolean get_EnforceConstraints()get_ExtendedProperties Method System.Data.PropertyCollection get_ExtendedProperties()get_HasErrors Method System.Boolean get_HasErrors()get_IsInitialized Method System.Boolean get_IsInitialized()get_Locale Method System.Globalization.CultureInfo get_Locale()get_Namespace Method System.String get_Namespace()get_Prefix Method System.String get_Prefix()get_Relations Method System.Data.DataRelationCollection get_Relations()get_RemotingFormat Method System.Data.SerializationFormat get_RemotingFormat()get_SchemaSerializationMode Method System.Data.SchemaSerializationMode get_SchemaSerializationMode()get_Site Method System.ComponentModel.ISite get_Site()get_Tables Method System.Data.DataTableCollection get_Tables()HasChanges Method System.Boolean HasChanges(), System.Boolean HasChanges(DataRowState rowStates)InferXmlSchema Method System.Void InferXmlSchema(XmlReader reader, String[] nsArray), System.Void I...Load Method System.Void Load(IDataReader reader, LoadOption loadOption, FillErrorEventHan...Merge Method System.Void Merge(DataSet dataSet), System.Void Merge(DataSet dataSet, Boolea...ReadXml Method System.Data.XmlReadMode ReadXml(XmlReader reader), System.Data.XmlReadMode Re...ReadXmlSchema Method System.Void ReadXmlSchema(XmlReader reader), System.Void ReadXmlSchema(Stream...RejectChanges Method System.Void RejectChanges()remove_Disposed Method System.Void remove_Disposed(EventHandler value)remove_Initialized Method System.Void remove_Initialized(EventHandler value)remove_MergeFailed Method System.Void remove_MergeFailed(MergeFailedEventHandler value)Reset Method System.Void Reset()set_CaseSensitive Method System.Void set_CaseSensitive(Boolean value)set_DataSetName Method System.Void set_DataSetName(String value)set_EnforceConstraints Method System.Void set_EnforceConstraints(Boolean value)set_Locale Method System.Void set_Locale(CultureInfo value)set_Namespace Method System.Void set_Namespace(String value)set_Prefix Method System.Void set_Prefix(String value)set_RemotingFormat Method System.Void set_RemotingFormat(SerializationFormat value)set_SchemaSerializationMode Method System.Void set_SchemaSerializationMode(SchemaSerializationMode value)set_Site Method System.Void set_Site(ISite value)ToString Method System.String ToString()WriteXml Method System.Void WriteXml(Stream stream), System.Void WriteXml(TextWriter writer),...WriteXmlSchema Method System.Void WriteXmlSchema(Stream stream), System.Void WriteXmlSchema(TextWri...CaseSensitive Property System.Boolean CaseSensitive {get;set;}Container Property System.ComponentModel.IContainer Container {get;}DataSetName Property System.String DataSetName {get;set;}DefaultViewManager Property System.Data.DataViewManager DefaultViewManager {get;}DesignMode Property System.Boolean DesignMode {get;}EnforceConstraints Property System.Boolean EnforceConstraints {get;set;}ExtendedProperties Property System.Data.PropertyCollection ExtendedProperties {get;}HasErrors Property System.Boolean HasErrors {get;}IsInitialized Property System.Boolean IsInitialized {get;}Locale Property System.Globalization.CultureInfo Locale {get;set;}Namespace Property System.String Namespace {get;set;}Prefix Property System.String Prefix {get;set;}Relations Property System.Data.DataRelationCollection Relations {get;}RemotingFormat Property System.Data.SerializationFormat RemotingFormat {get;set;}SchemaSerializationMode Property System.Data.SchemaSerializationMode SchemaSerializationMode {get;set;}Site Property System.ComponentModel.ISite Site {get;set;}Tables Property System.Data.DataTableCollection Tables {get;}


jvierra
Posts: 14550
Joined: Tue May 22, 2007 9:57 am
Contact:

connecting to Informix db

Post by jvierra »

Ok -

This is wrong:
$da = new-object System.Data.OleDb.OleDbConnection

The $da is a TableAdapter. You have changed that.

Use my posted code. The way you are doing it won't work with any database.

Locked