Microsoft T-sql Coding Standards
Microsoft T sql Coding Standards
Crystal Reports for Microsoft Dynamics Gp Great Plains – Overview & Faq
Microsoft Dynamics GP is successor of Great Plains Software Dynamics and eEnterprise ERP application, and Crystal Reports was always the reporting tool of the choice for Dynamics. Historically GP Dynamics was available on Pervasive SQL and Ctree platform, where Crystal Reports were limited, due to the limitation to Pervasive and Faircom ODBC drivers. The last version, available on Pervasive/Ctree was Microsoft Great Plains 7.5 and since version 8.0 GP Dynamics is available on Microsoft SQL Server only, and as you can expect, SQL Server opens all the possible horizons for reports development. Current Version of GP as of November 2007 is 10.0. In this small article we will open “philosophical” principles of Crystal Reports design for Microsoft Dynamics GP:
1. Crystal Reports is “just” a reporting tool, not and magic “all-the-problems-solver”. This means that you should not overestimate the power of report wizards, but instead design either SQL view or SQL stored procedure to accept parameters and return records set to your Crystal Report. Then all you need to do in CR is to group records, sort them, and of course apply styles and other “cosmetics”. We are placing this message in the first bullet, because we saw a lot of report design failures, where designer tried to do very complex GP tables links within CR and then apply further complex restrictions, and ended up with lines duplication, and other annoying problems, which in fact pile up if you try to fix them with similar patches and at the end of the way, report was abandoned
2. SQL View vs. Stored Procedure. Of course SP looks more elegant as it accepts parameters, which are translated to CR parameters, however SP is also more powerful than the view due to the ability to include temporarily tables and intermediate calculations (based on temporary tables). Temporary tables allow you to go beyond the standard techniques of grouping in SQL Select statement – this should be the real solution, which will eliminate records duplications, described in #1. If you plan to invest your time in CR practicing for GP, you should make several practical lessons where you base GP Crystal Reports on SQL view and stored procedure
3. Other reporting tools for GP. If you are CR developer, it is natural, that you try to apply Crystal Report in all the situations, however you should be aware that GP has very powerful pre-designed tools. If you need to create financial statements: Balance Sheet, P&L, Cash Flow – you should consider FRx report designer first, FRx links to GP General Ledger and so, if more efficient than CR in Financial Reporting. GP ReportWriter. This reporting tool gets the advantages of GP report launching screens, such as SOP Invoice form, from where you can print specific invoice – so called modified reports allow you to place your company logo on Invoice form. Other features of FRx and RW are outside of the scope, please check our publications on these products
4. GP Dexterity Reporting. Dexterity is IDE and programming language of GP. Dex allows you to customize GP forms and reports plus create new ones. In order to explore Dex option, we recommend you to contact GP Dexterity developer, who should help you in the selection
5. Calling CR from GP interface. You can use several techniques. First one is traditional – use VBA/Modifier (you will need customization site enabler license for GP), from VBA scripts you can call CR engine. Second technique is Dexterity, where you call Crystal Reports engine from your custom Dexterity sanscript code. You can also consider SDK for Visual Studio developer, allowing you to extend GP forms and of course call CR from your .Net extension application
About the Author
Andrew Karasev, Alba Spectrum Group, http://www.albaspectrum.com help@albaspectrum.com 1-866-528-0577, 1-630-961-5918, serving customers USA/Canada nationwide: Illinois, California, New York, Quebec, Ontario, Colorado, Utah, Wisconsin, Florida, Texas. Local service is available in Houston & Dallas: Richmond, Sugar Land, Katy, Rosenberg, Missouri City, Pearland, Friendswood, Meadows, Mission Bend, Jersey Village, Fort Worth; serving GP customers in Chicago, IL: Naperville, Aurora, Joliet, Wheaton, Bolingbrook, Romeoville, Lyons, Niles, Downers Grove, Lisle, West Chicago, Barrington, Schaumburg, Elk Grove Village, Lombard, Morris, Ottawa, Marseilles, Seneca, Oswego, Plainfield, Darien, Winchester, Hinsdale
Google I/O 2009 - Keynote Day 1 (full)
C++ Wrapper Library for Firebird Embedded SQL
Advantage of Embedded Firebird database is that it enables to save your application user’s time on database installation and also reduce requirements to his technical knowledge level.
Written by:
Eugene Wineblat,
Software Developer of Network Security Team.
- Embedded Firebird
- Limitations
- Building fbclient.dll
- C++ Wrapper
- Work with DB
- Classes Forge and Procedure
- Work with types
- Classes BufferReader and BufferWriter
- Structure of the project files
- List of the recommended literature
1. Embedded Firebird
1.1. Limitations of Embedded Firebird
If you want to use Embedded Firebird you should know about those limitations it applies to your work (version 2.0.5):
- THERE IS NO SUPPORT OF UNICODE. If you want to store information in unicode then you will have to care about the strings to be twice shorter than the maximal string length, and also store them as the arrays of bytes. You will have to convert such array to the Unicode when you get it.
- THERE IS NO SUPPORT OF UNICODE PATHS. Especially sad thing here is that library functions for creation and opening the Firebird databases do not support the Unicode paths.
- SIZE OF THE STORED PROCEDURE NAMES IS LIMITED. Such limitation is present in each database; however, in Firebird this size is depressingly small - just 30 characters. Usually this size is enough, but if you use the automatically generated names of the stored procedures, which include the names of the module and class, functional meaning and possibly limitations, it can be not enough. Anyway you should remember about this.
1.2. Building fbclient.dll
In order to proceed to the use of Firebird possibilities, you need to build the source code into the library (source code can be taken from the site http://firebirdsql.org ).
To build it you should go to the directory buildswin32 and use .bat file make_all.bat. The result will be files with the generated base definitions, constants and descriptions of the functions which will be located in the output directory.
In your work you will need the libraries fbclient_ms.lib (outputlib), icu*.dll (outputbin) and fbclient.dll (outputbin). However, it is necessary to remember that if you want to use Embedded Firebird then you should rename fbembed.dll in fbclient.dll and use this library.
For our wrapper we chose the least of files from output and src sufficient to use Firebird. These chosen in experimental way files can be seen in the directory of our project fbembed_provider_include.
To work with the Firebird database you should link library fbclient_ms.lib (fbembed_provider_includelib) to your project and place all dll-s (fbembed_provider_includedll) in the system directory ( /windows/system32).
2. C++ Wrapper
Our wrapper allows to:
- Put aside the details of FireBird database implementation
- Control resources during the work
- Control and process exceptional situations and errors
- Work with DB through the stored procedures
- Work with all types of data in the same way.
2.1. Work with the database
There are such functions to work with the database files:
- CreateDatabase – creates a file with empty database. Full path to the file with its name and extension should be specified.
It is also possible to specify login and password to access the base. If they are not specified then the standard login “SYSDBA” and password “MASTERKEY” are used.
UNICODE_FSS is the base encoding; however it does not mean that a database will begin to work with unicode.
- AttachDatabase – sets connection with database. UNICODE_FSS encoding is set by default.
- CreateFirebirdDatabaseFromFile – creates a database from a file.
To execute firebird-SQL constructions in different transactions the “GO$” separator is used.
Allowed constructions that can’t be used in one transaction are simultaneous table creation and using.
Example. Creation and setting connection with the database.
std::string db_name = "c:defaultbase.fdb";
fb::CreateDatabase ( db_name );
fb::CFirebirdForge forge ( db_name ); // attach inside
2.2. Forge and Procedure
CFirebirdForge class enables to set connection with a database (*.fdb), and create two types of procedures to work with the data in this base:
- CFirebirdProcedure (CFirebirdForge::CreateProcedure) is to call the existent in database stored procedure.
The name of the procedure is passed to the constructor and also the pointer to the buffer where all obtained data will be saved.
If the stored procedure has input parameters then it is necessary to add them in the same sequence and with the same names as they are used in this procedure (functions of AddTTTIn type are used, where TTT is a type. An object will be created in the procedure to implement the structure and behavior of this type).
If the procedure has output parameters then it is necessary to define them by means of the functions AddOutTTT, where TTT is a type of the obtained data. It is necessary to pass the name which is used in the RETURNS block of the procedure as a parameter. Parameters are defined in the same sequence as they are mentioned in the function description.
Example. Creation and execution of the procedure.
fb::CFirebirdForge forge ( db_name );
std::auto_ptr< fb::CFirebirdProcedure > pProc (forge.CreateProcedure
( "TEST_{id}", "INSERT", NULL) );
//this should be TEST_INSERT procedure
// add correct input arguments by type and sequence
pProc->AddStringIn( strVal );
pProc->AddWStringIn ( wstrVal );
pProc->AddDoubleIn ( dVal );
pProc->AddSingleIn ( fVal );
pProc->AddBinaryIn ( binVal );
pProc->AddInt8In ( int8Val );
pProc->AddInt16In ( int16Val );
pProc->AddInt32In( int32Val );
pProc->AddInt64In ( int64Val );
pProc->AddDateTimeIn ( timeVal );
pProc->AddGuidStringIn ( guidVal );
pProc->AddLongBinaryIn ( lbinValCpy );
pProc->AddLongWStringIn ( ltxtVal );
pProc->Execute();
- CFirebirdExecutable (CFirebirdForge::CreateQuery) is the procedure to execute SQL-query without parameters and returned values.
Example.
fb::CFirebirdForge forge ( db_name );
std::auto_ptr< fb::CFirebirdExecutable > pQueryTable ( forge.CreateQuery
("CREATE TABLE TBLTESTTYPES(
STRING Varchar(255),}
_ WSTRING Varchar(512),}
_ DOUBLE_T Double precision NOT NULL,}
_ FLOAT_T Float NOT NULL,}
_ BINARY Varchar(1024),}
_ INT8 Char(1) NOT NULL,}
_ INT16 Smallint NOT NULL,}
_ INT32 Integer NOT NULL,}
_ INT64 Numeric(10,0) NOT NULL,}
_ DATETIME Timestamp NOT NULL,}
_ GUID Varchar(36) NOT NULL,}
_ BLOBBIN Blob sub_type 0,
BLOBTEXT Blob sub_type 1
);" ) );
pQueryTable->Execute ();
If it is necessary to execute a SQL-query which returns some parameters, simply create a stored procedure for it using CreateQuery and call it by CreateProcedure.
2.3. Work with types
Te behavior of different data types which the wrapper works with is described in the CSQLData class. This is a template class, which is instantiated corresponding to the data:
- SMALLINT
- INTEGER
- INT64
- FLOAT
- DOUBLE PRECISION
- CHAR (n)
- VARYING
- TIMESTAMP
- DATE
- TIME
- BLOB
Depending on the C++ data types you need to store the different functions of FirebirdProcedure class are used. Functions of this class allocate the memory for variables and convert their values to the format required for work with Firebird SQL. To put these problems aside from the wrapper users the format of Firebird SQL fields where the information will be stored is predefined. You can see the correspondence in the table:
C++
FirebirdProcedure::Add
Firebird SQL
float
Single
Float
double
Double
Double precision
char
Int8
Char(1)
short
Int16
Smallint
int
Int32
Integer
__int64
Int64
Numeric(10,0)
std::string
GuidString
Varchar(36)
std::string
String
Varchar(n)
std::wstring
WString
Varchar(n)
std::wstring
LongWString
Blob syb_type 1
std::vector< char >
Binary
Varchar(n)
std::vector< char >
LongBinary
Blob sub_type 0
double
DateTime
Timestamp
For simple types (integer ones and types with a floating point) the general template format is used. For types which do not just allocate data or use difficult logic of converting there are specializations:
- time (TIMESTAMP)
In our realization the time is used as a double type and is a variant presentation of time (see functions of converting in fbutils.cpp - SystemTimeToTmTime / TmTimeToSystemTime ). Functions for time converting are developed using the internal Firebird classes ( srccommonclassestimestamp.h .cpp )
- string (VARYING and CHAR)
It is necessary to allocate memory dynamically in this type, and there is different volume for CHAR and VARYING. Also it is possible to add Unicode to them by std::wstring - it will be converted to the set of bytes. You just need only to watch that the size of such string is twice less than the maximal length of the field.
- arrays of data (BLOB)
Special group of functions is used to work with the BLOB type.
Firebird does not enable to write or read the arrays of data directly. To work with them you should first get a handle, by means of which you can write or read information. Functions isc_create_blob, isc_put_segement are used for writing and isc_open_blob, isc_get_segment are used for reading. A maximal size of segment in BLOB is 32768 byte.
If you want to write NULL value in any field of any type then during the initialization of variables in procedure write -1 in the sqlind field of the XSQLVAR structure. The same is right for the getting the information – if sqlind is equal to -1 it means that returned value is NULL.
We didn’t implement such types as DATE, TIME in our code, but it’s not hard to add them if you want to use just these types.
2.4. BufferReader and BufferWriter
All data obtained from the stored procedures are serialized in the buffer by the BufferWriter class. Correspondingly you should then get them from the buffer using BufferReader class.
The int32 field goes at the beginning of all information, the amount of the obtained records is written in it – this field is necessarily present in every resulting buffer. If 0 records were returned, then the buffer will contain only 0.
The special function of BufferReader class corresponds to every data type:
Single – read_single
Double – read_double
Int8 – read_int8
Int16 – read_int16
Int32 – read_int32
Int64 – read_int64
GuidString – read_
String – read_string_as_wstring
WString – read_wstring2
LongWString - read_wstring2
Binary – read_raw / read_raw_ref
LongBinary - read_raw / read_raw_ref
DateTime – read_double
Example. Obtaining results.
std::vector< char > arrOut;
std::auto_ptr< fb::CFirebirdProcedure> pProc ( pAdoForge->CreateProcedure (
"TEST_{id}", "SELECT", &arrOut ) );
... // add out parameters
pProc->Execute();
cmn::BufferReader br;
br.ConnectTo ( &arrOut.front(), arrOut.size() );
for ( int I = 0, count = br.read_int32(); I != count; ++I )
{
// get the return values from buffer
//br.read_string () / br.read_wstring () / br.read_double ();
}
3. Structure of project files
SrcFireBird.sln – solution file for Microsoft Visual Studio 2003
Srcfbembed_provider_include - headers, library and dll files of Firebird version 2.0.5, built for Win32 platform
Src fbembed_provider_lib - lib-library project with Firebird C++ Wrapper realization
Srcutils - project with utility functions and classes, used in the realization of Firebird C++ Wrapper
5. List of the recommended literature
All information about Firebird SQL can be found in the official sources
(http://www.firebirdsql.org/index.php?op=doc ).
Download Wrapper Source Files from our official site.
About the Author
Apriorit is an Ukrainian software development company.
Apriorit develops its own products as well as provide offshore development and QA services in the areas of advanced system programming, driver development, software for devices.
One of the key values of Apriorit's specialists is knowledge generation and sharing of experience.
Learn more about Apriorit and its experience at Apriorit Official site
phpbay]Microsoft T sql Coding Standards, 51, "", ""[/phpbay]
