Application using 2 databases and SqlMap

[jjmf]

Hi folks,

I´m studing the sqlmap demo to start using ActiveRecords and SqlMap.
The problem is that my application will use 2 databases:
- A main database
- A year-based named database (DB_2006, DB_2007, DB_2008, ...)

Be default, the year will be the current year but the user may change it using a TDropDownList.

I´m thinking about always connect to the main database (Configured in the application.xml) and to query tables in the other databases by using `dbname.tablename` in the SQL statements.

Code:

<module id="db" class="System.Data.TDataSourceConfig" >
                <database ConnectionString="mysql:host=***;dbname=main_database" Username="***" Password="***" />
        </module>
        <module id="activerecord" ConnectionID="db" class="System.Data.ActiveRecord.TActiveRecordConfig"  EnableCache="false"  />
        <module id="sqlmap" ConnectionID="db" class="System.Data.SqlMap.TSqlMapConfig" />

But what about the sqlmap xml files? How can I create a map if the database may change. For example:

Code:

<?xml version="1.0" encoding="utf-8" ?>
<sqlMap>
    <select id="SelectUser" resultClass="DUserRecord">
        <generate table="Categories">
        SELECT u.*, p.nome, p.email
FROM #tblAno#.usuario u
        INNER JOIN #tblAno#.pessoa p ON u.id_pess = p.id_pess
WHERE u.login = #username# AND u.ativo = 1
LIMIT 1"
    </select>
</sqlMap>

I must replace the #tblAno# with the database name (DB_2006, DB_2007, ...)
I would like some suggestions and approaches to deal with this.
Regards

[jjmf]

up! anyone?

[wei]

use one connection and one set of sqlmaps for each database. Or, define a property in the main sqlmap.xml file

e.g

Code:

<properties>
<property name="dbName" value="db1" />
       </properties>

then in the statements use "${dbName}" (without quotes) to replace with the value "db1".

[natsimhan]

Hi wei, I search all the forum and I don't find any solution...

I'm testing what you proposed before and what I found in the demo about sqlmap with the last svn...

My SqlMap.xml is below :

Code:

<?xml version="1.0" encoding="utf-8"?>
<sqlMapConfig>

  <properties>
    <property name="path_maps" value="maps" />
    <property name="username"  value="root"      />
    <property name="password"  value="1234"    />
    <property name="host"      value="localhost" />
    <property name="database"  value="test" />
  </properties>

  <provider class="TAdodbProvider" >
    <datasource ConnectionString="mysql://${username}:${password}@${host}/${database}" />
  </provider>

  <sqlMaps>
    <sqlMap name="STU" resource="${path_maps}/STU.xml" />
  </sqlMaps>

</sqlMapConfig>

But when I test my application Prado says :
'Unable to find SQLMap configuration file '${path_maps}/STU.xml'.

Why can't I use ${} pattern as a variable I don't understand what is wrong and I don't find anything....

I try to replace name= by key= same as is write in the doc, but it isn't better

Please , help me

PS : After, I want to put all variable in a file like Property.xml but this fails now....

[tof06]

Hi Natsimhan,

I've checked the framework code for SqlMap Configuration, and it seems that the properties are not replaced in config files, but only in mapping statement

In the file System.Data.SqlMap.Configuration.TSqlMapXmlConfiguration, near line 232 (latest svn) :

Code:

<?php
// [...]

$filename = $this->getAbsoluteFilePath($this->_configFile, $resource);
// replace with 
$filename = $this->getAbsoluteFilePath($this->_configFile, $this->replaceProperties($resource));
//[...]
?>

May a Prado developper helps you here ? I don't know if the properties can be used to do what you want (BTW, it's in the sqlmap tutorial, but I don't know if this document is up to date)

Tof.

[wei]

It is a bug (or missing feature), please post a ticket for this thanks!

Wei.

[tof06]

Ticket 616 submited.

Thanks.

[natsimhan]

Thank you, I would thus make without a moment but would be very happy when it is in place 

[tof06]

Sorry to wake up this quite old thread, but I didn't test since the Ticket 616 has been fixed.

And, the fix don't work

Imagine a such sqlmap configuration file :

Code:

<?xml version="1.0" encoding="UTF-8" ?>
<sqlMapConfig>
<properties>
<property name="maps" value="SQL"/>
</properties>
<sqlMaps>
<sqlMap name="Menu" resource="${maps]/menu.xml"/>
</sqlMaps>
</sqlMapConfig>

With this directory structure :

Code:

   protected/
         ...
       Data/
          SQL/
             menu.xml
          sqlmap.xml
         ...

If i run an app with SqlMap configured as this, I got an exception : 'Unable to find SQLMap configuration file '${maps}/menu.xml'.
If I remove the ${maps}, replacing it by SQL/, it works.

Now, if I look at TSqlMapConfiguration :

Code:

<?php
/*...*/
/**
 * Load document using simple xml.
 * @param string filename.
 * @return SimpleXmlElement xml document.
 */
protected function loadXmlDocument($filename,TSqlMapXmlConfiguration $config)
{
if(!is_file($filename))
throw new TSqlMapConfigurationException(
'sqlmap_unable_to_find_config', $filename);
return simplexml_load_string($config->replaceProperties(file_get_contents($filename)));
}
/*...*/
public function configure($filename=null)
{
$this->_configFile=$filename;
$document = $this->loadXmlDocument($filename,$this);

foreach($document->xpath('//property') as $property)
$this->loadGlobalProperty($property);

foreach($document->xpath('//typeHandler') as $handler)
$this->loadTypeHandler($handler);

foreach($document->xpath('//connection[last()]') as $conn)
$this->loadDatabaseConnection($conn);

//try to load configuration in the current config file.
$mapping = new TSqlMapXmlMappingConfiguration($this);
$mapping->configure($filename);

foreach($document->xpath('//sqlMap') as $sqlmap)
$this->loadSqlMappingFiles($sqlmap);

$this->resolveResultMapping();
$this->attachCacheModels();
}

?>

loadXmlDocument call replaceProperties on the xml file to replace properties name to their value...
But, when it load the global configuration file (sqlmap.xml), properties are empty, because the file has not been parsed yet!

Am I right ? Or totally wrong ??

Thanks

Tof.