| View previous topic :: View next topic |
| Author |
Message |
rp
Guest
|
 Posted: Wed Nov 09, 2005 3:57 pm Post subject: sqlite, oracle, mysql |
 |
|
| Can I use VDO with SQLite, Oracle, and MySQL databases? |
|
| Back to top |
|
 |
Infralution
Joined: 28 Feb 2005
Posts: 5027
|
| Posted: Wed Nov 09, 2005 10:39 pm Post subject: |
|
|
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 |
|
|
rp
Guest
|
| Posted: Wed Nov 09, 2005 11:54 pm Post subject: |
|
|
| Which ADO providers specifically would allow VDO to be used with those databases? |
|
| Back to top |
|
|
Infralution
Joined: 28 Feb 2005
Posts: 5027
|
| Posted: Thu Nov 10, 2005 2:01 am Post subject: |
|
|
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 |
|
|
rp
Guest
|
| Posted: Fri Nov 11, 2005 12:40 am Post subject: |
|
|
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
|
| Posted: Fri Nov 11, 2005 12:49 am Post subject: |
|
|
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 |
|
|
rp
Guest
|
| Posted: Fri Nov 11, 2005 4:43 pm Post subject: |
|
|
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
|
| Posted: Fri Nov 11, 2005 9:45 pm Post subject: |
|
|
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 |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
