Infralution Support Forum Index Infralution Support
Support groups for Infralution products
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Postgres integration

 
Post new topic   Reply to topic    Infralution Support Forum Index -> Virtual Data Objects Support
View previous topic :: View next topic  
Author Message
WaqasK



Joined: 21 Oct 2006
Posts: 32
PostPosted: Sat Oct 21, 2006 7:16 pm    Post subject: Postgres integration Reply with quote
Hi Guys,

I'm trying to bind VDO to a PostgreSQL database but I'm having trouble creating the virtual connection. At first i was using an Access DB and the connection was made thus:

Code:
vc as virtualconnection
dim conn as string

conn = "Provider=Microsoft.Jet.OLDB.4.0; DataSource=c:\db1.mdb"
vc = New VirtualConnection(conn, "", "", -1, 100)


I don't know wat the equivalent code would be when using PostgreSQL rather than access. Could you help me with this?

Thanks

Waqas
Back to top
View user's profile Send private message
WaqasK



Joined: 21 Oct 2006
Posts: 32
PostPosted: Sun Oct 22, 2006 6:15 pm    Post subject: Reply with quote
Ok guys,

I searched on the net and found what I think should be te connection string:

Code:
"Provider=PostgreSQL OLE DB Provider;Data Source=localhost;Location=postgres;User ID=id;password=pwd;"


However, when i connect to the database and attempt to run a simple query "SELECT * FROM mainlist" the system returns the following error:

Code:
An unhandled exception of type 'Infralution.VirtualData.InvalidQuery' occured in infralution.virtualdata.dll

Additional info: Invalid Query: SELECT * FROM mainlist
Error: Item cannot be found in the collection corresponding to the requested name or ordinal.


Any ideas on what could be causing this?
Back to top
View user's profile Send private message
Infralution



Joined: 28 Feb 2005
Posts: 5027
PostPosted: Sun Oct 22, 2006 10:39 pm    Post subject: Reply with quote
VDO is simply a layer ontop of standard ADODB that provides support for databinding. The actual query processing is done by ADODB. This means that both your connection string and the generated queries need to be valid for ADO. The advantage of this is that there is a wealth of information out on the net about configuring ADO for various databases.

I would try passing the user and password as parameters to the VirtualConnection constructor rather then including them in your connection string.

If you are still having problems try creating the ADO connection and opening a recordset directly eg

Code:
con = new ADODB.ConnectionClass();
con.CursorLocation = ADODB.CursorLocationEnum.adUseServer;
con.Open("Provider=PostgreSQL OLE DB Provider;Data Source=localhost;Location=postgres", "id", "pwd", -1);
ADODB.Recordset rs = new ADODB.RecordsetClass();
rs.Open("SELECT * FROM mainlist ", con, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic, -1);


If you can't get ADO to open the recordset with your connection string then you won't get VDO to work. Unfortunately ADO can be a bit cryptic in the error messages it returns when things go wrong. Another thing to check is what CursorLocation, CursorType and LockType PostgreSQL supports through ADO. The settings above are the defaults used by VDO - they generally provide the best performance - however if your database doesn't support them you may get errors.
_________________
Infralution Support
Back to top
View user's profile Send private message Visit poster's website
WaqasK



Joined: 21 Oct 2006
Posts: 32
PostPosted: Mon Oct 23, 2006 6:49 pm    Post subject: Reply with quote
Guys,

I've tried acessing the db directly with ADODB and it seems to work fine - I could get data from it. I also changed my conection string to something without the u/n and p/d. I still seem to be getting the error though. I attempted to change the Cursortype, Locktype and CursorLocation settings to what seemed to be the postgres standard but I still could not get it to work.

One thing that might be worth mentioning is that when CursorLocation value is adUseClient a different error message is returned:

Code:
Error: Multiple-step operation generated error. Check each status value.


However when the value is set to adUseServer the original error is returned.

Below is the code I am curently using:

form 1:
Code:


loadDB.Open(("Provider=PostgreSQL OLE DB Provider;Data Source=localhost;Location=postgres";)
loadDB.Connection.CursorType = CursoTypeEnum.adOpenKeyset
loadDB.Connection.LockType = CursoTypeEnum.adLockOptimistic
loadDB.Connection.Connection.CursorLocation = CursorTypeEnum.adUseClient/adUseServer

dim sql as string = "SELECT * FROM mainlist"
_virualtree.DataSoure = New mainRecordSet(sql)


loadDB Module:
Code:

Public Sub Open(byref conn as string)
    if virtualconn is nothing then
         virtualconn = new virtualConnection(conn, "uid", "pwd", -1, 50)
    end if
end Sub

list Class:

Code:

Public Class mainRecordset
     Inherits VirtualRecordset

Public Sub New(Byref statement as String)
    MyBase.New(GetType(mainlist), loadDB.connection, "mainlist", "ID", statement)
End Sub

End Class
Back to top
View user's profile Send private message
Infralution



Joined: 28 Feb 2005
Posts: 5027
PostPosted: Mon Oct 23, 2006 10:19 pm    Post subject: Reply with quote
Are you still getting the same error message?

Can you please post the exact code (copy and paste) that you are using to connect using ADO and VDO. The code you have posted above has obviously been typed in (it has obvious syntax errors). This might help us see if there is some kind of small typo error causing your problem.

What version of Postgres are you using? We will check your connection strings here.
_________________
Infralution Support
Back to top
View user's profile Send private message Visit poster's website
Infralution



Joined: 28 Feb 2005
Posts: 5027
PostPosted: Tue Oct 24, 2006 10:08 am    Post subject: Reply with quote
OK - we have done some testing with PostgreSQL and there is an issue (actually two). The first problem is that for some reason there is a problem in the VirtualConnection.InitializeRecordsetProperties method that is causing the error you are seeing. The InitializeRecordsetProperties method is really only required for SQLServer - but for some reason the PostgreSQL implementation of recordset properties is causing an issue. We will get a fix out for this ASAP. In the meantime if you want a workaround you can derive a new VirtualConnection class and override the InitializeRecordsetProperties method with a blank implementation eg

Code:
public class MyConnection : Infralution.VirtualData.VirtualConnection
{
    public MyConnection(string connectionString, string userID,  string password, int options, int recordsetCacheSize)
        : base(connectionString, userID, password, options, recordsetCacheSize)
   {
   }

    protected override void InitializeRecordsetProperties(ADODB.Recordset rs)
    { 
    }
}


The second issue is that it appears that PostgreSQL does not support the recordset.RecordCount property with ServerSide cursors. If you use a server side cursor the RecordCount property is always -1. VDO needs the RecordCount property to be implemented in order to provide an implementation of the IList.Count property required for databinding.

A solution to this is to use ClientSide cursors. To do this place the following line of code after creating the VirtualConnection:

Code:
VirtualConnection vc = new MyConnection("...")
vc.Connection.CursorLocation = ADODB.CursorLocationEnum.adUseClient;


Unfortunately using client side cursors will lose you some of the benefits of VDO because it will use more memory then server side cursors.
_________________
Infralution Support
Back to top
View user's profile Send private message Visit poster's website
Infralution



Joined: 28 Feb 2005
Posts: 5027
PostPosted: Tue Oct 24, 2006 10:49 am    Post subject: Reply with quote
Another issue with PostgreSQL appears to be that the driver does not support Updatable Cursors (see http://groups.google.com.au/group/comp.databases.postgresql.general/browse_frm/thread/e0cd4cba2a3443c6/ce7212df28c6d512?lnk=st&q=postgreSQL+ADO+adLockOptimistic&rnum=10&hl=en#ce7212df28c6d512


This means you cannot change recordset values using ADO. eg code like

Code:
recordset.Fields["Name"].Value = "Fred";


will throw an error. Obviously this also means that VDO will be unable to change record values (for instance with the user editing a grid)

It might be time to explore some other database options (eg Access, MySQL, SQLExpress)
_________________
Infralution Support
Back to top
View user's profile Send private message Visit poster's website
Infralution



Joined: 28 Feb 2005
Posts: 5027
PostPosted: Wed Oct 25, 2006 12:33 am    Post subject: Reply with quote
We have now released version 1.2.0 of Virtual Data which fixes the issue with PostgresSQL raising an InvalidQuery exception with the following error:

Quote:
Item cannot be found in the collection corresponding to the requested name or ordinal


Note that Version 1.2.0 now uses a stronger licensing key and so your existing 1.1 key will not work with this version. Simply email us details of your 1.1 key and purchase and we will email you a new key free of charge.
_________________
Infralution Support
Back to top
View user's profile Send private message Visit poster's website
WaqasK



Joined: 21 Oct 2006
Posts: 32
PostPosted: Wed Oct 25, 2006 5:42 pm    Post subject: Reply with quote
Guys,

Thanks for all your help, he support has been fantastic as per usual. I've decided to try and test out mySQL as a possible alterntive to postgreSQL. Unfortunately I'm experiencing problems with this too.

I downloaded MySQL 5 Community along with MySQL ODBC 3.51 to connect to the database. I'm using the following connection string

Code:
loadDB.Open("Driver={MySQL ODBC 3.51 Driver};Server=localhost:Database=docdata;"


When I attempt to run the code the database seems to connct fine but when the code gets to the _VirtualTree.ResumeLayout() line the system returns the following error:

Code:
An unhandled exception of type 'System.ArgumentException' occurred in system.windows.forms.dll

Additional information: '-1' is not a valid value for 'LargeChange'.  'LargeChange' must be greater than or equal to 0.


I assume this was reffering to the options value in the virtualConnection function. I played around with the value for a while to see what would happen and I found that when the value is set to anything less than 1 the above error is returned. However, if the value is 1 or greater then the followng error is returned:

Code:
An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in infralution.virtualdata.dll

Additional information: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.


The above error occurs when the code gets to the virtualConnetion function.

I know this is getting a bit tiresome but any help would be appreciated.

Regards,

WaqasK
Back to top
View user's profile Send private message
Infralution



Joined: 28 Feb 2005
Posts: 5027
PostPosted: Wed Oct 25, 2006 10:27 pm    Post subject: Reply with quote
The issue is almost certainly not the Options - which should be left as -1.

What parameters are you using for CursorType?

After you create your VirtualRecordset check the Count property ie

Code:
Dim mr as New mainRecordSet(sql)
Debug.WriteLine("Count" + mr.Count)


If this Count is -1 then it means that the database does not support the ADODB.Recordset.RecordCount property for this type of CursorType/CursorLocation. VDO needs the database cursor to implement this property to be able to bind.

Try the different cursor type options to see what the database supports.

As with PostgreSQL setting the CursorLocation to adUseClient will also almost certainly work - because in this case ADO handles the cursor rather than relying on the on the underlying server cursor.
_________________
Infralution Support
Back to top
View user's profile Send private message Visit poster's website
WaqasK



Joined: 21 Oct 2006
Posts: 32
PostPosted: Thu Oct 26, 2006 12:04 pm    Post subject: Reply with quote
Yeah guys changing the the CursonLocation property to adUseClient worked. I do have one question though.

Earlier you mentioned that changing the CursorLocation property to asUseClient would degrade performance. Can I ask by how much? Would performance degrade serverly if I had 2000+ records to load?
Back to top
View user's profile Send private message
Infralution



Joined: 28 Feb 2005
Posts: 5027
PostPosted: Thu Oct 26, 2006 11:15 pm    Post subject: Reply with quote
Well notionally using a client side cursor should have some impact on performance - however I've just done some benchmarking with an application containing over 6000 records and could see virtually no difference in either memory consumption or startup time. So probably for your size database it won't be significant
_________________
Infralution Support
Back to top
View user's profile Send private message Visit poster's website
WaqasK



Joined: 21 Oct 2006
Posts: 32
PostPosted: Sat Nov 04, 2006 3:58 pm    Post subject: Reply with quote
Guys,

I'm afraid to say I'm having problems again. I've downloaded Ver 1.2.1 of VDO but still the errors persist. Right now i'm getting this error:

Code:
An unhandled exception of type 'System.Reflection.TargetInvocationException' occured in system.dll

Additional information: Property accessor 'hisSubListRecords' on object 'RA_GUI.mainList' threw the following exception: 'Type of argument 'Number' is 'SystemUInt32', which is not numeric.'


It occurs at the ResumeLayout() line. I've added the rest of te code I use to access my MySQL database below:

'mainform

Code:
loadMainDatabase.Open("Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=docData;option=3")
loadMainDatabase.Connection.CursorLocation = CursorLocationEnum.adUseClient

VirtualTree1.DataSource = New mainRecordset("SELECT * FROM mainlist")
VirtualTree1.ResumeLayout()
VirtualTree1.UpdateRows()


'loadMainDatabase

Code:
Module loadMainDatabase

    Private _virtualConnection As VirtualConnection
    Private _random As New Random

    '' Open the database connection
    Public Sub Open(ByRef conn As String)
        If _virtualConnection Is Nothing Then
            _virtualConnection = New VirtualConnection(conn, "root", "password", -1, 100)
        End If
    End Sub 'Open

    '' Close the license database connection
    Public Sub Close()
        If Not (_virtualConnection Is Nothing) Then
            _virtualConnection.Connection.Close()
            _virtualConnection = Nothing
        End If
    End Sub 'Close

    '' Get the virtual connection used to access the Database
    Public ReadOnly Property Connection() As VirtualConnection
        Get
            Return _virtualConnection
        End Get
    End Property

End Module


'mainList

Code:
Public Class mainList
    Inherits VirtualRecord

    '' Set/Get the Col1 of this vehicle
    Public Property ID() As Integer
        Get
            Return Fix(GetValue("ID"))
        End Get
        Set(ByVal Value As Integer)
            SetValue("ID", Value)
        End Set
    End Property

    '' Returns the child records for this record
    Public ReadOnly Property hisSubListRecords() As historyRecordset
        Get
            Return New historyRecordset(Me)
        End Get
    End Property
End Class 'mainList

'' Defines a strongly typed recordset for mainList.
Public Class mainRecordset
    Inherits VirtualRecordset

    Public Sub New(ByRef statement As String)
        MyBase.New(GetType(mainList), loadMainDatabase.Connection, "mainlist", "ID", statement)
    End Sub

End Class


'hisSubList

Code:
Public Class hisSubList

    Inherits VirtualRecord

    '' Set/Get the Col1 of this vehicle
    Public Property ID() As Integer
        Get
            Return Fix(GetValue("ID"))
        End Get
        Set(ByVal Value As Integer)
            SetValue("ID", Value)
        End Set
    End Property

End Class 'hisSubList

Public Class historyRecordset
    Inherits VirtualRecordset

    '' Create a new historyRecordset
    Public Sub New()
        MyBase.New(GetType(hisSubList), loadMainDatabase.Connection, "hissublist", "hisID", "SELECT * FROM hissublist")
        'Me.SortField = "Col1"
    End Sub 'New

    '' Create a new historyRecordset for the given record
    Public Sub New(ByVal list As mainList)
        MyBase.New(GetType(hisSubList), loadMainDatabase.Connection, "hissublist", "hisID", String.Format("SELECT * FROM hissublist WHERE ID = {0}", list.ID))
    End Sub
End Class


Thanks for any help you can provide.
Waqas
Back to top
View user's profile Send private message
Infralution



Joined: 28 Feb 2005
Posts: 5027
PostPosted: Sun Nov 05, 2006 10:18 pm    Post subject: Reply with quote
What is the call stack when the error occurs?

This looks like a problem with accessing a specific field of the recordset. Does your hissublist table have a column called "Number" which is an Unsigned Integer? Because VB.NET does not support Unsigned Integers it may be that it is having some problems with this.

It would be best to check your recordset code separately from binding to Virtual Tree ie create a historyRecordset and then try to access the "Number" field directly

Code:

Dim hr as new historyRecordset(..)
Dim hsl as hisSubList = hr(0)
Debug.Writeline("Number: " + hr("Number"))


If you are still have problems may be you could email us a copy of your MySQL database and we will try to find the issue.
_________________
Infralution Support
Back to top
View user's profile Send private message Visit poster's website
WaqasK



Joined: 21 Oct 2006
Posts: 32
PostPosted: Mon Nov 06, 2006 9:09 pm    Post subject: Reply with quote
Guys,

I did some checking in my database and found that the error was caused by a foreign key that was defined as an unsigned integer. I removed it and added it again as a signed integer and that did the trick!

Thanks for all your help as per usual.

Waqas
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Infralution Support Forum Index -> Virtual Data Objects Support All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group