FiveTech Support Forums

Hello,

Anyone is using it?.

Any particular issue?. For example, SQLite has its own format for dates, different tan MSACCESS/MYSQL.

Thank you very much.

Thank you.

And what about logical fields?.

For example, with Access I use this filter:

       cCondicion := "DEVUELTO = 'False'" // revisar MySQL
       oRs:Filter = cCondicion

It Works perfect. Maybe it will cause trouble with Oracle?.

It is quite funny, Access and MSSQL do not behavie the same, but they sould.

lucasdebeltran wrote:Thank you.

And what about logical fields?.

For example, with Access I use this filter:

cCondicion := "DEVUELTO = 'False'" // revisar MySQL

cCondicion := "DEVUELTO = 0"

Mr Lucas

At present, FWH Sql functions support Oracle also.

FWH 13.07 version's ADO and SQL functions fully support SQLite and Oracle also suitably taking care of the field types and formatting appropriate to the RDBMS in addition to MSACCESS, MSSQL and MySQL.

In the next versions, we propose to add FireBird and Postgresql. I suppose with that we would be covering adequate range of RDBMSs.

Even now, for example:
FW_DateToSQL( dDateTime ) returns formatted date or datetime literal suitable to the RDMS, including Oracle. Please test the function.

Similarly FW_ValToSQL( uVal ) returns appropriately formatted literal
If uVal is an array the function returns comma delimited literals enclosed in parenthesis, which can be readily used in insert statements.

The purpose of all these functions is that you write the same code for all RDBMSs in the manner you know to write in Harbour and these functions take care of all the conversions required.

And our programmers need not refer to any one or any manual how to handle different RDBMSs

As mentioned above, Oracle does not provide native support for Boolean fields.
The most prevalent practice among Oracle programmers is to use Character field (width 1 ) and use 'Y' or 'N' to represent true or false. I have seen this practice with 99% of the programmers including well known authorities on Oracle.

Still I support using NUMBER field with values 1 or 0 ( as EMG suggested above ). When we do cross-platform programming this approach is very useful.
Our FWH SQL functions assume this approach for Oracle also.

For all others including SQLite, you can use BIT field type.
Recordset field value returns .T. or .F. and we can assign .T. or .F..

If you use BIT fields ( MSACCESS, MSSQL, MYSQL, SQLITE ) we can use 1/0 or TRUE/FALSE.
oRs:Filter := "MARRIED=TRUE"
or
oRs:Filter := "MARRIED=1"
But for Oracle
oRs:Filter := "MARRIED=1" only.

If we adopt the later kind of filters, we can be sure of covering Oracle also.

Oracle does not provide for AutoIncrement fields also. We need to create a Sequence and then a Trigger to insert the sequence values in the primary key field.

In version 13.07, the function FW_AdoCreateTable() for Oracle creates the table, sequence and a trigger. All this is done automatically and for our programmers it is as simple as creating auto inc field with any other RDBMS.

I continue usage of some of these functions in the next posting

For inter conversion of Date to/from DateTime variables, FWH provides
FW_DTOT() and FW_TTOD() functions. These functions are written in 'C' for speed.

Mr Lucas

Please note that there is a difference in formatting dates for ADO and for SQL.

For setting filters in ADO we need to format dates as #YYYY-MM-DD#.
The function FW_DateToADO( dDate ) returns a string literal in this format.

Example:
oRs:Filter := "HIREDATE > " + FW_DateToADO( dMyDate )
You can use this date format in ADO irrespective of the RDBMS. (Not in SQL statements executed in ADO)

For SQL statements, ( example WHERE clauses, UPDATE and INSERT statements) we need to format the dates in one of the formats recoginized by the respective RDBMS. Each RDBMS accepts some alternative formats.

We are more interested in multi-platform programming and therefore we need to reduce the number of formats to use.

Oracle: DATE 'YYYY-MM-DD' and TIMESTAMP 'YYYY-MM-DD HH:MI:SS'
MSSQL: 'YYYY-MM-DD' and 'YYYY-MM-DDTHH:MM:SS'
Others: 'YYYY-MM-DD' and 'YYYY-MM-DD HH:MM:SS'

MS Access accepts #YYYY-MM-DD#. But also accepts 'YYYY-MM-DD'

By using FW functions all these formatting needs are automatically taken care of. You can confidently write the same code which works on any RDBMS

Lucas,

for now I using Oracle 11g.
in Oracle not existing limit size a database file.
but for the all depend from Aplication Architecture have we do.

right there a difference architecture between Oracle and other RDBMS.
of all things, you can review the manual architectur.
but basically all function to process the data.

to save a data in format dates, in Oracle we can use Internal Oracle Function
like TO_DATE() or to do otherwise TO_CHAR()

de facto prescriptive from Mr.Nages and Mr.Rick is good information for the case.

Hope this helps.

Mulyadi

TO_DATE()

Thank you.

SO in this case:

         dDesde := date()

          cCondicion := "FECHA >= "+DTOC(dDesde) +" AND FECHA <= "+DTOC(dHasta)   // REVISAR MYSQL
          oRs:Filter = cCondicion

Instead of Dtoc, I should use FW_DateToSQL( dDesde )?.

Thanks.

Mr. Nages,

Thank you very much.

About using logical fields, I do for example:

if oRs:Fields( "PRESTA" ):Value = .T.
[..]

But you advise me to use 1 or 0 for logicals as to work with Oracle.

But if I change .t. to 1 I get an error.

Thank you very much.

lucasdebeltran wrote:About using logical fields, I do for example:

if oRs:Fields( "PRESTA" ):Value = .T.
[..]

But you advise me to use 1 or 0 for logicals as to work with Oracle.

But if I change .t. to 1 I get an error.

lucasdebeltran wrote:Mr. Nages,

Thank you very much.

About using logical fields, I do for example:

if oRs:Fields( "PRESTA" ):Value = .T.
[..]

But you advise me to use 1 or 0 for logicals as to work with Oracle.

But if I change .t. to 1 I get an error.

Thank you very much.