Determining type of MySQL field.

[glakk@potatoradio.f2s.com]

I have a problem of migrating a database from one host to another.
I can't do a dump on the source server. The only access I have is thru
queries. So it looks like I'm going to have to query all the
tables and insert to the destination.

I'm trying to come up with a general solution to this problem which
uses queries to determine the structure of the source db, and create
the tables on the destination, which I've already done using the nice
"show create table" query syntax combined with the PHP
mysql_list_tabl es() function.

The problem comes with doing the inserts. It looks like I'm going to
have to do a "select * from table" thing and then loop thru the
recordset doing insert after insert.

Q: Do I have to do it that way? Is there a way I can insert all the
records from source to destination more efficiently?

Q: How can I determine the type of value in a field returned from
a query? PHP's is_string returns true on ALL fields regardless of type
used in the database itself. I need to know whether to wrap the values
in quotes or not before I insert them.

I'd like to be able to at least fetch a record and insert the record
without having to process it. I'd like to grab the whole table and
insert it, but haven't a clue how to do this.

Q: Is there some kind of select/fetch that I can do that will give me a
record ready to insert i.e. with commas and quotes?

TIA,

[News Me]

Re: Determining type of MySQL field.

glakk@potatorad io.f2s.com wrote:[color=blue]
> I have a problem of migrating a database from one host to another.
> I can't do a dump on the source server. The only access I have is thru
> queries. So it looks like I'm going to have to query all the
> tables and insert to the destination.
>
> I'm trying to come up with a general solution to this problem which
> uses queries to determine the structure of the source db, and create
> the tables on the destination, which I've already done using the nice
> "show create table" query syntax combined with the PHP
> mysql_list_tabl es() function.
>
> The problem comes with doing the inserts. It looks like I'm going to
> have to do a "select * from table" thing and then loop thru the
> recordset doing insert after insert.
>
> Q: Do I have to do it that way? Is there a way I can insert all the
> records from source to destination more efficiently?
>
> Q: How can I determine the type of value in a field returned from
> a query? PHP's is_string returns true on ALL fields regardless of type
> used in the database itself. I need to know whether to wrap the values
> in quotes or not before I insert them.
>
> I'd like to be able to at least fetch a record and insert the record
> without having to process it. I'd like to grab the whole table and
> insert it, but haven't a clue how to do this.
>
> Q: Is there some kind of select/fetch that I can do that will give me a
> record ready to insert i.e. with commas and quotes?
>
> TIA,
>[/color]

The following queries will return the info you need:

SHOW TABLES // returns list of tables
DESCRIBE <table name> // returns column name, type, options, etc...

NM

--
convert UPPERCASE NUMBER to a numeral to reply

[NurAzije]

Re: Determining type of MySQL field.

May be if you try to use some PL/SQL script it will be easier, but
don't know how!!!
You can ask some Database experts for idea's and then get back to ask
about ways to convert these idea's to PHP script..

[glakk@potatoradio.f2s.com]

Re: Determining type of MySQL field.


News Me wrote:[color=blue]
>
> The following queries will return the info you need:
>
> SHOW TABLES // returns list of tables[/color]

PHP's mysql_list_tabl es is more useful, because the result doesn't
need to be processed.
[color=blue]
> DESCRIBE <table name> // returns column name, type, options, etc...[/color]

MySQL's 'SHOW CREATE TABLE' returns a string that can be used to
duplicate the table on the other host.

Also, I made the mistake of assuming that MySQL was strongly typed,
i.e. that strings had to be quoted, but scalers could not be. It
turned out that if you quote everything, it works fine (but you
have to addslashes() to the string you create.

The problem now is that I have auto_increment fields. ... oooooh.

[Tony23]

Re: Determining type of MySQL field.

<glakk@potatora dio.f2s.com> wrote in message
news:1109368156 .305033.309950@ l41g2000cwc.goo glegroups.com.. .[color=blue]
>
> News Me wrote:[color=green]
> >
> > The following queries will return the info you need:
> >
> > SHOW TABLES // returns list of tables[/color]
>
> PHP's mysql_list_tabl es is more useful, because the result doesn't
> need to be processed.
>[color=green]
> > DESCRIBE <table name> // returns column name, type, options, etc...[/color]
>
> MySQL's 'SHOW CREATE TABLE' returns a string that can be used to
> duplicate the table on the other host.
>
> Also, I made the mistake of assuming that MySQL was strongly typed,
> i.e. that strings had to be quoted, but scalers could not be. It
> turned out that if you quote everything, it works fine (but you
> have to addslashes() to the string you create.
>
> The problem now is that I have auto_increment fields. ... oooooh.[/color]


I have recently (like on Friday) completed a script to "backup" a mySQL
database from one server to another. It does this by first obtaining the
structure of the source DB and recreating that structure in the destination
(the DB must not exist on the destination host - it creates the DB then
inserts the tables). Then it cycles through the source DB, one table at a
time and one field at a time - pulls a field form the source, then writes
it to the destination. In short, it's an automated means to totally
duplicate a DB. So far, It's worked on tables with 10,000+ records and sizes
of 100M+.

If it seems like something that could help, email me at "tony" at
"naturesflavors " dot "com" and I'll send you the code.

[Tony23]

Re: Determining type of MySQL field.

"Gordon Burditt" <gordonb.ooorf@ burditt.org> wrote in message
news:421f97cf$0 $88038$16895aa@ news.airnews.ne t...[color=blue]
>[color=green]
> >I'm trying to come up with a general solution to this problem which
> >uses queries to determine the structure of the source db, and create
> >the tables on the destination, which I've already done using the nice
> >"show create table" query syntax combined with the PHP
> >mysql_list_tab les() function.
> >
> >The problem comes with doing the inserts. It looks like I'm going to
> >have to do a "select * from table" thing and then loop thru the
> >recordset doing insert after insert.[/color]
>
> MySQL allows an extended insert statement which inserts a whole bunch
> of records at a time. The queries generated by mysqldump -opt can
> get ridiculously long (breaks my editor) like 50k per line.[/color]

I have been unable to import most of my databases using mysqldump because
they're too big. I'm getting dump files of 100MB or so. phpmyadmin doesn't
process them (it doesn't even try), and when I try to insert via a command
line, it parses a portion of them then craps out on an error.

[color=blue]
> The mysql_field_* and mysql_list_fiel ds functions might help.
>[color=green]
> >I'd like to be able to at least fetch a record and insert the record
> >without having to process it. I'd like to grab the whole table and
> >insert it, but haven't a clue how to do this.
> >
> >Q: Is there some kind of select/fetch that I can do that will give me a
> >record ready to insert i.e. with commas and quotes?[/color]
>
> I have used queries that look like:
>
> select concat('INSERT INTO foobar VALUES (\'', variable1, '\', \'', ....[/color]
,[color=blue]
> ) from foobar2 ;
>
> but mostly where I already know the structure of the table. This is[/color]
probably[color=blue]
> what mysqldump does, though.[/color]

When I looked at my mysqldump files, it had a series of statements to create
the tables, then an INSERT statement for each record:

INSERT INTO foobar VALUES ('var1', 'var2', ... );