1
/**************************************************
4
**************************************************
5
* This code was created by Peter Harvey @ CodeByDesign.
6
* Released under GPL 18.FEB.99
8
* Contributions from...
9
* -----------------------------------------------
10
* Peter Harvey - pharvey@codebydesign.com
11
**************************************************/
13
#include "classISQL.h"
14
#include "classODBC.h"
15
#include <qwhatsthis.h>
20
"Use this area to type SQL statements for your query.\n" \
21
"For example: SELECT * FROM TABLE1\n"
22
"You can cut/paste and save/load text in this area\n" ;
24
char *szHelpSQLSlider = \
25
"Use this slider to quickly access recent SQL statements.\n" ;
27
char *szHelpResults = \
28
"This area contains the output from your recent SQL query.\n" \
29
"You can cut/paste and save/load data in this area.\n" \
30
"NOTE: The max column size is 1024." ;
32
char *szHelpStatus = \
33
"This area contains status messages.\n" ;
35
classISQL::classISQL( SQLHDBC hDbc, QString qsDataSource, QWidget *parent, const char *name )
36
: QWidget( parent, name ), hDbc( hDbc ), qsDataSource( qsDataSource.stripWhiteSpace() ), nSQL( 1 )
38
QVBoxLayout *layoutMain = new QVBoxLayout( this );
41
QFont qf( "Fixed", 12 );
42
qf.setFixedPitch( TRUE );
45
pTabBar = new QTabBar( this );
46
pTabBar->setGeometry( 0, 0, 150, 20 );
47
pTabBar->addTab( new QTab( "SQL" ) ) ;
48
pTabBar->addTab( new QTab( "Results" ) ) ;
49
connect( pTabBar, SIGNAL(selected(int)), SLOT(ChangeTextType(int)) );
52
txtSQL = new QMultiLineEdit( this, "txtSQL" );
53
txtSQL->setFocusPolicy( QWidget::StrongFocus );
54
txtSQL->setBackgroundMode( QWidget::PaletteBase );
55
txtSQL->insertLine( "" );
56
txtSQL->setReadOnly( FALSE );
57
txtSQL->setOverwriteMode( FALSE );
58
txtSQL->setFont( qf );
60
// SQL DATA RESULTS GUI
61
txtResults = new QMultiLineEdit( this, "txtResults" );
62
txtResults->setFocusPolicy( QWidget::StrongFocus );
63
txtResults->setBackgroundMode( QWidget::PaletteBase );
64
txtResults->insertLine( "" );
65
txtResults->setReadOnly( FALSE );
66
txtResults->setOverwriteMode( FALSE );
67
txtResults->setFont( qf );
70
// QUICK ACCESS SLIDER
71
pSliderRecentSQL = new QSlider( QSlider::Horizontal, this );
72
pSliderRecentSQL->setTickmarks( QSlider::Left );
73
pSliderRecentSQL->setTickInterval( 1 );
74
pSliderRecentSQL->setLineStep( 1 );
75
pSliderRecentSQL->setPageStep( 1 );
76
pSliderRecentSQL->setMinValue( nSQL );
77
pSliderRecentSQL->setMaxValue( nSQL );
78
pSliderRecentSQL->setValue( nSQL );
79
connect( pSliderRecentSQL, SIGNAL(valueChanged(int)), SLOT(gotoHistoryItem(int)) );
83
QLabel *labelStatus = new QLabel( "STATUS", this );
84
labelStatus->setAlignment( AlignCenter );
87
listStatus = new QListBox( this );
88
listStatus->setSelectionMode( QListBox::NoSelection );
91
QWhatsThis::add( txtSQL , szHelpSQL );
92
QWhatsThis::add( pSliderRecentSQL , szHelpSQLSlider );
93
QWhatsThis::add( txtResults , szHelpResults );
94
QWhatsThis::add( labelStatus , szHelpStatus );
95
QWhatsThis::add( listStatus , szHelpStatus );
98
layoutMain->addWidget( pTabBar );
99
layoutMain->addWidget( txtSQL, 6 );
100
layoutMain->addWidget( txtResults, 6 );
101
layoutMain->addWidget( pSliderRecentSQL );
102
layoutMain->addWidget( labelStatus );
103
layoutMain->addWidget( listStatus, 2 );
106
setMinimumSize( 50, 50 );
107
setMaximumSize( 32767, 32767 );
108
resize( parent->size() );
111
void classISQL::addStatus( const QString &statusMsg )
113
if ( listStatus->count() == 300 )
114
listStatus->removeItem( 0 ) ;
116
listStatus->insertItem( statusMsg ) ;
117
listStatus->setBottomItem( listStatus->count()-1 ) ;
120
void classISQL::ExecSQL(View view)
122
CursorScoper s(this) ;
124
SQLLEN nRowsAffected;
130
addStatus( QString().sprintf( "RUN: view=%s sql=%s", view == Text ? "Text" : view == TextDelimited ? "TextDelimited" : "HTML", txtSQL->text().simplifyWhiteSpace().data() ) ) ;
132
// CREATE A STATEMENT
133
StatementScoper stmt( hDbc ) ; if ( !stmt() ) return ;
136
if (!SQL_SUCCEEDED(nReturn=SQLPrepare(stmt(), (SQLCHAR*)txtSQL->text().simplifyWhiteSpace().data(), SQL_NTS) ) )
137
return my_msgBox( "classISQL", "SQLPrepare", nReturn, NULL, NULL, stmt(), txtSQL->text() ) ;
140
if (!SQL_SUCCEEDED(nReturn=SQLExecute( stmt() ) ) )
141
return my_msgBox( "classISQL", "SQLExecute", nReturn, NULL, NULL, stmt(), txtSQL->text() ) ;
146
// GET NUMBER OF COLUMNS RETURNED
147
if (!SQL_SUCCEEDED(SQLNumResultCols( stmt(), &nColumns ) ) )
150
txtResults->setAutoUpdate( FALSE );
157
QArray<int> colWidths(nColumns) ;
158
// GET A RESULTS HEADER (column headers)
159
getResultsHeader( stmt(), nColumns, qsHorizSep, colWidths );
160
// GET A RESULTS BODY (data)
161
nRowsAffected = getResultsBody( stmt(), nColumns, qsHorizSep, colWidths );
169
const char **colChar = new const char * [nColumns ];
172
for ( nCol = 1; nCol <= nColumns; nCol++ )
174
SQLCHAR szColumnName[MAX_COLUMN_WIDTH];
175
if (SQL_SUCCEEDED(SQLColAttribute( stmt(), nCol, SQL_DESC_LABEL, szColumnName, sizeof(szColumnName), 0, 0 ) ) )
176
qsLine += QString("\"") + QString((const char *)szColumnName).stripWhiteSpace() + "\"," ;
178
qsLine += "\"ERR\"," ;
180
// Determine if this column needs to have quotes around it
181
SQLINTEGER nConciseType = 0 ;
183
if (SQL_SUCCEEDED(SQLColAttribute( stmt(), nCol, SQL_DESC_CONCISE_TYPE, 0, 0, 0, &nConciseType ) ) )
185
switch (nConciseType)
188
case SQL_VARCHAR: // =12
189
case SQL_LONGVARCHAR: // =-1
190
case SQL_WCHAR: // =-8
191
case SQL_WVARCHAR: // =-9
192
case SQL_WLONGVARCHAR: // =-10
193
colChar[nCol] = "\"" ;
201
txtResults->insertLine( qsLine );
205
while ( SQL_SUCCEEDED(SQLFetch(stmt() ) ) )
209
// Process all columns
210
for ( nCol = 1; nCol <= nColumns; nCol++ )
212
char szData[MAX_COLUMN_WIDTH];
213
memset(szData, 0, sizeof(szData) ) ; // Handle broken drivers that don't properly null terminate
214
SQLLEN nIndicator = 0;
215
if (SQL_SUCCEEDED(SQLGetData( stmt(), nCol, SQL_C_CHAR, (SQLPOINTER)szData, sizeof(szData), &nIndicator ) ) )
216
qsLine += QString().sprintf("%s%s%s,", colChar[nCol], nIndicator != SQL_NULL_DATA ? QString(szData).stripWhiteSpace().data() : "", colChar[nCol] ) ;
218
qsLine += QString("\"ERR\",") ;
220
txtResults->insertLine( qsLine );
222
nRowsAffected = nRow ;
231
txtResults->insertLine( "<html>" ) ;
232
txtResults->insertLine( "<body>" ) ;
233
txtResults->insertLine( "<table border>" ) ;
234
txtResults->insertLine( QString().sprintf("<caption>%s</caption>", txtSQL->text().data() ) ) ;
236
txtResults->insertLine( " <tr>" );
237
SQLCHAR szColumnName[MAX_COLUMN_WIDTH] ;
238
for ( nCol = 1; nCol <= nColumns; nCol++ )
240
if (SQL_SUCCEEDED(SQLColAttribute( stmt(), nCol, SQL_DESC_LABEL, szColumnName, sizeof(szColumnName), 0, 0 ) ) )
241
txtResults->insertLine( QString().sprintf(" <th>%s</th>", QString((const char *)szColumnName).stripWhiteSpace().data() ) ) ;
243
txtResults->insertLine( " <th>ERR</th>" ) ;
245
txtResults->insertLine( " </tr>" );
249
while ( SQL_SUCCEEDED(SQLFetch(stmt() ) ) )
252
txtResults->insertLine( " <tr>" );
253
// Process all columns
254
for ( nCol = 1; nCol <= nColumns; nCol++ )
256
char szData[MAX_COLUMN_WIDTH] ;
257
SQLLEN nIndicator = 0;
258
memset(szData, 0, sizeof(szData) ) ; // Handle broken drivers that don't properly null terminate
259
if (SQL_SUCCEEDED(SQLGetData( stmt(), nCol, SQL_C_CHAR, (SQLPOINTER)szData, sizeof(szData), &nIndicator ) ) )
260
txtResults->insertLine( QString().sprintf(" <td>%s</td>", nIndicator != SQL_NULL_DATA ? QString((const char *)szData).stripWhiteSpace().data() : "" ) ) ;
262
txtResults->insertLine( " <td>ERR</td>" ) ;
264
txtResults->insertLine( " </tr>" );
267
txtResults->insertLine( "</table>" ) ;
268
txtResults->insertLine( "</body>" ) ;
269
txtResults->insertLine( "</html>" ) ;
270
nRowsAffected = nRow ;
276
pTabBar->setCurrentTab( 1 );
279
txtResults->setAutoUpdate( TRUE );
280
txtResults->repaint();
281
addStatus( QString().sprintf( "RUN: %d rows and %d columns affected", nRowsAffected, nColumns ) ) ;
284
void classISQL::getResultsHeader( SQLHSTMT hStmt, SWORD nColumns, QString &qsHorizSep, QArray<int> &colWidths )
286
QString qsColumnHeader;
287
QString qsFill ; qsFill.fill('-', MAX_COLUMN_WIDTH);
288
for ( int nCol = 1; nCol <= nColumns; nCol++ )
290
unsigned int nMaxLength;
291
SQLCHAR szColumnName[MAX_COLUMN_WIDTH]; szColumnName[0] = 0;
292
// Grab the column name and display size
293
SQLColAttribute( hStmt, nCol, SQL_DESC_DISPLAY_SIZE, 0, 0, 0, &nMaxLength );
294
SQLColAttribute( hStmt, nCol, SQL_DESC_LABEL, szColumnName, sizeof(szColumnName), 0, 0 ) ;
295
QString qsColumnName( QString((const char *)szColumnName).stripWhiteSpace() ) ;
296
// Calc the column width
297
int nWidth = max( nMaxLength, qsColumnName.length() ) ;
298
nWidth = min( nWidth, MAX_COLUMN_WIDTH );
299
// Buld the formatted column
300
qsHorizSep += QString().sprintf( "+%-*.*s-" , nWidth, nWidth, qsFill.data() ) ;
301
qsColumnHeader += QString().sprintf( "| %-*.*s" , nWidth, nWidth, qsColumnName.data() ) ;
302
colWidths[nCol-1] = nWidth ;
305
qsColumnHeader += "|";
307
txtResults->insertLine( qsHorizSep );
308
txtResults->insertLine( qsColumnHeader );
309
txtResults->insertLine( qsHorizSep );
312
int classISQL::getResultsBody( SQLHSTMT hStmt, SWORD nColumns, const QString &qsHorizSep, const QArray<int> &colWidths )
317
while ( SQL_SUCCEEDED(SQLFetch(hStmt) ) )
321
// PROCESS ALL COLUMNS
322
for ( int nCol = 1; nCol <= nColumns; nCol++ )
325
char szData[MAX_COLUMN_WIDTH]; szData[0] = 0;
326
SQLRETURN nReturn = SQLGetData( hStmt, nCol, SQL_C_CHAR, (SQLPOINTER)szData, sizeof(szData), &nIndicator );
327
// Grab the column data
328
if ( SQL_SUCCEEDED(nReturn) && nIndicator != SQL_NULL_DATA )
329
qsLine += QString().sprintf( "| %-*.*s", colWidths[nCol-1], colWidths[nCol-1], szData );
331
qsLine += QString().sprintf( "| %-*.*s", colWidths[nCol-1], colWidths[nCol-1], " " );
333
txtResults->insertLine( qsLine + "|" );
335
txtResults->insertLine( qsHorizSep );
339
void classISQL::ChangeTextType( int nTab )
343
pSliderRecentSQL->show();
349
pSliderRecentSQL->hide();
355
void classISQL::gotoHistoryItem( int nValue )
357
QValueList<QString>::Iterator it;
360
it = listSQL.at( nSQL );
361
(*it) = txtSQL->text();
365
it = listSQL.at( nSQL );
366
txtSQL->setText( (*it) );
368
// Lets keep it simple for now, previous sql can be executed again but not edited, force
369
// the user to copy and paste to last sql for editing. Remember; its important to maintain
370
// a history of executed statements, unchanged, so that the user can audit self.
371
QPalette oPalette = txtResults->palette();
373
if ( nSQL == pSliderRecentSQL->maxValue() )
375
txtSQL->setReadOnly( false );
376
txtSQL->setPalette( oPalette );
380
txtSQL->setReadOnly( true );
381
oPalette.setColor( QColorGroup::Text, txtResults->backgroundColor() );
382
oPalette.setColor( QColorGroup::Base, txtResults->foregroundColor() );
383
txtSQL->setPalette( oPalette );
387
void classISQL::appendHistoryItem()
389
QValueList<QString>::Iterator it;
392
it = listSQL.at( nSQL );
393
(*it) = txtSQL->text();
395
// ADD AS LAST & MOVE TO LAST
396
listSQL.append( txtSQL->text() );
397
pSliderRecentSQL->setMaxValue( pSliderRecentSQL->maxValue() + 1 );
398
pSliderRecentSQL->setValue( pSliderRecentSQL->maxValue() );
401
void classISQL::NewSQL()
403
pSliderRecentSQL->setValue( pSliderRecentSQL->maxValue() );
406
pTabBar->setCurrentTab( 0 ) ;
407
addStatus( "NEW: sql text cleared" ) ;
410
void classISQL::OpenSQL()
414
if ( pTabBar->currentTab() == 0 )
416
pSliderRecentSQL->setValue( pSliderRecentSQL->maxValue() );
422
// LET USER PICK A FILE
423
QString qsFile = QFileDialog::getOpenFileName();
424
if ( qsFile.isNull() )
427
// TRY TO LOAD THE FILE
428
QFile hFile( qsFile );
429
if ( !hFile.open( IO_ReadOnly ) )
430
return my_msgBox( "classISQL", "QFile.open", hFile.status(), NULL, NULL, NULL, qsFile ) ;
432
txt->setAutoUpdate( FALSE );
435
QTextStream t( &hFile );
437
txt->append( t.readLine() ) ;
440
txt->setAutoUpdate( TRUE );
443
if ( pTabBar->currentTab() == 0 )
444
qsSQLFileName = qsFile;
446
qsResultsFileName = qsFile;
448
addStatus( QString().sprintf( "OPEN: file %s opened", qsFile.data() ) ) ;
451
void classISQL::SaveSQL()
453
QMultiLineEdit *txt = pTabBar->currentTab() ? txtResults : txtSQL ;
454
const QString &qsFileName = pTabBar->currentTab() ? qsResultsFileName : qsSQLFileName ;
456
if ( qsFileName.isEmpty() )
459
// TRY TO SAVE THE FILE
460
QFile hFile( qsFileName );
461
if ( !hFile.open( IO_WriteOnly ) )
462
return my_msgBox( "classISQL", "QFile.open", hFile.status(), NULL, NULL, NULL, qsFileName ) ;
464
hFile.writeBlock( txt->text(), txt->text().length() );
466
addStatus( QString().sprintf( "SAVE: file %s saved", qsFileName.data() ) ) ;
469
void classISQL::SaveAsSQL()
471
QMultiLineEdit *txt = pTabBar->currentTab() ? txtResults : txtSQL ;
472
const QString &qsFileName = pTabBar->currentTab() ? qsResultsFileName : qsSQLFileName ;
474
// LET USER PICK A FILE
475
QString qsFile = QFileDialog::getSaveFileName( qsFileName );
476
if ( qsFile.isNull() )
479
// TRY TO SAVE THE FILE
480
QFile hFile( qsFile );
481
if ( !hFile.open( IO_WriteOnly ) )
482
return my_msgBox( "classISQL", "QFile.open", hFile.status(), NULL, NULL, NULL, qsFile ) ;
484
hFile.writeBlock( txt->text(), txt->text().length() );
487
// SAVE THE NEW FILE NAME
488
if ( pTabBar->currentTab() == 0 )
489
qsSQLFileName = qsFile;
491
qsResultsFileName = qsFile;
493
addStatus( QString().sprintf( "SAVE SQL: file %s saved", qsFile.data() ) ) ;