SQLCE 3.5 Table Exist

Question

Post reply

SQLCE 3.5 Table Exist

chris.pink

SSC Veteran

Points: 217
More actions
August 19, 2008 at 8:25 am

#191324

Cannot get the scheme select * from INFORMATION_SCHEMA.Tables where TABLE_NAME = 'test'"
this always returns 0, i Know the table exists, Anyone any idea what i am missing ?
Anybody got any good examples ?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

FroggEye SSC Veteran Points: 295 More actions · Answer 1

I run this on Query Analyzer 3.5 on my WM6 Device

SELECT * FROM Information_Schema.Tables WHERE Table_Name = 'Users'

and get one row returned with the following columns with data in them:

Table_Catalog

Table_Schema

Table_Name

Table_Type

Table_GUID

Description

Table_Propid

Table_Created

Date_Modified

I have tried running this query on Query Analyzer 3.5:

IF EXISTS (SELECT * FROM Information_Schema.Tables WHERE Table_Name = 'Users')

SELECT 'Exists'

ELSE

SELECT 'Doesn't exist'

And it fails every time. But when I run it in SSMS it works every time. Any thoughts on that?

joel-948223 Grasshopper Points: 21 More actions · Answer 2

This is day one for me and SQL Compact. So far I’m very disappointed! I googled for a little background and recall seeing something about multiple statements not being supported.

My solution to the IF EXISTS(..) DROP was to take each piece one by one.

using (SqlCeConnection connection = new SqlCeConnection(_connectString))

{

using (SqlCeCommand command = new SqlCeCommand())

{

//connect

command.Connection = connection;

command.Connection.Open();

//test exists

command.CommandText = sqlCount;

object count = command.ExecuteScalar();

if (count != null && (int)count > 0)

{

//Drop if exists

command.CommandText = sqlDrop;

command.ExecuteNonQuery();

}

//Create Table

command.CommandText = sqlCreate;

command.ExecuteNonQuery();

}

connection.Close();

}

I don’t know if this is the correct solution or not but it is my workaround for now. I don’t see how compact 3.5 is MS preferred application data source over express. No procs, no udfs, no IFs?

Robert Cain-464398 SSC Veteran Points: 288 More actions · Answer 3

Chris, your query should have worked, but here is an example from my blog:

http://arcanecode.wordpress.com/2007/04/16/system-views-in-sql-server-compact-edition-tables/

Also be sure to check out the Arcane Lessons link at the top of my blog, I have several posts about the various system views available in SSCE.

Robert Cain-464398 SSC Veteran Points: 288 More actions · Answer 4

Joel,

You did hit on the right solution. You also asked a question "why is this preferred over SQL Express?"

The simple answer is, it's not. Not necessarily anyway, as with all programming problems it depends on your situation. SSCE was originally designed to be used on mobile devices like PDA's and Smartphones. Only recently (2005) did it make the transition to the desktop.

It's a great solution if all you need to do is store some data in a small, fast, single user database that will let you run queries against it. If you need multi user, network storage, and some of the other features such as stored procs and UDFs then you probably should go with something like SQL Express.

The trade off is in installation. SSCE can be installed with your application, and takes up very little space on the users disk. SQL Express requires a separate install outside the capabilities of the average user, needs tons of disk space and lots of memory.

You have to decide what the needs of your users will be, and thus which database will better suit the problem you are trying to solve with your app.

The nice part is if you start with SSCE, you can transition to SQL Express fairly easily, most of the code you write will work (although you may want to rewrite some parts to take advantage of the SQL Express features).

Robert