data type mapping from application to MaxDB

[Former Member]

Hi,

May be this is question is little too specific and the information provided here is not complete. Still please try and see if the question makes sense.

My application's internal data types are mapped to data types supported by different databases as per the information given below:

Based on this information i am trying to map the application data types to MaxDB data types and have given the mapping as given below.

Please correct me if you feel any of the mapping may not be correct.

Way to read the info is on the left hand side is the application data type and the right hand side in quote is database specific type.

MySQL

=====

COLUMNTYPE_BINARYSTRING = _T("VARBINARY");

COLUMNTYPE_LONG = _T("INT");

COLUMNTYPE_DOUBLE = _T("DOUBLE");

COLUMNTYPE_CHARSTRING = _T("VARCHAR");

COLUMNTYPE_DATETIME = _T("DATETIME");

COLUMNTYPE_BLOB = _T("LONGBLOB");

COLUMNTYPE_CLOB = _T("LONGTEXT");

Oracle

======

COLUMNTYPE_LONG = _T("NUMBER"); // NUMBER(38) == INTEGER

COLUMNTYPE_DOUBLE = _T("FLOAT");

COLUMNTYPE_STRING = _T("VARCHAR2");

COLUMNTYPE_DATETIME = _T("DATE");

COLUMNTYPE_BLOB = _T("BLOB");

COLUMNTYPE_CLOB = _T("CLOB");

DB2

====

COLUMNTYPE_BINARYSTRING_BEFOREWIDTH = _T("VARCHAR");

COLUMNTYPE_BINARYSTRING_AFTERWIDTH = _T("FOR BIT DATA");

COLUMNTYPE_LONG = _T("INTEGER");

COLUMNTYPE_DOUBLE = _T("DOUBLE");

COLUMNTYPE_CHARSTRING = _T("VARCHAR");

COLUMNTYPE_DATETIME = _T("TIMESTAMP");

COLUMNTYPE_BLOB = _T("BLOB");

COLUMNTYPE_CLOB = _T("CLOB");

SYBASE

=======

COLUMNTYPE_BINARYSTRING = _T("VARBINARY");

COLUMNTYPE_LONG = _T("INT");

COLUMNTYPE_DOUBLE = _T("FLOAT");

COLUMNTYPE_CHARSTRING = _T("VARCHAR");

COLUMNTYPE_WCHARSTRING = _T("UNIVARCHAR");

COLUMNTYPE_DATETIME = _T("DATETIME");

COLUMNTYPE_BLOB = _T("IMAGE");

COLUMNTYPE_CLOB = _T("TEXT");

Based on this info i am chosing the following datatypes from MaxDB for the application internal types.

COLUMNTYPE_LONG = _T("INTEGER")

COLUMNTYPE_DOUBLE = _T("FLOAT");

COLUMNTYPE_CHARSTRING = _T("CHAR");

COLUMNTYPE_DATETIME = _T("DATE"); // Should i chose TIMESTAMP or TIME instead ?

COLUMNTYPE_BLOB = _T("BLOB");

COLUMNTYPE_CLOB = _T("CLOB");

Regards

Raja

[lbreddemann · Accepted solution]

> COLUMNTYPE_LONG = _T("INTEGER")

> COLUMNTYPE_DOUBLE = _T("FLOAT");

> COLUMNTYPE_CHARSTRING = _T("CHAR");

> COLUMNTYPE_DATETIME = _T("DATE"); // Should i chose TIMESTAMP or TIME instead ?

> COLUMNTYPE_BLOB = _T("BLOB");

> COLUMNTYPE_CLOB = _T("CLOB");

Hi there,

this looks Ok so far.

What exactly do you want to store into your DATETIME field?

regards,

Lars

[Former Member · Accepted solution]

Hi Lars,

Right now am not sure about the answer to your question and i'll get back to you on that.

In the mean time which column type from the MaxDB do you think comes closest to the varbinary type of MySQL/Sybase.

COLUMNTYPE_BINARYSTRING = _T("VARBINARY");

Regards

Raj

[Former Member · Accepted solution]

Hi Lars owing to the discussion in the thread

i am changing the data type mapping for the following types.

Previously:

==========

COLUMNTYPE_BLOB = _T("BLOB");

COLUMNTYPE_CLOB = _T("CLOB");

Now:

======

COLUMNTYPE_BLOB = _T("LONG BYTE");

COLUMNTYPE_CLOB = _T("LONG UNICODE");

[Former Member · Accepted solution]

After studying what is offered by all other db's for their below mentioned date and time field i came to the conclusion that MaxDB requires TIMESTAMP field to store the same value and not DATE field as decided earlier.

MySQL

COLUMNTYPE_DATETIME = _T("DATETIME");

Oracle

COLUMNTYPE_DATETIME = _T("DATE");

DB2

COLUMNTYPE_DATETIME = _T("TIMESTAMP");

SYBASE

COLUMNTYPE_DATETIME = _T("DATETIME");

Regards

Raja