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 

sqlite, oracle, mysql

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





PostPosted: Wed Nov 09, 2005 3:57 pm    Post subject: sqlite, oracle, mysql Reply with quote

Can I use VDO with SQLite, Oracle, and MySQL databases?
Back to top
Infralution



Joined: 28 Feb 2005
Posts: 5027

PostPosted: Wed Nov 09, 2005 10:39 pm    Post subject: Reply with quote

Yes - because it uses a standard ADO connection you can use any provider that you could with ADO. See Microsoft's ADO documentation for information about ADO providers.
_________________
Infralution Support
Back to top
View user's profile Send private message Visit poster's website
rp
Guest





PostPosted: Wed Nov 09, 2005 11:54 pm    Post subject: Reply with quote

Which ADO providers specifically would allow VDO to be used with those databases?
Back to top
Infralution



Joined: 28 Feb 2005
Posts: 5027

PostPosted: Thu Nov 10, 2005 2:01 am    Post subject: Reply with quote

The following web site has sample OLEDB and ODBC connections strings for various databases including Oracle and MySQL.

http://www.carlprothman.net/Default.aspx?tabid=81

This doesn't have SQLite listed - but a quick visit to the SQLite web pages revealed that you can get ODBC and OLEDB wrappers for SQLite that you should be able to use from ADO.
_________________
Infralution Support
Back to top
View user's profile Send private message Visit poster's website
rp
Guest





PostPosted: Fri Nov 11, 2005 12:40 am    Post subject: Reply with quote

Excellent!

I am having a problem with a SQLite driver though.

First, I installed the driver found at http://luggle.com/~sean/

Then I created a simple SQLite database using SQLite Database Browser found at http://sqlitebrowser.sourceforge.net

CREATE TABLE test (data TEXT, id INTEGER PRIMARY KEY)

I added a couple rows to the test table.

Then I modified the SimpleDataBinding_VB project's MainForm constructor:

Code:

    Public Sub New()
        Const connectionString As String = "Provider=OleSQLite.SQLiteSource.1; Data Source=i:\test.db"

        '
        ' Required for Windows Form Designer support
        '
        InitializeComponent()

        ' create the connection
        '
        Dim connection As New VirtualConnection(connectionString, "", "", -1, 5)

        ' set the data grid data source to a recordset
        '
        _dataGrid.DataSource = New VirtualRecordset(connection, "test", "id", "SELECT * FROM test")
    End Sub 'New


When I run it I get this exception when the program tries to instantiate the VirtualRecordSet:

Code:

An unhandled exception of type 'Infralution.VirtualData.InvalidQuery' occurred in infralution.virtualdata.dll

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


Is this error being caused by the driver or VirtualRecordSet?
Back to top
Infralution



Joined: 28 Feb 2005
Posts: 5027

PostPosted: Fri Nov 11, 2005 12:49 am    Post subject: Reply with quote

This exception is the result of the ADO connection returning an error when the recordset was opened. This can be because there is a driver error or because your query is badly formed (eg references non-existant fields or tables).

In this case the error returned by ADO:

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


seems to imply that either SQLite doesn't support wildcarding or else you've mis-spelled the table name. You could try using a named field in the Select clause. You can also try just opening a connection and recordset directly using ADODB. If you have problems with this then I would contact the driver author.
_________________
Infralution Support
Back to top
View user's profile Send private message Visit poster's website
rp
Guest





PostPosted: Fri Nov 11, 2005 4:43 pm    Post subject: Reply with quote

Ok, I tried using ADODB directly and it worked:

Code:

        Const connectionString As String = "Provider=OleSQLite.SQLiteSource.1; Data Source=i:\test.db"
        Dim SQL As String = "SELECT * FROM test"
        Dim Conn As New ADODB.Connection
        Dim Records As New ADODB.Recordset
        Dim Adapter As New OleDbDataAdapter
        Dim Data As New DataSet("test")
        Conn.ConnectionString = connectionString
        Conn.Open()
        Records.Open(SQL, Conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic, -1)
        Adapter.Fill(Data, Records, "test")
        _dataGrid.DataSource = Data
        Conn.Close()


Is VirtualRecordSet modifying the query at all? Can you try a simple SQLite project to see if you can reproduce the error?
Back to top
Infralution



Joined: 28 Feb 2005
Posts: 5027

PostPosted: Fri Nov 11, 2005 9:45 pm    Post subject: Reply with quote

The query that VDO does is shown in the exception ie "Select * from test". The only other difference would be the CursorType and LockType. The defaults are adOpenKeyset and adLockOptimistic respectively. It's possible that SQLite does not support these - you can change the default values for these by setting the CursorType and LockType properties on the connection before using it.
_________________
Infralution Support
Back to top
View user's profile Send private message Visit poster's website
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