Re: OdbcDataReader

From: Joaquim Pinto (joaquimfpinto_at_solverde.pt)
Date: 09/19/04 

Date: 19 Sep 2004 10:05:51 -0700

Hi Brett,

I did as you told and i find out that the connection to the MySql
database closes and if i go to the MySql mamager i not able to open it
as it gives the same error "unable to open.....".
Is it because i'm openning and closing the connection to many times?
Thanks,
Joaquim
"Baavgai" <brettmc@gmail.com> wrote in message news:<ciakp4$ob5@odak26.prod.google.com>...
> While I'm not sure about your 1900 row magic number, I can offer a
> couple of other approaches.
>
> First would be to just catch the error. This is be slower than your
> current method, but less likely to snag on concurrence problems. Put a
> try..catch block around your inserts and accept that some are going to
> throw a fit.
>
> Second, depending on your data, is faster than your current or my
> previously mentioned option. Have a temporary staging table in the
> destination database. Clear it and insert everything into it. Then,
> from inside the destination database, delete matching records in the
> staging table and just push the rest in.
>
> This can be optimized further with timestamps or some kind or row
> versioning.
>
> Hope this helps,
> Brett
>
> Joaquim Pinto wrote:
> > Hello All,
> >
> > I'm developing an application that is using odbcconnection,
> > odbccommand and odbcdatareader to access different tables in two
> > different databases, one Access and the other MySql.
> > The main idea is to pass all data from the Access database, 98300
> > records, into MySql.
> > Mainly I'm using SQL language to access both databases.
> > In order to make sure I don't have two records with the same data
> this
> > is what I did:
> > After create both connections I open the Access database and open a
> > particular table.
> > While the odbccommand is reading the Access table I open the other
> > connection and table from MySql and by passing some string from the
> > Access table I try to find if that record already exists on the MySql
> > table.
> > If it's not the case, by using the HasRows method, I open a different
> > connection and use the INSERT command from SQL to insert that record
> > on the MySql table.
> > Of course after due the instructions in the MySql database I close
> > both commands and connections and when I reach the end of the Access
> > database I do the same.
> > Basically for every record on the Access database I'm opening and
> > closing two connections to MySql, one is checking if the record
> exists
> > and the other one is inserting the record if it doesn't exists.
> > All those instructions seams to be correct, but whenever I reach more
> > or less 1900 records both commands that are reading the MySql table
> > give me an error and when I check the MySql database I can see there
> > the records.
> > I don't think this is a problem from MySql as I can do the same job
> > with VB6 and I never got this problem even if I don't use SQL with
> > VB6.
> > Can anyone give a hand on this?
> > Regards,
> > Joaquim Pinto

Relevant Pages

  • Re: hitting the limits
    ... relatively straight forward MySql database. ... 10K visitors a month and an 80Mb database are nothing. ... heavily loaded server. ... But the connection will hang around until the garbage ...
    (comp.lang.php)
  • Re: VFP8 & MySQL
    ... > VFP functions like NVL to a MySQL one, definitely it made the switch very ... > About your connection problem, I have mine setup as default for 100 ... As for the server, we left it ... The server crashed and corrupted the database. ...
    (microsoft.public.fox.programmer.exchange)
  • Re: VFP8 & MySQL
    ... Main Location and 3 branches (these are remotely accessing MySQL at the main ... About your connection problem, I have mine setup as default for 100 ... As for the server, we left it alone. ... The server crashed and corrupted the database. ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Java and MySql program example ?
    ... > I am trying to learn Java and need an example program with MySql ... > MySql database. ... Database connection established ...
    (comp.lang.java.programmer)
  • Re: MySQL Database problem (probably already solved in a message, but this is somewhat urgent)
    ... MySQL server has a database with a table, ... columns, an FSR column, and a password column. ... checked if the supposed arrays that were returned were actually arrays ...
    (comp.lang.php)
Loading