Make a copy of tables using C API

Hi,

I have been trying to write a program that uses the MySQL C API to backup a
source table into a backup table. The approach I use is to read from the
source table and then insert the informaiton into the backup table. However,
since the source table might be really big so and in order not to run out of
memory, I use mysql_use_result() to retrieve information from the source
file and then I tried to insert the row of information into the backup table
right away before I fetch the next row. Obviously, my approach is not right
since I can't execute a insert query before I free my result set. My source
code is as follows:

sprintf(query, "select * from %s", sourceTable);
res = mysql_query(conn, query);
while ((row = mysql_fetch_row(res))
{
sprint(insert_pat, "insert into %s values (", backupTable));
.
.
.
mysql_query(conn, insert_pat); //Commands out of sync
}
Can anyone suggest other ways to make a copy of tables, please?
Thank you,
Michelle





--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Make a copy of tables using C API

Michelle,

Don't have C touch the data. Use

INSERT INTO backupTable SELECT * FROM sourceTable ;

assuming "backupTable" has schema matching "sourceTable"
perfectly. If not, list columns after backupTable as you might
for an "INSERT...VALUES..." statement and corresponding ones in
the SELECT list.

Don't forget to "TRUNCATE TABLE backupTable" if all that's wanted in the
"backupTable" is a copy of the "sourceTable".

-Jan

On Thu, 7 Apr 2005 13:25:52 -0400 , "Wang, Michelle"
rddc.gc.ca> said:
> Hi,
>
> I have been trying to write a program that uses the MySQL C API to
> backup a source table into a backup table. The approach I use is to
> read from the source table and then insert the informaiton into the
> backup table. However, since the source table might be really big so
> and in order not to run out of memory, I use mysql_use_result() to
> retrieve information from the source file and then I tried to insert
> the row of information into the backup table right away before I fetch
> the next row. Obviously, my approach is not right since I can't
> execute a insert query before I free my result set. My source code is
> as follows:
>
> sprintf(query, "select * from %s", sourceTable); res =3D
> mysql_query(conn, query); while ((row =3D mysql_fetch_row(res)) {
> sprint(insert_pat, "insert into %s values (", backupTable)); .
> . . mysql_query(conn, insert_pat); //Commands out of sync }
> Can anyone suggest other ways to make a copy of tables, please?
> Thank you, Michelle

[snip]
--=20

Jan Theodore Galkowski (o°) =

jtgalkowski@alum.mit.edu
http://www.smalltalkidiom.net

The Smalltalk Idiom
laboratory workflow software
PHP,ANSI SQL,relational designs
Internet engineering



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

Re: Make a copy of tables using C API

Hello Michelle,

If I had been you, I would have used a GUI like Sqlyog
to take back.

www.webyog.com

Karam

--- Jan Theodore Galkowski
wrote:
> Michelle,
>
> Don't have C touch the data. Use
>
> INSERT INTO backupTable SELECT * FROM sourceTable
> ;
>
> assuming "backupTable" has schema matching
> "sourceTable"
> perfectly. If not, list columns after backupTable
> as you might
> for an "INSERT...VALUES..." statement and
> corresponding ones in
> the SELECT list.
>
> Don't forget to "TRUNCATE TABLE backupTable" if all
> that's wanted in the
> "backupTable" is a copy of the "sourceTable".
>
> -Jan
>
> On Thu, 7 Apr 2005 13:25:52 -0400 , "Wang, Michelle"
> > rddc.gc.ca> said:
> > Hi,
> >
> > I have been trying to write a program that uses
> the MySQL C API to
> > backup a source table into a backup table. The
> approach I use is to
> > read from the source table and then insert the
> informaiton into the
> > backup table. However, since the source table
> might be really big so
> > and in order not to run out of memory, I use
> mysql_use_result() to
> > retrieve information from the source file and then
> I tried to insert
> > the row of information into the backup table right
> away before I fetch
> > the next row. Obviously, my approach is not right
> since I can't
> > execute a insert query before I free my result
> set. My source code is
> > as follows:
> >
> > sprintf(query, "select * from %s", sourceTable);
> res =
> > mysql_query(conn, query); while ((row =
> mysql_fetch_row(res)) {
> > sprint(insert_pat, "insert into %s values (",
> backupTable)); .
> > . . mysql_query(conn, insert_pat);
> //Commands out of sync }
> > Can anyone suggest other ways to make a
> copy of tables, please?
> > Thank you, Michelle
>
> [snip]
> --
>
> Jan Theodore Galkowski (o°)
> jtgalkowski@alum.mit.edu
> http://www.smalltalkidiom.net
>
> The Smalltalk Idiom
> laboratory workflow software
> PHP,ANSI SQL,relational designs
> Internet engineering
>
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
>
http://lists.mysql.com/win32?unsub=karam_chand03@yahoo.com
>
>



__________________________________
Yahoo! Messenger
Show us what our next emoticon should look like. Join the fun.
http://www.advision.webevents.yahoo.com/emoticontest

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Make a copy of tables using C API

Does Sqlyog has a feature that allow my app to call it and perform
actions? If I could call a dll from it and perform actions inside my
app I'll stop developing my backup program right now! Do I have to pay
to use its full version? Because I don't believe they would develop a
library to do such tricky jobs for free.

Michelle, will your program run at the server or at a client? Because
you could call mysqldump to get the data from the source table into a
file and them add this content to another table using the mysql
console. I do believe that would be a little faster than connecting to
the server and getting all the content, and you don't need much
memory, because the disk will act as storage.

Good luck,

On Apr 7, 2005 5:18 PM, Karam Chand wrote:
> Hello Michelle,
>=20
> If I had been you, I would have used a GUI like Sqlyog
> to take back.
>=20
> www.webyog.com
>=20
> Karam
>=20
> --- Jan Theodore Galkowski
> wrote:
> > Michelle,
> >
> > Don't have C touch the data. Use
> >
> > INSERT INTO backupTable SELECT * FROM sourceTable
> > ;
> >
> > assuming "backupTable" has schema matching
> > "sourceTable"
> > perfectly. If not, list columns after backupTable
> > as you might
> > for an "INSERT...VALUES..." statement and
> > corresponding ones in
> > the SELECT list.
> >
> > Don't forget to "TRUNCATE TABLE backupTable" if all
> > that's wanted in the
> > "backupTable" is a copy of the "sourceTable".
> >
> > -Jan
> >
> > On Thu, 7 Apr 2005 13:25:52 -0400 , "Wang, Michelle"
> > > > rddc.gc.ca> said:
> > > Hi,
> > >
> > > I have been trying to write a program that uses
> > the MySQL C API to
> > > backup a source table into a backup table. The
> > approach I use is to
> > > read from the source table and then insert the
> > informaiton into the
> > > backup table. However, since the source table
> > might be really big so
> > > and in order not to run out of memory, I use
> > mysql_use_result() to
> > > retrieve information from the source file and then
> > I tried to insert
> > > the row of information into the backup table right
> > away before I fetch
> > > the next row. Obviously, my approach is not right
> > since I can't
> > > execute a insert query before I free my result
> > set. My source code is
> > > as follows:
> > >
> > > sprintf(query, "select * from %s", sourceTable);
> > res =3D
> > > mysql_query(conn, query); while ((row =3D
> > mysql_fetch_row(res)) {
> > > sprint(insert_pat, "insert into %s values (",
> > backupTable)); .
> > > . . mysql_query(conn, insert_pat);
> > //Commands out of sync }
> > > Can anyone suggest other ways to make a
> > copy of tables, please?
> > > Thank you, Michelle
> >
> > [snip]
> > --
> >
> > Jan Theodore Galkowski (o°)
> > jtgalkowski@alum.mit.edu
> > http://www.smalltalkidiom.net
> >
> > The Smalltalk Idiom
> > laboratory workflow software
> > PHP,ANSI SQL,relational designs
> > Internet engineering
> >
> >
> >
> > --
> > MySQL Windows Mailing List
> > For list archives: http://lists.mysql.com/win32
> > To unsubscribe:
> >
> http://lists.mysql.com/win32?unsub=3Dkaram_chand03@yahoo.com
> >
> >
>=20
>=20
> __________________________________
> Yahoo! Messenger
> Show us what our next emoticon should look like. Join the fun.
> http://www.advision.webevents.yahoo.com/emoticontest
>=20
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=3Ddanieldaveiga@gma=
il.com
>=20
>=20


--=20
Daniel da Veiga
Computer Operator - RS - Brazil

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org