void SaNewConnection::testConnection()
{
char errbuf[SACAPI_ERROR_SIZE];
sacapi_i32 code;
SqlAnyConnection *conn;
// load the SQL Anywhere interface
if ( !SqlAnyConnection::initApi() )
{
QMessageBox::information( this,
tr( "Failed to load interface" ),
tr( SqlAnyConnection::failedInitMsg() ) );
return;
}
// establish read-only connection to the database
conn = SqlAnyConnection::connect( txtName->text()
, txtHost->text(), txtPort->text(), txtServer->text()
, txtDatabase->text(), txtParameters->text(), txtUsername->text()
, txtPassword->text(), chkSimpleEncryption->isChecked()
, chkEstimateMetadata->isChecked(), true
, code, errbuf, sizeof( errbuf ) );
if ( conn )
{
// retrieve the username and password, in case the user adjusted them
QgsDataSourceURI theUri( conn->uri() );
if ( chkStoreUsername->isChecked() )
{
txtUsername->setText( theUri.username() );
}
if ( chkStorePassword->isChecked() )
{
txtPassword->setText( theUri.password() );
}
conn->release();
QMessageBox::information( this,
tr( "Test connection" ),
tr( "Connection to %1 was successful" )
.arg( txtDatabase->text() ) );
}
else
{
QMessageBox::information( this,
tr( "Test connection" ),
tr( "Connection failed. "
"Check settings and try again.\n\n"
"SQL Anywhere error code: %1\n"
"Description: %2" )
.arg( code )
.arg( errbuf ) );
}
SqlAnyConnection::releaseApi();
}
void SaGeomColTypeThread::getLayerTypes()
{
mStopped = false;
// establish read-only connection to the database
char errbuf[SACAPI_ERROR_SIZE];
sacapi_i32 code;
SqlAnyConnection *conn = SqlAnyConnection::connect( mConnInfo, true, code, errbuf, sizeof( errbuf ) );
if ( conn )
{
for ( uint i = 0; i < schemas.size() && !mStopped; i++ )
{
QString geomtype = geomtypes[i];
QString sridstr = sridstrs[i];
QString lineinterp = lineinterps[i];
QString sql;
QString quotedTableName;
QString fromStr;
SqlAnyStatement *stmt;
quotedTableName = QString( "%1.%2" )
.arg( quotedIdentifier( schemas[i] ) )
.arg( quotedIdentifier( tables[i] ) );
if ( mEstimateMetadata )
{
fromStr = QString( "(SELECT TOP %1 %2 FROM %3 WHERE %2 IS NOT NULL ) AS sampleGeoms " )
.arg( sGeomTypeSelectLimit )
.arg( quotedIdentifier( columns[i] ) )
.arg( quotedTableName );
}
else
{
fromStr = quotedTableName;
}
// retrieve distinct geometry types
if ( geomtype == "WAITING" )
{
QStringList types;
sql = QString(
"SELECT DISTINCT "
"CASE "
"WHEN UCASE(%1.ST_GeometryType()) IN ('ST_POINT','ST_MULTIPOINT') THEN 'ST_POINT' "
"WHEN UCASE(%1.ST_GeometryType()) IN ('ST_LINESTRING','ST_MULTILINESTRING') THEN 'ST_LINESTRING' "
"WHEN UCASE(%1.ST_GeometryType()) IN ('ST_POLYGON','ST_MULTIPOLYGON') THEN 'ST_POLYGON' "
"ELSE 'ST_GEOMETRY' "
"END "
"FROM %2 " )
.arg( quotedIdentifier( columns[i] ) )
.arg( fromStr );
stmt = conn->execute_direct( sql );
if ( stmt->isValid() )
{
while ( stmt->fetchNext() )
{
QString type;
stmt->getString( 0, type );
types += type;
}
}
delete stmt;
if ( types.isEmpty() )
{
geomtype = "ST_GEOMETRY";
}
else
{
geomtype = types.join( "," );
}
}
// retrieve distinct srids
if ( sridstr == "WAITING" )
{
QStringList srids;
QStringList interps;
sql = QString(
"SELECT srid, "
"IF round_earth = 'Y' THEN 'ROUND EARTH' ELSE 'PLANAR' ENDIF "
"FROM ( "
"SELECT DISTINCT %1.ST_SRID() AS srid FROM %2 "
") AS sridlist, SYS.ST_SPATIAL_REFERENCE_SYSTEMS "
"WHERE srid = srs_id " )
.arg( quotedIdentifier( columns[i] ) )
.arg( fromStr );
stmt = conn->execute_direct( sql );
if ( stmt->isValid() )
{
while ( stmt->fetchNext() )
{
int srid;
QString interp;
stmt->getInt( 0, srid );
stmt->getString( 1, interp );
srids += QString::number( srid );
if ( !interps.contains( interp ) )
{
interps += interp;
}
}
}
delete stmt;
if ( srids.isEmpty() )
{
sridstr = "UNKNOWN";
lineinterp = "UNKNOWN";
}
else
{
sridstr = srids.join( "," );
lineinterp = interps.join( "," );
}
}
// Now tell the layer list dialog box...
emit setLayerType( schemas[i], tables[i], columns[i], geomtype, sridstr, lineinterp );
}
conn->release();
}
}
void SaSourceSelect::on_btnConnect_clicked()
{
if ( mColumnTypeThread )
{
mColumnTypeThread->stop();
mColumnTypeThread = 0;
}
QModelIndex rootItemIndex = mTableModel.indexFromItem( mTableModel.invisibleRootItem() );
mTableModel.removeRows( 0, mTableModel.rowCount( rootItemIndex ), rootItemIndex );
// populate the table list
QSettings settings;
// load the SQL Anywhere interface
if ( !SqlAnyConnection::initApi() )
{
QMessageBox::information( this,
tr( "Failed to load interface" ),
tr( SqlAnyConnection::failedInitMsg() ) );
return;
}
// compute connection information
QString key = "/SQLAnywhere/connections/" + cmbConnections->currentText();
mEstimateMetadata = settings.value( key + "/estimateMetadata", false ).toBool();
mOtherSchemas = settings.value( key + "/otherSchemas", false ).toBool();
mConnInfo = SqlAnyConnection::makeUri( key
, settings.value( key + "/host" ).toString()
, settings.value( key + "/port" ).toString()
, settings.value( key + "/server" ).toString()
, settings.value( key + "/database" ).toString()
, settings.value( key + "/parameters" ).toString()
, settings.value( key + "/username" ).toString()
, settings.value( key + "/password" ).toString()
, settings.value( key + "/simpleEncryption", false ).toBool()
, mEstimateMetadata );
SaDebugMsg( "Connection info: " + mConnInfo );
// establish read-only connection to the database
char errbuf[SACAPI_ERROR_SIZE];
sacapi_i32 code;
SqlAnyConnection *conn = SqlAnyConnection::connect( mConnInfo, true, code, errbuf, sizeof( errbuf ) );
if ( conn )
{
// get the list of suitable tables and columns and populate the UI
geomCol details;
if ( getTableInfo( conn->addRef(), mOtherSchemas ) )
{
// Start the thread that gets the geometry type for relations that
// may take a long time to return
if ( mColumnTypeThread != NULL )
{
connect( mColumnTypeThread, SIGNAL( setLayerType( QString, QString, QString, QString, QString, QString ) ),
this, SLOT( setLayerType( QString, QString, QString, QString, QString, QString ) ) );
// Do it in a thread.
mColumnTypeThread->start();
}
}
else
{
SaDebugMsg( "Unable to get list of spatially enabled tables "
"from the database" );
}
if ( cmbConnections->count() > 0 ) mAddButton->setEnabled( true );
conn->release();
}
else
{
QMessageBox::warning( this, tr( "Connection failed" ),
tr( "Connection to database %1 failed. "
"Check settings and try again.\n\n"
"SQL Anywhere error code: %2\n"
"Description: %3" )
.arg( settings.value( key + "/database" ).toString() )
.arg( code )
.arg( errbuf ) );
}
mTablesTreeView->sortByColumn( SaDbTableModel::dbtmTable, Qt::AscendingOrder );
mTablesTreeView->sortByColumn( SaDbTableModel::dbtmSchema, Qt::AscendingOrder );
//if we have only one schema item, expand it by default
int numTopLevelItems = mTableModel.invisibleRootItem()->rowCount();
if ( numTopLevelItems < 2 || mTableModel.tableCount() < 20 )
{
//expand all the toplevel items
for ( int i = 0; i < numTopLevelItems; ++i )
{
mTablesTreeView->expand( mProxyModel.mapFromSource( mTableModel.indexFromItem( mTableModel.invisibleRootItem()->child( i ) ) ) );
}
}
}
