• Print

Author Topic: MYSQL CLIENT for QB64  (Read 2781 times)

cbrooks999

  • Newbie
  • *
  • Posts: 47
Re: MYSQL CLIENT for QB64
« Reply #30 on: August 05, 2012, 06:02:52 AM »
I wish Galleon would respond - he's a fantastic programmer.

He should be able to at least tell us if it's possible or not.

Galleon

  • Administrator
  • Hero Member
  • *****
  • Posts: 4664
  • QB Forever
    • Email
Re: MYSQL CLIENT for QB64
« Reply #31 on: August 06, 2012, 01:20:17 PM »
Thanks. I'm sure what you want to do is possible, maybe I'll get round to looking at your code later.
Something old... Something new... Something borrowed... Something blue...

cbrooks999

  • Newbie
  • *
  • Posts: 47
Re: MYSQL CLIENT for QB64
« Reply #32 on: August 06, 2012, 01:22:09 PM »
Thanks Galleon.  I deeply appreciate any help.  I hope it will be a quick one for you.

dadogiobbe

  • Newbie
  • *
  • Posts: 4
    • Email
Re: MYSQL CLIENT for QB64
« Reply #33 on: December 12, 2012, 02:15:55 AM »
it's not so elegant...but it works...if you need more than 30 fields, it'r very simple to modify...
 :) :) :)


TYPE FIELDSET
    f0 AS _OFFSET
    f1 AS _OFFSET
    f2 AS _OFFSET
    f3 AS _OFFSET
    f4 AS _OFFSET
    f5 AS _OFFSET
    f6 AS _OFFSET
    f7 AS _OFFSET
    f8 AS _OFFSET
    f9 AS _OFFSET
    f10 AS _OFFSET
    f11 AS _OFFSET
    f12 AS _OFFSET
    f13 AS _OFFSET
    f14 AS _OFFSET
    f15 AS _OFFSET
    f16 AS _OFFSET
    f17 AS _OFFSET
    f18 AS _OFFSET
    f19 AS _OFFSET
    f20 AS _OFFSET
    f21 AS _OFFSET
    f22 AS _OFFSET
    f23 AS _OFFSET
    f24 AS _OFFSET
    f25 AS _OFFSET
    f26 AS _OFFSET
    f27 AS _OFFSET
    f28 AS _OFFSET
    f29 AS _OFFSET
    f30 AS _OFFSET
END TYPE

DECLARE CUSTOMTYPE LIBRARY
    SUB OFFSETtoMYSQL_FIELD ALIAS memcpy (dest AS MYSQL_FIELD_TYPE, BYVAL source AS _OFFSET, BYVAL bytes AS LONG)
    SUB OFFSETtoFIELDSET ALIAS memcpy (dest AS FIELDSET, BYVAL source AS _OFFSET, BYVAL bytes AS LONG)
END DECLARE

...
...
'*** Read from the db ***
DIM SHARED result AS _OFFSET
DIM SHARED num_fields AS LONG
DIM SHARED num_rows AS LONG
DIM ROW AS _OFFSET
DIM FLD AS _OFFSET
DIM ft AS MYSQL_FIELD_TYPE
DIM SHARED NAME_FIELDS(100) AS STRING

mysql_query conn, "SELECT * FROM PAZIENTS where SURNAME = 'Doe'"

result = mysql_store_result(conn)

num_fields = mysql_num_fields(result)
num_rows = mysql_num_rows(result)

FOR XX = 0 TO num_fields - 1
    f%& = mysql_fetch_field(result)
    OFFSETtoMYSQL_FIELD ft, f%&, LEN(ft)
    NAME_FIELDS(XX) = offset_to_string(ft.name)
NEXT XX


DO
    ROW = mysql_fetch_row(result)
    IF ROW THEN
        FOR c = 1 TO 30
            PRINT c, FROM_FIELDSET_NUM(ROW, c); "  "
        NEXT c
        PRINT
        '  PRINT FROM_FIELDSET_NAME(ROW, "Surname")
        '  PRINT FROM_FIELDSET_NAME(ROW, "Name")
        '  PRINT FROM_FIELDSET_NAME(ROW, "Address")

    END IF
LOOP UNTIL ROW = 0


mysql_free_result result

'*** Close the db ***
mysql_close conn


FUNCTION FROM_FIELDSET_NAME$ (ROW AS _OFFSET, NAME$)

FOR XX = 1 TO num_fields - 1
    IF UCASE$(NAME$) = UCASE$(NAME_FIELDS(XX)) THEN EXIT FOR
NEXT XX



DIM VAR AS FIELDSET
OFFSETtoFIELDSET VAR, ROW, LEN(VAR)
SELECT CASE XX
    CASE IS = 0
        A$ = offset_to_string(VAR.f0)
    CASE IS = 1
        A$ = offset_to_string(VAR.f1)
    CASE IS = 2
        A$ = offset_to_string(VAR.f2)
    CASE IS = 3
        A$ = offset_to_string(VAR.f3)
    CASE IS = 4
        A$ = offset_to_string(VAR.f4)
    CASE IS = 5
        A$ = offset_to_string(VAR.f5)
    CASE IS = 6
        A$ = offset_to_string(VAR.f6)
    CASE IS = 7
        A$ = offset_to_string(VAR.f7)
    CASE IS = 8
        A$ = offset_to_string(VAR.f8)
    CASE IS = 9
        A$ = offset_to_string(VAR.f9)
    CASE IS = 10
        A$ = offset_to_string(VAR.f10)
    CASE IS = 11
        A$ = offset_to_string(VAR.f11)
    CASE IS = 12
        A$ = offset_to_string(VAR.f12)
    CASE IS = 13
        A$ = offset_to_string(VAR.f13)
    CASE IS = 14
        A$ = offset_to_string(VAR.f14)
    CASE IS = 15
        A$ = offset_to_string(VAR.f15)
    CASE IS = 16
        A$ = offset_to_string(VAR.f16)
    CASE IS = 17
        A$ = offset_to_string(VAR.f17)
    CASE IS = 18
        A$ = offset_to_string(VAR.f18)
    CASE IS = 19
        A$ = offset_to_string(VAR.f19)
    CASE IS = 20
        A$ = offset_to_string(VAR.f20)
    CASE IS = 21
        A$ = offset_to_string(VAR.f21)
    CASE IS = 22
        A$ = offset_to_string(VAR.f22)
    CASE IS = 23
        A$ = offset_to_string(VAR.f23)
    CASE IS = 24
        A$ = offset_to_string(VAR.f24)
    CASE IS = 25
        A$ = offset_to_string(VAR.f25)
    CASE IS = 26
        A$ = offset_to_string(VAR.f26)
    CASE IS = 27
        A$ = offset_to_string(VAR.f27)
    CASE IS = 28
        A$ = offset_to_string(VAR.f28)
    CASE IS = 29
        A$ = offset_to_string(VAR.f29)
END SELECT
FROM_FIELDSET_NAME$ = A$
END FUNCTION





FUNCTION FROM_FIELDSET_NUM$ (ROW AS _OFFSET, NUM)

DIM VAR AS FIELDSET
OFFSETtoFIELDSET VAR, ROW, LEN(VAR)
XX = NUM
SELECT CASE XX
    CASE IS = 0
        A$ = offset_to_string(VAR.f0)
    CASE IS = 1
        A$ = offset_to_string(VAR.f1)
    CASE IS = 2
        A$ = offset_to_string(VAR.f2)
    CASE IS = 3
        A$ = offset_to_string(VAR.f3)
    CASE IS = 4
        A$ = offset_to_string(VAR.f4)
    CASE IS = 5
        A$ = offset_to_string(VAR.f5)
    CASE IS = 6
        A$ = offset_to_string(VAR.f6)
    CASE IS = 7
        A$ = offset_to_string(VAR.f7)
    CASE IS = 8
        A$ = offset_to_string(VAR.f8)
    CASE IS = 9
        A$ = offset_to_string(VAR.f9)
    CASE IS = 10
        A$ = offset_to_string(VAR.f10)
    CASE IS = 11
        A$ = offset_to_string(VAR.f11)
    CASE IS = 12
        A$ = offset_to_string(VAR.f12)
    CASE IS = 13
        A$ = offset_to_string(VAR.f13)
    CASE IS = 14
        A$ = offset_to_string(VAR.f14)
    CASE IS = 15
        A$ = offset_to_string(VAR.f15)
    CASE IS = 16
        A$ = offset_to_string(VAR.f16)
    CASE IS = 17
        A$ = offset_to_string(VAR.f17)
    CASE IS = 18
        A$ = offset_to_string(VAR.f18)
    CASE IS = 19
        A$ = offset_to_string(VAR.f19)
    CASE IS = 20
        A$ = offset_to_string(VAR.f20)
    CASE IS = 21
        A$ = offset_to_string(VAR.f21)
    CASE IS = 22
        A$ = offset_to_string(VAR.f22)
    CASE IS = 23
        A$ = offset_to_string(VAR.f23)
    CASE IS = 24
        A$ = offset_to_string(VAR.f24)
    CASE IS = 25
        A$ = offset_to_string(VAR.f25)
    CASE IS = 26
        A$ = offset_to_string(VAR.f26)
    CASE IS = 27
        A$ = offset_to_string(VAR.f27)
    CASE IS = 28
        A$ = offset_to_string(VAR.f28)
    CASE IS = 29
        A$ = offset_to_string(VAR.f29)
END SELECT
FROM_FIELDSET_NUM$ = A$
END FUNCTION

cbrooks999

  • Newbie
  • *
  • Posts: 47
Re: MYSQL CLIENT for QB64
« Reply #34 on: December 14, 2012, 11:41:53 AM »
I can't wait to try it!  I am getting a couple errors in the code and I don't know enough about these specific commands to decipher exactly what the error is.  The version of QB I am running is .954 and I have the mysql library.  Can you please help me resolve the error?

 SUB OFFSETtoMYSQL_FIELD ALIAS memcpy (dest AS MYSQL_FIELD_TYPE, BYVAL source AS _OFFSET, BYVAL bytes AS LONG)
Illegal sub/function parameter

DIM ft AS MYSQL_FIELD_TYPE
unknown type (I assume due to the line above not being present)

mysql_query conn, "SELECT * FROM PAZIENTS where SURNAME = 'Doe'"
Syntax error


dadogiobbe

  • Newbie
  • *
  • Posts: 4
    • Email
Re: MYSQL CLIENT for QB64
« Reply #35 on: December 17, 2012, 03:38:31 AM »
you have to insert these new function in the last galleon source....
this is not a complete software...i posted only functions to parse the fields...
 :)

cbrooks999

  • Newbie
  • *
  • Posts: 47
Re: MYSQL CLIENT for QB64
« Reply #36 on: December 26, 2012, 11:29:45 AM »
I've tried and tried to get this code together but I'm getting errors throughout the code. (syntax etc)  Galleon's original code works fine - but I can't get the new code inserted.

I'm still getting:  SUB OFFSETtoMYSQL_FIELD ALIAS memcpy (dest AS MYSQL_FIELD_TYPE, BYVAL source AS _OFFSET, BYVAL bytes AS LONG)
Illegal sub/function parameter

as well as others.

Is there any chance you can paste up the complete code?

Galleon

  • Administrator
  • Hero Member
  • *****
  • Posts: 4664
  • QB Forever
    • Email
Re: MYSQL CLIENT for QB64
« Reply #37 on: December 30, 2012, 04:52:13 PM »
Just posting an Email I received...

Quote
Regarding only fetching first field of data
    row = mysql_fetch_row(result)
    IF row THEN
        FOR i = 0 TO num_fields - 1
            a$ = offset_to_string(offset_at_offset(row))
            PRINT a$,
        NEXT
        PRINT
    END IF

I'll be up-skilling myself on databases soon. I'll be able to help a lot more with this soon.
Something old... Something new... Something borrowed... Something blue...

cbrooks999

  • Newbie
  • *
  • Posts: 47
Re: MYSQL CLIENT for QB64
« Reply #38 on: December 30, 2012, 05:24:37 PM »
Is there any way someone could help me add in the prior recommendation (couple posts back) into this code (4 column) and get it to work?  I have yet to be able to get the prior post to work

Code: [Select]
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_get_client_info$
    FUNCTION mysql_init%& (BYVAL x AS LONG)
    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)
    SUB mysql_close (BYVAL mysql AS _OFFSET)
    SUB mysql_query (BYVAL mysql AS _OFFSET, what AS STRING)
    FUNCTION mysql_store_result%& (BYVAL mysql AS _OFFSET)
    FUNCTION mysql_num_fields& (BYVAL result AS _OFFSET)
    FUNCTION mysql_fetch_row%& (BYVAL result AS _OFFSET)
    SUB mysql_free_result (BYVAL result AS _OFFSET)
    '...
END DECLARE
DIM result AS _OFFSET
DIM num_fields AS LONG
DIM row AS _OFFSET


DIM conn AS _OFFSET
conn = mysql_init(0)
IF conn = 0 THEN PRINT "Could not init MYSQL client!": END

PRINT mysql_real_connect(conn, "127.0.0.1", "USERNAME", "PASSWORD", "test2", 0, 0, 0)
INPUT "Do you need to create the table? [y/N] ", createtable$
createtable$ = LCASE$(createtable$)
IF createtable$ = "y" THEN
    mysql_query conn, "CREATE TABLE test(id int NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), name VARCHAR(25), address VARCHAR(116), phone VARCHAR(15));"
END IF


DO
    INPUT "Add a row of data? [y/N]", addrow$ '' At least in the linux world, the capital just means it's the default option, so the next line does an LCASE$() just in case (I guess that is sort of a joke *shrug*)
    addrow$ = LCASE$(addrow$)
    IF (addrow$ = "") OR (addrow$ = "n") THEN EXIT DO
    INPUT "Name:      ", name$
    INPUT "Address:   ", address$
    INPUT "Phone:     ", phone$


    mysql_query conn, "INSERT INTO `test2`.`test` (`name`, `address`, `phone`) VALUES ('" + name$ + "', '" + address$ + "', '" + phone$ + "');"

LOOP


mysql_query conn, "SELECT * FROM test;"

result = mysql_store_result(conn)

num_fields = mysql_num_fields(result)

DO
    row = mysql_fetch_row(result)
    IF row THEN
        FOR i = 0 TO num_fields - 1
            PRINT offset_to_string(offset_at_offset(row)), '' Comma keeps us on the same line
        NEXT
        PRINT '' Move to next text line for next row
    END IF
LOOP UNTIL row = 0
mysql_free_result result

mysql_close conn



Galleon

  • Administrator
  • Hero Member
  • *****
  • Posts: 4664
  • QB Forever
    • Email
Re: MYSQL CLIENT for QB64
« Reply #39 on: January 01, 2013, 12:04:43 AM »
Quote
Is there any way someone could help me add in the prior recommendation (couple posts back) into this code (4 column) and get it to work?  I have yet to be able to get the prior post to work
I'm on it...
Something old... Something new... Something borrowed... Something blue...

  • Print