2

I am trying to get the schema out of a database, before applying any queries from a local SQL Server Compact Edition file (*.sdf).

So far, I can connect to the database and perform queries when I know the schema.

I have tried two approached and both failed.

First, I try to use

 import adodbapi.schema_table as DB_schema
 temp2 = DB_schema.names(connection_handler)

but this fails with the following error:

raise AttributeError('no such attribute in ADO connection object as="%s"' % item)
AttributeError: no such attribute in ADO connection object as="adoConn"

(this was the easy solution from the reference guide)

Also

connection_handler.get_table_names() #doesn't work

fails...

Then, I tried

sql_query_to_get_all_table_names_from_database = "SELECT Distinct TABLE_NAME FROM information_schema.TABLES"
cursor.execute(sql_query_to_get_all_table_names_from_database)

which also fails with a slightly different message:

adodbapi.apibase.DatabaseError: (-2147352567, 'Exception occurred.', (0, u'Microsoft SQL Server Compact OLE DB Provider', u'Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.', None, 0, -2147217887), None)
Command:
SELECT Distinct TABLE_NAME FROM information_schema.TABLES

Parameters:
[]

The very same query works fine when it is performed on compact view.

Any ideas are more than welcome.

Is there any other approach? (I have tried odbc, SQL Server, but no luck so far)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Christos Tsotskas
  • 21
  • 1

1 Answers1

0

With adodbapi version 2.6.0.7:

Once connected to the database, the connection has the method:

myconnection.get_table_names()

which returns the schema, as explained in the reference guide:

http://adodbapi.sourceforge.net/quick_reference.pdf

For me, this only worked after changing the connector cursor location:

myconnection.connector.CursorLocation = 2

This changes the cursor location to the server side, a trick I found here:

How do i correctly query a sql ce 4.0 database file using adodbapi?

johnDanger
  • 1,990
  • 16
  • 22