MySQL

[MLambert]

Hi Team,
I am starting to work with MySQL and I have a couple of questions. I am not a 'clever' user of QB64 .. my code is simple... hence the question.

MYSQL *mysql_init(MYSQL *mysql) .. this is MySQL code.
How does this get converted to
mysql_init%& (BYVAL always_0 AS _OFFSET) ? .. and what is this telling me ?

Thanks,

Mike

[Juanjogomez]

Hi MLambert,

I Think you want to connect to the database. The code that i use is:

DECLARE CUSTOMTYPE LIBRARY "mysql_helper"
    FUNCTION offset_to_string$ ALIAS offset_to_offset (BYVAL offset AS _OFFSET)
    FUNCTION offset_at_offset%& (BYVAL offset AS _OFFSET)
END DECLARE
DECLARE DYNAMIC LIBRARY "mysql"
    FUNCTION mysql_init%& (BYVAL always_0 AS _OFFSET)
    FUNCTION mysql_error$ (BYVAL mysql AS _OFFSET)
    FUNCTION mysql_get_client_info$
    FUNCTION mysql_real_connect%& (BYVAL mysql AS _OFFSET, host AS STRING, user AS STRING, password AS STRING, db AS STRING, BYVAL port AS _UNSIGNED LONG, BYVAL unix_socket AS _OFFSET, BYVAL client_flag AS _UNSIGNED _OFFSET)
    FUNCTION mysql_real_connect_dont_open%& ALIAS mysql_real_connect (BYVAL mysql AS _OFFSET, host AS STRING, user AS STRING, password AS STRING, BYVAL db AS _OFFSET, BYVAL port AS _UNSIGNED LONG, BYVAL unix_socket AS _OFFSET, BYVAL client_flag AS _UNSIGNED LONG)
    SUB mysql_query (BYVAL mysql AS _OFFSET, mysql_command AS STRING)
    FUNCTION mysql_query& (BYVAL mysql AS _OFFSET, mysql_command AS STRING)
    FUNCTION mysql_store_result%& (BYVAL mysql AS _OFFSET)
    FUNCTION mysql_num_fields~& (BYVAL result AS _OFFSET)
    FUNCTION mysql_num_rows&& (BYVAL result AS _OFFSET)
    FUNCTION mysql_fetch_row%& (BYVAL result AS _OFFSET)
    FUNCTION mysql_fetch_lengths%& (BYVAL result AS _OFFSET)
    SUB mysql_close (BYVAL mysql AS _OFFSET)
    SUB mysql_free_result (BYVAL result AS _OFFSET)
END DECLARE

DIM SHARED errf AS LONG
DIM SHARED conn AS _OFFSET
conn = mysql_init(0)
DIM columns AS LONG
REDIM SHARED DB_RESULT(columns, rows) AS STRING

IF conn = 0 THEN
    PRINT "I Don't connect to MYSQL client!": SLEEP (0): x$ = INKEY$
ELSE
    PRINT "connected"
END IF

DIM conexion AS _OFFSET
'*** Open Database ***
conexion = mysql_real_connect(conn, "127.0.0.1", "root", "key", "database", 0, 0, 0)
IF conexion = 0 THEN PRINT "I don't connect to database": x$ = INPUT$(1)

Best regards

[MLambert]

Thank you Juanjogomez for your response. I apologise for not replying sooner but I did not see the response until just now.

I have another question ...

I can connect and perform a select.

How do I get the data from the output of the select into my QB64 variables and vice versa ??

Thanks,

Mike

[Juanjogomez]

Hi Mlambert.

No problem. For example, look for clients named PETER in table client

a$ = "PETER"

busclave$ = "SELECT * FROM client WHERE name = '" + a$ + "'" + CHR$(0)  '---you always have to finish the command with CHR$(0)
CALL basedato(busclave$, rows, columns)

IF errf THEN PRINT "ERROR "; errf '--if there is a syntax error or another typ
IF rows <> 0 THEN
    FOR n% = 1 TO rows
        FOR m% = 1 TO columns
            PRINT DB_RESULT(m%, n%) '--Print all the data of each client whose name is Peter
        NEXT
    NEXT
END IF

END

'----------------------------------------------------------- MYSQL ----------------------------------------------------------------------------
SUB basedato (busclave$, rows, columns)
    mysql_query conn, busclave$
    DIM result AS _OFFSET
    result = mysql_store_result(conn)
    IF LEFT$(busclave$, 6) = "SELECT" THEN
        DIM row AS _OFFSET
        DIM dato AS _OFFSET
        columns = mysql_num_fields(result)
        rows = mysql_num_rows(result)
        '*** Save in Array ***
        REDIM DB_RESULT(columns, rows) AS STRING
        FOR filas = 1 TO rows
            DIM mysql_row AS _OFFSET
            mysql_row = mysql_fetch_row(result)
            DIM mem_mysql_row AS _MEM
            mem_mysql_row = _MEM(mysql_row, columns * LEN(an_offset%&)) 'The upper limit is unknown at this point
            DIM mysql_lengths AS _OFFSET
            mysql_lengths = mysql_fetch_lengths(result)
            DIM mem_mysql_lengths AS _MEM
            mem_mysql_lengths = _MEM(mysql_lengths, columns * 4)
            DIM mem_field AS _MEM
            FOR campos = 1 TO columns
                length = _MEMGET(mem_mysql_lengths, mem_mysql_lengths.OFFSET + (campos - 1) * 4, _UNSIGNED LONG)
                mem_field = _MEM(_MEMGET(mem_mysql_row, mem_mysql_row.OFFSET + (campos - 1) * LEN(an_offset%&), _OFFSET), length)
                DB_RESULT(campos, filas) = SPACE$(length)
                _MEMGET mem_field, mem_field.OFFSET, DB_RESULT(campos, filas)
                _MEMFREE mem_field
            NEXT
            _MEMFREE mem_mysql_lengths
            _MEMFREE mem_mysql_row
        NEXT
    END IF
    mysql_free_result result
END SUB


Regard

[MLambert]

Thank you. I will now try to digest your code.

Mike

[MLambert]

Hi  Juanjogomez,

I get a syntax error in line

       mysql_query conn, busclave$

Mike

**** IGNORE THIS .... FOUND THE ANSWER ****

[MLambert]

*** Ignore this - worked it out

Hi  Juanjogomez,

I am having great difficulty in reading your program.

Why do you use _mem ??  I am maybe reading 4 million rows .. will this fit them all ?

Will DB_RESULT hold millions of transactions ??

what does "DIM mem_mysql_row as _MEM mean" ?

why do this "REDIM DB_RESULT(columns, rows) AS STRING"

If I read the a row 9 to be updated ) via an index .. do I just issue an update ?? or is there another step ??

It would be very helpful if you would pls place comments in your code... or maybe I don't bother trying to understand your code and just use it.

Will it handle CRUD ? or will it need altering ??

For "select *" can I just read a row and print it .. then go and read another row and print it etc... without having to use memory variable ??

Thanks for the help,

Mike

[MLambert]

*** Ignore this - worked it out

Hi  Juanjogomez,

I assume I have to define the functions as per

DECLARE DYNAMIC LIBRARY "mysql"

etc ...

END DECLARE

Mike

[MLambert]

Hi  Juanjogomez,

Put together the program you have sent me .... can connect ok to server and database ..

but

program hangs on "columns = mysql_num_fields(result)"

Mike

*** Still does .. I wrote my own routines

[MLambert]

Seeing that nobody replied to my queries .. I guess I used up all of my credits...

Thks for the kick off

[Juanjogomez]

Hello MLambert

Sorry but I do not connect every day to the forum.
The program that I use to connect to MYSQL is not mine, it was designed by Galeon, which I think was the one that started creating QB64. I simply use your library and your code.

I will try to answer your questions

Will DB_RESULT hold millions of transactions ??..... Yes. If you expect the result to be very large, you should do it in parts, adding the LIMIT option.

why do this "REDIM DB_RESULT(columns, rows) AS STRING".....The rows and columns are saved in DB_RESULT and it has to be resized each time a search is made, because its number varies depending on the result returned by SELECT

If I read the a row 9 to be updated ) via an index .. do I just issue an update ?? or is there another step ??.....After a SELECT, if you want to update a data, you must do an UPDATE. If you modify DB_RESULT, you are doing it only at the level of the program, not in the database.
For example:
busclave$="SELECT amount, id FROM bill WHERE client ='juanjogomez'"+chr$(0):CALL basedato(busclave$, rows, columns)
'--to change the 9 result adding up 100
dato=VAL(DB_RESULT(1,9)):dato=dato +100: dato$=LTRIM$(STR$(dato))
id$=DB_RESULT(2,9)
busclave$="UPDATE bill SET amount= '" + dato$+"' WHERE id= '"+id$+"'"+chr$(0):call basedato(busclave$,rows, columns)

Will it handle CRUD ? or will it need altering ??.... can use all CRUD operations and SLQ sentences

For "select *" can I just read a row and print it .. then go and read another row and print it etc... without having to use memory variable ??.....The result you always have to save it in a variable. With LIMIT 1 you can read only 1 data and next read the next data , but if there is not much data it is more comfortable to print the data saved in the array. That's already the SQL language

Regards

[MLambert]

Hi Juanjogomez,

Thank you for the help. I am using the same routine as yourself.

One last  question (I hope)  .. after I perform a select ... how do I get the selected data into my QB64 program .

select * from table ..

A$ = ?
B$  = ?

Assume the zip code is the 8th column..

 ZipCode$ = ??

Thanks very much for the help.

Mike

[MLambert]

Hi Juanjogomez,

DB_RESULTS is an array.

It seems that DB_RESULTS stores the data .... how can this store a million rows .. each row has 141 fields ?

By the way I am using the original code from the wiki.

Thanks,

Mike

[Juanjogomez]

Hi MLamber,
For so many data fields you have to use the LIMIT option because if you don't you will run out of memory.

[MLambert]

Thank you

Mike