creating a bunch of tables from a central table - Database Discussion Boards

johnb10-Nov-02 22:27

10-Nov-02 22:27

For the Client:

It will be offline mostly which is why the need for a local cached version. It requires alot of add, del, insert,select, update operations to be done on the local database.

A) accessing server database
I had considered using MSXML(XMLHTTP ) for the accessing the server database, but hadnt considered using MSXML for the local database.

Not sure if MSXML is appropriate for database solution.

Is a small DAO redistributable still availble from MS?
In that case I could use DAO?

Cheers
John

Re: Which DB to use?

Daniel Turini11-Nov-02 0:24

Daniel Turini

11-Nov-02 0:24

johnb wrote:
Is a small DAO redistributable still availble from MS?
In that case I could use DAO?
Argh!

I would keep a safe distance from DAO, if I were you.

And I have some nightmare stories I lived with MDAC redistributables, too Cry | :((

lazy isn't my middle name.. its my first.. people just keep calling me Mel cause that's what they put on my drivers license. - Mel Feik

Re: Which DB to use?

johnb11-Nov-02 0:36

johnb

11-Nov-02 0:36

Maybe ADO is the answer, XP, 2000 already have a version of MDAC installed,
I guess, win98 users will have to install MDAC2.7 approx 5MB download.

Re: Which DB to use?

Christian Graus11-Nov-02 0:29

Christian Graus

11-Nov-02 0:29

MSDE is basically SQL Server with no fancy GUI and only 5 connections at a time. It's designed so I can write an app without needing to buy SQL Server, then my customers can use it with SQL Server, but if you don't need more than 5 connections at a time it might be worth a look.

Christian

No offense, but I don't really want to encourage the creation of another VB developer. - Larry Antram 22 Oct 2002

Hey, at least Logo had, at it's inception, a mechanical turtle. VB has always lacked even that... - Shog9 04-09-2002

During last 10 years, with invention of VB and similar programming environments, every ill-educated moron became able to develop software. - Alex E. - 12-Sept-2002

Re: Which DB to use?

johnb11-Nov-02 0:37

johnb

11-Nov-02 0:37

yeah had a look at MSDE, whats the redist size?

Relationship problem

leppie10-Nov-02 4:02

leppie

10-Nov-02 4:02

Hi all

No, this aint personal Smile | :)

I have made a SQL DB with a couple of related tables but were a bit "lazy" setting up relationships, basically each relationship were setup with all the options unticked. However, I realise now that some options are obviously necessary, like checking on creation, but I'm not really sure what all the options do.

Firstly I would like to know what does each one do, and how it affects the tables (like when deleting records) , and secondly how to get rid of the couple of "rogue" entries I have in my tables that does not link to anything as I deleted them (to test mind you Smile | :)

).

Help appreciated Smile | :)

"I dont have a life, I have a program."

Re: Relationship problem

Daniel Turini10-Nov-02 22:26

Daniel Turini

10-Nov-02 22:26

You really should read my article: SQL Server DO's and DONT's[^] Wink | ;)

But for the "phantom" child rows, if the tables are not too big (this is not a high performance solution), you can run a simple DELETE:

[I'm assuming a master table and a detail table and ID as the primary keys on the tables.]
DELETE detail
WHERE ID not IN (SELECT ID from master)

lazy isn't my middle name.. its my first.. people just keep calling me Mel cause that's what they put on my drivers license. - Mel Feik

creating a bunch of tables from a central table

Like2Byte8-Nov-02 10:08

Like2Byte

8-Nov-02 10:08

Hey, thanks for reading my thread.

In MS Access I have this central table called 'TableOfTables.' It contains the following fields(types):DB_FIELD0(autonumber), DB_CITIES(string), and DB_LENGTH(number).

It contains approximately 400 entries: An auto-generated number(1, 2, 3, ...), a city name, and the number of entries for that city in the last field.

I need to extract the city name, from TableOfTables, to create a new table within the database such that the new table's name will be the extracted city name and the table will contain an address field, a phone number, an e-mail address, etc. (This isn't the exact stuff I need but it helps with the point and it's FAR shorter than what I really need.;P)

I Opened the DB in Access, created a queary with only TABLEOFTABLES, switched to the SQL panel and tried:

CREATE TABLE TABLEOFTABLES.DB_REGIONS(DB_FIELD0 integer not null, DB_FIELD1 varchar(20) not null, DB_FIELD2 varchar(20) not null, DB_FIELD3 varchar(20) not null)

hopeing to extract the city names from TABLEOFTABLES and generate these ~400 tables with the fields given.

Confused | :confused:

What is the SQL statement I could use to complete this task? Confused | :confused:

Thanks again.

Re: creating a bunch of tables from a central table

Pyt8-Nov-02 10:17

Pyt

8-Nov-02 10:17

Hi,

I don't think you can do that in SQL, you cannot use a variable as the table name in the "create table" statement.

I don't think it's a good idea to do that anyway. You should use just create another table and create a foreign key in that table referencing the autonumber of TABLEOFTABLES, that should solve your problem.

Pyt.

Re: creating a bunch of tables from a central table

Vagif Abilov10-Nov-02 6:03

Vagif Abilov

10-Nov-02 6:03

You can create table programmatically using ADOX (ADO extensions to manage database dictionary). However, like others said, it's not very good idea to create a table per city. Basically it's bad design. Why can't you store information for all cities in a single table and use indexes to retrieve data that belongs to each city. Believe me, it's so much easier.

Vagif Abilov
MCP (Visual C++)
Oslo, Norway

Hex is for sissies. Real men use binary. And the most hardcore types use only zeros - uppercase zeros and lowercase zeros.
Tomasz Sowinski

Re: creating a bunch of tables from a central table

Like2Byte11-Nov-02 2:34

Like2Byte

11-Nov-02 2:34

<<why can't="" you="" store="" information="" for="" all="" cities="" in="" a="" single="" table="" and="" use="" indexes="" to="" retrieve="" data="" that="" belongs="" each="" city.="" believe="" me,="" it's="" so="" much="" easier.="">>

Believe me, I wish I could for this project!! I have to use one table per city because the device I'm programming (Pocket PC - 2002) using AppForge 3.1 doesn't do SQL statements all that well. Using a 'select * from CITIES' won't work because AppForge (reportedly) only returns EXACT matchs. Finding similarities (LIKE) is a NO-GO.

1) I can't create new tables based upon the results from a query.

2) I can't open and close DBs or tables within a DB because of a memory leak created BY AppForge (It's written in their documentation) - not my code.

3) Management has just set fire to the hoop they want me to jump through.

On the other hand, I'm not a VB programmer, per se, although I've been programming VB on a "professional level" (scary, huh?) for the better part of six months. Being a C/VC++ programmer, one could imagine my disparity toward programming in VB. Not that I don't want to learn VB but I should at least learn before being forced to program in it. Confused | :confused:

Sorry for the rant. This 'problem' has been wieghing on my concious for the better part of this development cycle. Frown | :(

DataSets and XML

Karen8-Nov-02 6:31

Karen

8-Nov-02 6:31

Hi,
I have converted a delimited text file to a DataTable and added to a new DataSet (using VB and ADO.NET). I will add more than 1 table to this DataSet.
Then my next task is to convert these tables into serveral xml files.
For example, for the Category Table, I will have to make an xml file which contain datas where category num < 1000, and another xml file which contain datas where category num > 1000. However, since the WriteXml only belongs to the DataSet Object, but not the DataTable object, how do I do this????? Confused | :confused:

??????

Please help meeeee!!!! Cry | :((

Thanks a million!
Karen

Re: DataSets and XML

Rickard Andersson²⁰10-Nov-02 20:57

Rickard Andersson²⁰

10-Nov-02 20:57

Go through all rows in the table in a loop and then check if num < 1000.
Then store each row in a new table (add new DataRow to a DataTable) and assign that table to a DataSet and call WriteXml....

Do you understand?

Rickard Andersson@Suza Computing
C# and C++ programmer from SWEDEN!
UIN: 50302279
E-Mail: nikado@pc.nu
Speciality: I love C#, ASP.NET and C++!

Re: access to a mysql-database

Vasudevan Deepak Kumar9-Nov-02 0:19

Vasudevan Deepak Kumar

9-Nov-02 0:19

Hey Thomas,

I am not too sure of MySQL Administration however with some use of MySQL, I think MySQL needs to be informed of the IP Addresses or hosts that need to be connected. If the connecting php and the mysql resides on the same host, then you can use

localhost

instead, since localhost can always connect.

Deepak Kumar Vasudevan
http://deepak.portland.co.uk/

Serious and Dreaded OleDB Adapter DB2 Driver Crashing Problem

Vasudevan Deepak Kumar7-Nov-02 20:00

Vasudevan Deepak Kumar

7-Nov-02 20:00

Hi there,

I am using OleDB to connect to an IBMDB2 database on an AIX Server on Rs/6000 and also to a Linux based IBMDB2 database.

All is okay but when there is a duplicate record insertion, which has to be handled by a try catch block and return to the calling file, IIS/ASP.NET Worker Process fails miserably with a error message called

Server Application Unavailable. Event Viewer logs a message
aspnet_wp process stopped unexpectedly.

When I tried to run the query via Console Application, the try catch block is not able to handle this type of SQL Error alone. Instead it raises an Exception

System.ExecutionEngineException

The problem does not seem to be with the application, since it can handle any SQL statement failures and can gracefully return the error message except for the the dreaded Primary Key/Unique Key violation exception.

A Free Utility for SQL/OlEDB called QueryExpress from http://www.albahari.com/ also fails and crashes with the abovesaid exception.

We doubt whether the OLEDBDriver (Provider=IBMDADB2) is the culprit since if we use Microsoft ODBC.NET (Driver=IBM DB2 ODBC Driver), the above exception is gracefully handled.

Confused | :confused:

While anything via IBM OleDb Driver fails, IBM DB2 Command Center is able to report the error gracefully along with some SQLState etc. Confused | :confused:

But my question is there any other OLEDB providers for DB2 database from Microsoft or any other vendor?

Thanks in advance...

Deepak Kumar Vasudevan
http://deepak.portland.co.uk/

Trivial SQL (i think)

alex.barylski7-Nov-02 11:33

alex.barylski

7-Nov-02 11:33

I'm a newbie with SQL so please take it easy on me... Blush | :O

Anyways...

I have 2 tables

clients ( email, name, phone, address, agencyid )
agency ( agencyid, email, name, phone, address, website )

I need to pull all the agency records out and determine if they have clients/members...???

How would I do this...i'm so lost???

Currently I have something like:

SELECT agency, clients WHERE agency.agencyid = clients.agencyid AND this is where i'm lost

Is there anyway inside the SQL statement I can determine if an agency has no members...? This is what I need returned...agencies with no members...???

Do I have to use a for loop?
I'm using mySQL and PHP BTW

$query = "SELECT * agency";
$result = mysql_query($query); // Store all agencies inside result set
$row = mysql_fetch_array($result);

// Iterate all agencies and determine if they have members
for($i=0; $i<mysql_num_rows($result); $i++){
  $clientResult = mysql_query("SELECT * clients WHERE clients.agencyid = $row[$i].agencyid");
  if(mysql_num_rows($clientResult) == 0)
    // Store this record for display
}

Do I have to do it like the above or can I accomplish this from ONE SQL statement...? Or Am I doing it all wrong...?

Thanx! Smile | :)

"An expert is someone who has made all the mistakes in his or her field" - Niels Bohr

Re: Trivial SQL (i think)

Paul Watson7-Nov-02 11:47

Paul Watson

7-Nov-02 11:47

Hockey wrote:
Is there anyway inside the SQL statement I can determine if an agency has no members...? This is what I need returned...agencies with no members...???

The in statement in SQL is brilliant. Basically in your case you need to do this: select * from agency where Not AgencyID in (select AgencyID from Clients)

That should return all agency rows which do not occur in clients.

in can be used many ways. For instance if you have a pre-defined list of values to check for you can do: select * from agency where Not AgencyID in (1, 2, 3, 4)

Paul Watson
Bluegrass
Cape Town, South Africa

Ray Cassick wrote:
Well I am not female, not gay and I am not Paul Watson

Re: Trivial SQL (i think)

alex.barylski7-Nov-02 12:58

alex.barylski

7-Nov-02 12:58

I've actually used the in statement before, but only like in your second example...

Anyways, I appreciate the help Paul...I was getting pretty desperate...

One question? I just read a little more on the subject and it appears I could accomplish something very similar using a left join...???

I will have to read up on this appraoch more, but which would be more efficient...?

Thanx again

Cheers! Smile | :)

"An expert is someone who has made all the mistakes in his or her field" - Niels Bohr

Re: Trivial SQL (i think)

alex.barylski7-Nov-02 13:10

alex.barylski

7-Nov-02 13:10

Hey Paul, i'm getting the following error, you wouldn't care to help me figure out why...? Smile | :)

<br />
SQL-query :  <br />
<br />
SELECT * FROM agency WHERE NOT agencyid IN (SELECT agencyid FROM clients) LIMIT 0, 30<br />
<br />
MySQL said: <br />
<br />
<br />
You have an error in your SQL syntax near 'SELECT agencyid FROM clients) LIMIT 0, 30' at line 1<br />

"An expert is someone who has made all the mistakes in his or her field" - Niels Bohr

Re: Trivial SQL (i think)

Paul Watson7-Nov-02 21:08

Paul Watson

7-Nov-02 21:08

Hockey wrote:
MySQL

Doh, I just assumed you meant Microsoft SQL Server. I have about 2 hours experience with MySQL and most of it was spent cursing Big Grin | :-D

There are some differences between the TSQL that MS SQL Server uses and the SQL that MySQL uses.

*the following is pure guessing so I could be utterly wrong*

If I had to guess, the error seems to be saying that a in statement has a maximum of 30 returnable rows. There might be a setting in MySQL for this.

If none of that works then in answer to your other question about rather using a join..

SELECT
    Clients.ClientID, 
    Clients.AgencyID AS ClientAgencyID, 
    Agency.AgencyID, 
    Agency.Name AS AgencyName
FROM
    Clients FULL OUTER JOIN
    Agency ON Clients.AgencyID = Agency.AgencyID
WHERE    (Clients.ClientID IS NULL)

Sorry but I can't test it against a MySQL box so I do not know if it works on MySQL, works on MSSQL though.

As for performance I am no DBA so I really could not tell you which is faster.

Paul Watson
Bluegrass
Cape Town, South Africa

Ray Cassick wrote:
Well I am not female, not gay and I am not Paul Watson

Re: Trivial SQL (i think)

markkuk8-Nov-02 2:08

markkuk

8-Nov-02 2:08

MySQL doesn't support subSELECTs at all. You must replace the nested query, see the MySQL manual.

Re: Trivial SQL (i think)

alex.barylski8-Nov-02 8:11

alex.barylski

8-Nov-02 8:11

Brutal...

I ended up using multiple queries inside a for loop...proabbly not the MOST elegant solution, but it works...

"An expert is someone who has made all the mistakes in his or her field" - Niels Bohr

SELECT ... FOR XML AUTO

Alexandru Savescu6-Nov-02 23:42

Alexandru Savescu

6-Nov-02 23:42

Hello,

I want to use the SELECT some recordsets in an XML format (using the SQL Server 2000 features). I am using Carlos Antollini classes that wrap the ADO database clases. I am declaring a CADORecordset object, open it and use GetFieldValue (0, var), where var is a _variant_t object.

Its type is VT_ARRAY | VT_UI1 (this means an array of bytes, rights?). The parray member looks like this:

cDims = 0x0001; // one dimension array
fFeatures = 0x0080;
cbElements = 0x00000001;
cLocks = 0x0;
pvData = 0xsmth;

Dumping my array of bytes into a file looks awful. However, looking at the code the array looks like an array of BSTRs, but is not the XML encoded data I am expecting.

Can anyone help?
Thanks.

Best regards,
Alexandru Savescu

Best regards,
Alexandru Savescu

Saving a recordset in an XML stream

Alexandru Savescu5-Nov-02 23:33

Alexandru Savescu

5-Nov-02 23:33

I am saving an ADO recordset as an XML. It works fine. However I do not want to save into a file, I would like to save it into a stream, a CString, ostream etc. Anyone have an idea?

Thanks!

Best regards,
Alexandru Savescu

Re: Saving a recordset in an XML stream

Richard Deeming6-Nov-02 2:54

Richard Deeming

6-Nov-02 2:54

You can pass a Stream object to the Save method of the recordset. For example, in IIS 5 you can pass in the Response object, and the XML will be sent straight to the browser.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.