DBImport v3.3 Chinese Release: Database data interoperability and document generation tools (IT staff essentials)

Objective:

Long time no writing, the distance from the previous article is 3 months ago, although the work is very busy, mainly still lacks the content and passion of writing, so not how to do.

There were one months of continuous interview at different levels to apply for the staff, very much want to write the impulse, and later still resisted.

Estimates write is also the kind of people who speak ill, disciplining emotional text, no nutrition, so the mood has not wanted to write.

In addition to the management of the company, but also developed a set of applications for the rapid development of information systems framework, this opportunity to write the text and share with everyone.

The following words are in the text.

Background:

There are not many releases of this dbimport tool, only: V1.0, V2.0, V3.0, V3.2, and V3.3 released today.

Because of the frequency of using this tool (data-guide, data-directed scripts, and database documents) frequently,

Plus the previous version has been very stable, basic to meet individual needs, so the frequency of the update is not high, starting from V3.0, more than a year to release a version.

The power of this release is mainly driven by the needs of users (paid or non-paid) and cyq.data the underlying framework.

Here's a look at the history text:

DBImport V3.3 the content of the version update is as follows:1: Increase data interoperability between MSSQL, MYSQL, and Oracle, and convert related field descriptions at the same time.

PS: Guide The data when the instructions are also taken over, if you want to copy a company database, and do not import and export backup permissions, use this tool to guide O.

2: Database script Export Add field description script.

PS: There is also the reason for the smooth out.

3: Increase the processing of the decimal place, the data between each other without loss of precision.

PS: Before the version on the decimal place, if the need to accurately guide the past, the need to manually deal with the resulting table structure, to change the number of re-guide, this is done directly this aspect of the matter.

4: Database document export increases the display of the decimal field.

PS: With 3, 4 is also a straight out.

5: Increase the processing of n data types, which may not be common.

PS: This is said to be more general, because all the types of MSSQL are dealt with, the underlying code is also changed a lot, so too many things to use more general words to explain.

6: The configuration item increases whether the stored procedure paging settings are used.

PS: Because the underlying CYQ.DATA framework removes Oracle's paging stored procedures, the distribution stored procedure for MSSQL also adds configuration items that are not enabled, so there is more of this configuration item on the software.

7: The configuration item adds the following action items:A: Bulk INSERT "include PRIMARY key" B: Bulk Insert "Ignore primary key" C: Bulk Update "Automatic recognition based on primary key" D: Insert or update "Auto-identify based on primary key"

PS: This feature is very powerful, through the D option, almost can achieve data synchronization between the two databases, and repeated n times not afraid of.

:http://www.cyqdata.com/download/article-detail-42517

New:

Share the issue with this version update: 1:mssql:

A:timestamp type, which stores binary data, other database types are dates and require special handling.

B: Identify the database version number, can be obtained through the ServerVersion property of the Connection object , and previously did not care about this field.

C: Use sqlbulkcopy BULK INSERT, pay attention to the data length problem, if the length is truncated, will cause the MSSQL service directly hanging off, very abnormal results.  

D:numeric types of wood have corresponding SqlDbType.

E: When the Where in ID (1,2,3,4,...) expression is directly above 10,000, MSSQL2000 hangs up directly, and other versions of the database are normal.

f:mssql2000 has an XML field, and the same thing that Wood has about Max is the need to convert other types of processing.

G: Insert binary data into the MSSQL database so that it can be this whole:

UPDATEAlltypeSET
B5=newid(),
--b7= ' <aa>dddd</aa> '
A1=(SELECT* fromOPENROWSET(BULKN'C:\Users\cyq\Pictures\ico\taobao.ico', Single_blob) asAa

WHEREId=12:sqlite:

A: If the field is self-increasing, then it must be a primary key, and only this key can exist.

B: If it is self-increasing, the database script keyword must be in advance and cannot be defined later, such as:

Of the Positive solution:
"ID"INTEGERPRIMARYKEYAutoIncrement notNULL,
The wrong:
"ID"INTEGERAutoIncrement notNULL,
PRIMARYKEY("ID")

C: For the GUID type of the field, because the storage structure is 16 binary, so the query is cumbersome, need to make the following conversion, before the comparison query:

string gv=bitconverter.tostring (The new Guid ("509e4e37-43ed-4e3d-b3bc-1c0929f0d151"). Tobytearray ()). Replace ("-", "" ");  

The final condition becomes where id=x ' gv This string of things '

3:access:

A: For GUIDs, normal is where id= ' 509e4e37-43ed-4e3d-b3bc-1c0929f0d151 ', but this condition is only normal when queried and deleted, and when updated, it has to be written like this:

Where id= ' {509e4e37-43ed-4e3d-b3bc-1c0929f0d151} ' so there's nothing to do but add a brace yourself.

4:mysql:

A: You cannot set the primary key for a text or BLOB field.

B: For the TinyInt field, want to use 0-255, should be defined as: TinyInt (3) UNSIGNED

C: There can only be one self-increment column, if present, must be defined as the primary key.

D: (Chinese content or comment) garbled problem or to configure the MySQL installation directory under the My.ini encoding.

5:oracle:

A: Null values are not allowed for fields that are set to a not Null property, and the workaround is to insert an empty string.

B:ORA-08002: Sequence SEQ1. Currval has not been defined in this session, the problem requires calling Seq1.nextval first .

C: for where ID in (1,2,3,4,5 ...) the maximum expression is 1000, which exceeds this number and needs to be split into where ID in (,.. +) or ID in (1001,1002...2000) ...

D: When executing multiple statements in bulk, you can generally use:

begin
Statement 1;
Statement 2;
Statement 3;
End;

But it is also not useful when, for example, the statement is the comment on column table. The field is description. The solution is to split into a single piece of execution.

Summarize:

Time is not too early, tomorrow also have to work, got under, for the loyal message to 32 likes support brother sister:

For these two days, only the local information under the Contact Author (tab) on the software is sent to the email address under the contact information, the probability of obtaining a randomly sent software registration code.

thanks~~~