RE: MYSQL to XML

Tue, 26 Apr 2005 16:04:24 -0700


Mathias
Thanks for your help, I really appreciated it. And I was just wondering if MySQL has another statment (besides show create table) that only displays the foreign key, but I see that only with the "show create table MyTable" could get this.

Greetings

From: [EMAIL PROTECTED]
To: Mikel - <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com
Subject: RE: MYSQL to XML
Date: Mon, 25 Apr 2005 17:41:47 +0200
MIME-Version: 1.0
X-Originating-IP: 213.41.126.253
Received: from lists.mysql.com ([213.136.52.31]) by mc5-f35.hotmail.com with Microsoft SMTPSVC(6.0.3790.211); Mon, 25 Apr 2005 08:44:24 -0700
Received: (qmail 21182 invoked by uid 109); 25 Apr 2005 15:41:56 -0000
Received: (qmail 21160 invoked from network); 25 Apr 2005 15:41:55 -0000
Received: pass (lists.mysql.com: local policy)
X-Message-Info: JGTYoYF78jHafVH/hFUFqKtbfGVCQG07u9r/IzplGqc=
Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
List-ID: <mysql.mysql.com>
Precedence: bulk
List-Help: <mailto:[EMAIL PROTECTED]>
List-Unsubscribe: <mailto:[EMAIL PROTECTED]>
List-Post: <mailto:mysql@lists.mysql.com>
List-Archive: http://lists.mysql.com/mysql/183082
Delivered-To: mailing list mysql@lists.mysql.com
References: <[EMAIL PROTECTED]>
User-Agent: Internet Messaging Program (IMP) 3.2.5
X-Virus-Checked: Checked
Return-Path: [EMAIL PROTECTED]
X-OriginalArrivalTime: 25 Apr 2005 15:44:24.0943 (UTC) FILETIME=[A82A1FF0:01C549AD]

Hi Mikel,
Show create table shows a line CONSTRAINT ... FORIEGN KEY ...

you can add a grep on this line. But this will be difficult. You can construct
another desc2xml using just "show create table" to have it easier.

Mathias

Selon Mikel - <[EMAIL PROTECTED]>:

> Thanx Mathias for your quick and effective response, I see that your program
> almost display the format that I need, the thing is that I need the foreign
> key information too, Does MySQL have a statement besides "show create table"
> to display this information?....Thanks again for your suggestions and help
>
> Greetings
>
> >From: "mathias fatene" <[EMAIL PROTECTED]>
> >To: <mysql@lists.mysql.com>
> >CC: <mysql@lists.mysql.com>
> >Subject: RE: MYSQL to XML
> >Date: Sun, 24 Apr 2005 21:39:14 +0200
> >MIME-Version: 1.0
> >Received: from lists.mysql.com ([213.136.52.31]) by mc3-f23.hotmail.com
> >with Microsoft SMTPSVC(6.0.3790.211); Sun, 24 Apr 2005 12:42:26 -0700
> >Received: (qmail 15912 invoked by uid 109); 24 Apr 2005 19:40:50 -0000
> >Received: (qmail 15893 invoked from network); 24 Apr 2005 19:40:50 -0000
> >Received: pass (lists.mysql.com: local policy)
> >X-Message-Info: JGTYoYF78jEQFMtosA6GPW/w+/WF28t94KBGDmreITY=
> >Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
> >List-ID: <mysql.mysql.com>
> >Precedence: bulk
> >List-Help: <mailto:[EMAIL PROTECTED]>
> >List-Unsubscribe:
> ><mailto:[EMAIL PROTECTED]>
> >List-Post: <mailto:mysql@lists.mysql.com>
> >List-Archive: http://lists.mysql.com/mysql/183030
> >Delivered-To: mailing list mysql@lists.mysql.com
> >X-MSMail-Priority: Normal
> >X-Mailer: Microsoft Outlook, Build 10.0.2616
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> >X-Virus-Checked: Checked
> >Return-Path: [EMAIL PROTECTED]
> >X-OriginalArrivalTime: 24 Apr 2005 19:42:26.0285 (UTC)
> >FILETIME=[BE1839D0:01C54905]
> >
> >Hi Mikel,
> >There are a lot of possibilities including commercial (:o)) products.
> >I suggest you those solutions. The output should be reparsed for your
> >needs :
> >1. the -X on client :
> > C:\Mysql>mysql -u mathias world -X -e "desc country"
> > <?xml version="1.0"?>
> >
> > <resultset statement="desc country">
> > <row>
> > <Field>Code</Field>
> > <Type>char(3)</Type>
> > <Null></Null>
> > <Key>PRI</Key>
> > <Default></Default>
> > <Extra></Extra>
> > </row>
> > <row>
> > <Field>Name</Field>
> > <Type>char(52)</Type>
> > <Null></Null>
> > <Key></Key>
> > <Default></Default>
> > <Extra></Extra>
> > </row>
> >
> > <row>
> > <Field>Continent</Field>
> >...
> >...
> >
> >2. install perl DBI and DBIx-XML_RDB modules :
> >#!perl -w
> ># ---------------------------------------------------------------
> ># Describe2xml
> ># Author : Mathias FATENE
> ># Date : 24 april 2005
> ># ---------------------------------------------------------------
> >use DBIx::XML_RDB;
> >
> > my $userid='root';
> > my $password='**************';
> > my $dbname='world';
> > my $dsn = "DBI:mysql:database=$dbname;host=localhost";
> >
> > my $xmlout = DBIx::XML_RDB->new($dsn,'mysql',$userid, $password) || die
> >"Failed to make new xmlout";
> >
> > $xmlout->DoSql("describe country");
> > print $xmlout->GetData;
> >
> >C:\Mysql>perl describe.pl
> ><?xml version="1.0"?>
> ><DBI driver="DBI:mysql:database=world;host=localhost">
> > <RESULTSET statement="describe country">
> > <ROW>
> > <Field>Code</Field>
> > <Type>char(3)</Type>
> > <Null></Null>
> > <Key>PRI</Key>
> > <Default></Default>
> > <Extra></Extra>
> > </ROW>
> > <ROW>
> > <Field>Name</Field>
> > <Type>char(52)</Type>
> > <Null></Null>
> > <Key></Key>
> > <Default></Default>
> > <Extra></Extra>
> > </ROW>
> > <ROW>
> >...
> >...
> >3. install Perl DBI and DBD-Mysql and use my program (formatted for your
> >needs) :
> >#!perl -w
> ># ---------------------------------------------------------------
> ># Describe2xml
> ># Author : Mathias FATENE
> ># Date : April, 24 2005
> ># ---------------------------------------------------------------
> >use DBI;
> >
> > my $userid='root';
> > my $password='************';
> > my $dbname='world';
> > my $dsn = "DBI:mysql:database=$dbname;host=localhost";
> >
> > my $dbh = DBI->connect($dsn,$userid, $password,{'RaiseError' => 1});
> > # ---------------------------------------------------------------
> > # describe country table and print it in XML format
> > # ---------------------------------------------------------------
> > my $table="country";
> > $sth = $dbh->prepare("describe $table");
> > $sth->execute();
> >
> > print "\<table name=\"$table\"\>\n";
> > while (my @ref = $sth->fetchrow_array()) {
> > print "\<column name=\"$ref[0]\" required=\"true\"
> >type=\"$ref[1]\"";
> > print " primaryKey=\"true\"" if ($ref[3] eq "PRI") ;
> > print "/\>\n";
> > }
> > $sth->finish();
> > print "\</table\>\n";
> >
> > # Disconnect from the database.
> > $dbh->disconnect();
> >
> >C:\Mysql>perl desc.pl country
> ><table name="country">
> ><column name="Code" required="true" type="char(3)" primaryKey="true"/>
> ><column name="Name" required="true" type="char(52)"/>
> ><column name="Continent" required="true"
> >type="enum('Asia','Europe','North
> >America','Africa','Oceania','Antarctica','South America')"/>
> ><column name="Region" required="true" type="char(26)"/>
> ><column name="SurfaceArea" required="true" type="float(10,2)"/>
> ><column name="IndepYear" required="true" type="smallint(6)"/>
> ><column name="Population" required="true" type="int(11)"/>
> ><column name="LifeExpectancy" required="true" type="float(3,1)"/>
> ><column name="GNP" required="true" type="float(10,2)"/>
> ><column name="GNPOld" required="true" type="float(10,2)"/>
> ><column name="LocalName" required="true" type="char(45)"/>
> ><column name="GovernmentForm" required="true" type="char(45)"/>
> ><column name="HeadOfState" required="true" type="char(60)"/>
> ><column name="Capital" required="true" type="int(11)"/>
> ><column name="Code2" required="true" type="char(2)"/>
> ></table>
> >
> >is this beautifull ?
> >
> >I will modify Describe2xml.pl to be more parametrized (user, db, pass,
> >FK, ...) as soon as possible.
> >
> >Mathias
> >
> >
> >
> > >> Hi list, does it possible for MySQL to generate XML in the followin
> >format:
> > >>
> > >> <table name="ServiceType">
> > >> <column name="idTipoServicio" primaryKey="true"
> > >> required="true" type="VARCHAR" size="10"/>
> > >> <column name="nombre" required="true" type="VARCHAR"
> >size="255"/>
> > >> <column name="costo" required="true" type="FLOAT" size="9"/>
> > >> <column name="idGrupo" required="true" type="INTEGER"/>
> > >> <column name="activa" required="true" type="BOOLEANINT"/>
> > >>
> > >> <foreign-key foreignTable="Grupo" onUpdate="none"
> >onDelete="none">
> > >> <reference foreign="idGrupo" local="idGrupo"/>
> > >> </foreign-key>
> > >> </table>
> > >>
> > >> This XML is the structure of the ServiceType table, I'll hope that
> >you >> can
> > >> help me
> > >>
> > >> Thnx in advanced
> > >>
> > >> Greetings
> > >>
> > >> P.S. Any suggestions (tools) will be appreciated
> > >>
> > >>
> > >>
> > >> Thread
> > >>
> > >> * MySQL to XML - Mikel -, April 23 2005 1:07am
> > >>
> >
> >
> >
> >
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> >
>
>
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to