function to create tables for Access and MySQL

[lucasdebeltran]

Hello,

As the sintax is different, is there a function to créate tables for either Access or MySQL?.

Thank you.

[Enrico Maria Giordano]

Lucas,

Hello,

As the sintax is different, is there a function to créate tables for either Access or MySQL?.

Thank you.

This is the function I'm using:

Code: Select all  Expand view

STATIC FUNCTION ADDTABLE( cMot, cTab, aFld )

    LOCAL cQuery := "CREATE TABLE " + cTab + " ( "

    LOCAL cType

    LOCAL i

    IF cMot == "JET"
        cQuery += "Id COUNTER PRIMARY KEY, "
    ELSEIF cMot == "MSSQL"
        cQuery += "Id INT IDENTITY PRIMARY KEY, "
    ELSEIF cMot == "MYSQL"
        cQuery += "Id SERIAL, "
    ENDIF

    FOR i = 1 TO LEN( aFld )
        cType = aFld[ i, DBS_TYPE ]

        DO CASE
            CASE cType = "C"
                cQuery += aFld[ i, DBS_NAME ] + " VARCHAR ( " + NTRIM( aFld[ i, DBS_LEN ] ) + " ), "
            CASE cType = "N"
                cQuery += aFld[ i, DBS_NAME ] + " NUMERIC ( " + NTRIM( aFld[ i, DBS_LEN ] ) + ", " + NTRIM( aFld[ i, DBS_DEC ] ) + " ), "
            CASE cType = "D"
                cQuery += aFld[ i, DBS_NAME ] + " DATETIME, "
            CASE cType = "L"
                cQuery += aFld[ i, DBS_NAME ] + " INT, "
            CASE cType = "M"
                IF cMot == "JET"
                    cQuery += "[" + aFld[ i, DBS_NAME ] + "]" + " MEMO, "
                ELSEIF cMot == "MSSQL"
                    cQuery += "[" + aFld[ i, DBS_NAME ] + "]" + " TEXT, "
                ELSEIF cMot == "MYSQL"
                    cQuery += aFld[ i, DBS_NAME ] + " TEXT, "
                ENDIF
        ENDCASE
    NEXT

    cQuery = STRIM( cQuery, 2 ) + " )"

    SQLEXEC( cQuery )

    RETURN NIL

EMG

[Antonio Linares]

Enrico,

Where are functions STrim(), NTrim() and SqlExec() ? thanks

[Enrico Maria Giordano]

Antonio,

Enrico,

Where are functions STrim(), NTrim() and SqlExec() ? thanks

Code: Select all  Expand view

#define STRIM( cStr, nChr ) Left( cStr, Len( cStr ) - nChr )
#define NTRIM( nNumber ) LTrim( Str( nNumber ) )

FUNCTION SQLEXEC( cQuery )

    LOCAL cCns := "Your connectionstring here"

    LOCAL oCn := CREATEOBJECT( "ADODB.Connection" )

    oCn:CursorLocation = adUseClient

    oCn:Open( cCns )

    oCn:Execute( cQuery )

    oCn:Close()

    RETURN NIL

EMG

[lucasdebeltran]

Mr. Enrico,

Thank you very much, but I always get, either MYSQL or MSACCESS:

Incorrect arguments, out of range or in conflict with others. (0x800A0BB9)


What I am doing wrong?.

Thank you.

[Enrico Maria Giordano]

Lucas,

Mr. Enrico,

Thank you very much, but I always get, either MYSQL or MSACCESS:

Incorrect arguments, out of range or in conflict with others. (0x800A0BB9)


What I am doing wrong?.

Thank you.

Please, show me how are you calling the function. A reduced and self-contained sample would be better.

EMG

[lucasdebeltran]

Mr. Enrico,

I have to open the connection using oCn2:CursorLocation = adUseClient.

I was using my own Exec function, but in yours is that set on. Now it is working fine.


Also, in MySQL, when creating a table, this sets up auto_increment feature and it is managed by MySQL:

cQuery += "ID INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID), "



What is the equivalent please for MS Access?.

For MsAccess, I tried with NO luck:

cQuery += "Id INT IDENTITY PRIMARY KEY, "


But the auto increment feature is not managed propelly, I always get 0 at the Id field. Anything else to set up?.

Thank you.

Best regards

[Enrico Maria Giordano]

Mr. Enrico,

I have to open the connection using oCn2:CursorLocation = adUseClient.

I was using my own Exec function, but in yours is that set on. Now it is working fine.

I don't understand but ok, that's good.

Also, in MySQL, when creating a table, this sets up auto_increment feature and it is managed by MySQL:

cQuery += "ID INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID), "



What is the equivalent please for MS Access?.

For MsAccess, I tried with NO luck:

cQuery += "Id INT IDENTITY PRIMARY KEY, "


But the auto increment feature is not managed propelly, I always get 0 at the Id field. Anything else to set up?.

Thank you.

Best regards

Please look at my function:

Code: Select all  Expand view

   IF cMot == "JET"
        cQuery += "Id COUNTER PRIMARY KEY, "

EMG

[lucasdebeltran]

Mr. Enrico,

Thank you, but with your function the result is the same, with Access and ADO autoincrement fields are not managed.

With MySQL is fine.

Anything else has to be done?.

Thanks.

[Richard Chidiak]

Lucas

What provider do you use to connect to Mysql with ADO ?

Richard

[Enrico Maria Giordano]

Lucas,

Mr. Enrico,

Thank you, but with your function the result is the same, with Access and ADO autoincrement fields are not managed.

With MySQL is fine.

Anything else has to be done?.

Thanks.

My function works fine here, even with Access. As I already said, I need a sample of how are you calling it.

EMG

[lucasdebeltran]

Mr. Enrico,

Thank you very much. Your function works perfect. There is a bug at TDataRow managing Access numeric fields with decimals, not related to your function.

Best regards

[nageswaragunupudi]

Mr. Enrico,

Thank you very much. Your function works perfect. There is a bug at TDataRow managing Access numeric fields with decimals, not related to your function.

Best regards

Yes, in the case of blank row and it was fixed. You already have the fixed version with you

[lucasdebeltran]

Mr. Nages,

In the datarow.prg you sent me on 15th I still get the error.

The error only happens with MSACCESS, not with MySQL or MSSQL server.

Thank you.

[Antonio Linares]

Enrico,

Does your function support Memo fields ? How do you specify them ? thanks

Lucas,

I am using Enrico's code here and the Access tables are properly created with the autoinc counter