Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

VB6 and MySQL not getting along...

I've got VB6 and MyODBC 3.51 on a client computer

I've got a server with MySQL(specifically, RedHat 7.0 that comes with MySQL 3.23).

I've gotten the MyODBC on the client successfully "talking" to the MySQL server (I get a "test successful" from the test connection button on the setup).

now, when I use the data-source in a VB6 data environment, the Recordset functions are not behaving properly. ie#1: the .Open() is successful, but it seemingly always returns an empty set, even when pulling up entire tables. ie#2: the EOF() and BOF() funcs are both returning TRUE, but the both should be FALSE if the recordset is empty.

if anyone has any ideas on solutions to this, I'd be most grateful.

thanks in advance!!

-this is posted in a few other boards where it is applicable.

Comments

  • GideonOmegaGideonOmega Member Posts: 617
    " ie#2: the EOF() and BOF() funcs are both returning TRUE, but the both should be FALSE if the recordset is empty."

    I believe that the EOF and BOF will return true if no records are in the recordset(ie. "" = End Of File = true, etc)

    could you post the code that you are using I might beable to pick it apart a bit better, also your using a dsn through ODBC are you using a connection object for your recordset?

    [blue]
    C:Dos
    C:Dos Run
    Run Dos Run
    [/blue]

  • LAbluesmanLAbluesman Member Posts: 14
    ok, you were right about the EOF() and BOF(), I guess I got mixed up, lol.

    anyway, here is the code for looking up recordsets. NOTE: I am using a data environment, so, all the connection code and such are not here.

    [BLUE]
    str_SQLstring = "SELECT * FROM testtable"

    Debug.Print str_SQLstring

    DataEnvironment1.rscmnd_mysql1.Open (str_SQLstring)

    ' ------ begin testing section ------ ** ------

    'make a cloned copy of the original recordset
    Set rst_mySQL_recordset = DataEnvironment1.rscmnd_mysql1.Clone()

    'Check the number of records that was returned in the recordset.
    shitlong = 101
    [/BLUE]
    **This is where the first error occurs, immediately below.
    [BLUE]
    shitlong = DataEnvironment1.rscmnd_mysql1.RecordCount

    ' 'Move to the first record in the recordset.
    ' rst_mySQL_recordset.MoveFirst

    'Specifies on which page the current record resides.
    'Sets or returns a Long value from 1 to the number of pages in the Recordset object (PageCount),
    ' or returns one of the following constants.
    If DataEnvironment1.rscmnd_mysql1.AbsolutePage = adPosUnknown Then
    shitlong = DataEnvironment1.rscmnd_mysql1.AbsolutePage
    Else
    shitlong = DataEnvironment1.rscmnd_mysql1.AbsolutePage
    End If

    'Specifies the ordinal position of a Recordset object's current record.
    'Sets or returns a Long value from 1 to the number of records in the Recordset object (RecordCount),
    ' or returns one of the following constants.
    If DataEnvironment1.rscmnd_mysql1.AbsolutePosition = adPosUnknown Then
    shitlong = DataEnvironment1.rscmnd_mysql1.AbsolutePosition
    Else
    shitlong = DataEnvironment1.rscmnd_mysql1.AbsolutePosition
    End If

    'DataEnvironment1.rscmnd_mysql1.ActiveCommand

    shitbool = DataEnvironment1.rscmnd_mysql1.BOF

    shitlong = DataEnvironment1.rscmnd_mysql1.PageCount

    shitbool = DataEnvironment1.rscmnd_mysql1.EOF

    shitlong = DataEnvironment1.rscmnd_mysql1.PageSize
    [/BLUE]

    now, if you take this code and put the EOF/BOF()'s before the first error, they'll return FALSE.

    if you also need some info from the server odbc.ini file, the ODBC settings on the client, or some stuff from the data environment, be specific as to what you need, and I'll try to get it to you.

    thanks!!


    : " ie#2: the EOF() and BOF() funcs are both returning TRUE, but the both should be FALSE if the recordset is empty."
    :
    : I believe that the EOF and BOF will return true if no records are in the recordset(ie. "" = End Of File = true, etc)
    :
    : could you post the code that you are using I might beable to pick it apart a bit better, also your using a dsn through ODBC are you using a connection object for your recordset?
    :


  • GideonOmegaGideonOmega Member Posts: 617
    Question: Do you need to use a data enviroment for any paticular reason, have you considered using a connection object instead?

    I myself have never used a data enviroment myself for a connection like that, you can use the dsn you have already created to connect through instead, if your not familer with the methods and want to give it a try I would be more then happy to help.
    [blue]
    C:Dos
    C:Dos Run
    Run Dos Run
    [/blue]

  • LAbluesmanLAbluesman Member Posts: 14
    yes, we are using the data environment for a reason: this application has to be able to jump databases, such as between a test-db and an "actual working version" db.

    but, if you believe/know of how to make a connection object successfully talk to the MySQL db, then please post it/let me know, and I'll try to translate it over to the data env. setup.

    now, here's an extra !!NOTE!! : I believe the problem is in VB6, or in some of it's settings, as opposed to the problem being with the ODBC client settings. This is because we have a second program (cppDemo) that also uses the ODBC client to run simple SQL queries on the db, and it runs correctly.

    : Question: Do you need to use a data enviroment for any paticular reason, have you considered using a connection object instead?
    :
    : I myself have never used a data enviroment myself for a connection like that, you can use the dsn you have already created to connect through instead, if your not familer with the methods and want to give it a try I would be more then happy to help.

  • GideonOmegaGideonOmega Member Posts: 617
    Ok let me start by asking you, in your code that you posted you use a clone function, now I've never used it before myself, so could you explan it a bit, also is it used in the cppdemo.

    now I'm guessing it is used to make a copy of the data base in question, but I belive that you could use a connection object fed by your dsn to acomplish the same thing(directly connecting to the database instead of going through a dataenviroment).

    also have you tried testing the app without using a cloned copy?

    Lastly have you ever used a connection object before?



    : yes, we are using the data environment for a reason: this application has to be able to jump databases, such as between a test-db and an "actual working version" db.
    :
    : but, if you believe/know of how to make a connection object successfully talk to the MySQL db, then please post it/let me know, and I'll try to translate it over to the data env. setup.
    :
    : now, here's an extra !!NOTE!! : I believe the problem is in VB6, or in some of it's settings, as opposed to the problem being with the ODBC client settings. This is because we have a second program (cppDemo) that also uses the ODBC client to run simple SQL queries on the db, and it runs correctly.

    [blue]
    C:Dos
    C:Dos Run
    Run Dos Run
    [/blue]

  • LAbluesmanLAbluesman Member Posts: 14
    ok, about the clone() : it is used to make a shallow copy of the recordset object. This code is pulled and modified from my code that I used to connect VB6 and a Unify DB - in this case, the data env.'s recordset object was misbehaving for add/modify/delete actions, so I made a clone copy and performed operations on it.

    In this code, since the data env.'s recordset object wasn't working correctly, I figured I'd try the clone work-around again.

    now, another ***UPDATE*** to this:
    It appears as though VB6 ~is~ understanding and correctly using the ODBC client - when I view and tinker with the ODBC client through the data env., it correctly displays all of the field names for the table. so, the problem seems to be occuring at runtime, when I try to use .Open(), or at least it's not something in the data env. settings.

    : Ok let me start by asking you, in your code that you posted you use a clone function, now I've never used it before myself, so could you explan it a bit, also is it used in the cppdemo.
    :
    : now I'm guessing it is used to make a copy of the data base in question, but I belive that you could use a connection object fed by your dsn to acomplish the same thing(directly connecting to the database instead of going through a dataenviroment).
    :
    : also have you tried testing the app without using a cloned copy?
    :
    : Lastly have you ever used a connection object before?
    :
    :
    :
    : : yes, we are using the data environment for a reason: this application has to be able to jump databases, such as between a test-db and an "actual working version" db.
    : :
    : : but, if you believe/know of how to make a connection object successfully talk to the MySQL db, then please post it/let me know, and I'll try to translate it over to the data env. setup.
    : :
    : : now, here's an extra !!NOTE!! : I believe the problem is in VB6, or in some of it's settings, as opposed to the problem being with the ODBC client settings. This is because we have a second program (cppDemo) that also uses the ODBC client to run simple SQL queries on the db, and it runs correctly.
    :
    : [blue]
    : C:Dos
    : C:Dos Run
    : Run Dos Run
    : [/blue]
    :
    :

  • GideonOmegaGideonOmega Member Posts: 617
    [b][red]This message was edited by Moderator at 2003-12-16 12:47:7[/red][/b][hr]
    Ok this is how you would create a connection object using a dsn, this is assuming any password and username info is stored in the dsn.

    for this to work you must include the refference (Microsoft activex data objects 2.6)
    [code]
    Set g_objConn = New ADODB.Connection 'creates the instance of the connection object

    'opens the connection object using the dsn
    g_objConn.Open "DSN=MultiLease;User ID=;Password=;"
    The above was set up for a SQL SERVER database, but should work fine as it uses a DSN.
    [/code]

    ok, now that the connection object is istablished, try using the recordset object like so:
    [code]

    dim objRS as ADODB.Recordset
    dim strFieldValue as String
    set objRS = new ADODB.Recordset


    with objRS
    .ActiveConnection = g_objConn
    .CursorLocation = adUseClient
    .CursorType = adOpenForwardOnly
    .Source = "Select * from tblName"
    .Open
    'assign a text field value from the first record
    strfieldvalue = !fieldName
    .Close
    End with

    'you can loop through the recordset as follows:
    With objRS
    .ActiveConnection = g_objConn
    .CursorLocation = adUseClient
    .CursorType = adOpenForwardOnly
    .Source = "Select * from tblName"
    .Open
    If .EOF or .BOF = true then goto EmptyRecordset
    Do while Not .EOF
    ....assign values here
    .MoveNext
    Loop
    .Close
    End With
    Exit sub
    EmptyRecordset:
    msgbox "Error, no records found"
    End sub
    [/code]

    give that a try and let me know if it works out for you, this code can be modified to allow for use of different databases if needed.


    [blue]
    C:Dos
    C:Dos Run
    Run Dos Run
    [/blue]



Sign In or Register to comment.