• Print

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

emery

  • Hero Member
  • *****
  • Posts: 570
    • Dhpos Self Checkout module
Re: MYSQL CLIENT for QB64
« Reply #15 on: September 11, 2011, 07:55:25 AM »
that is good  ;D
Checkout my free selfcheckout module for dhpos at http://dhpos.midislandsnoblazers.ca/index.php

REYBOCATA

  • Guest
Re: MYSQL CLIENT for QB64
« Reply #16 on: September 11, 2011, 08:17:32 AM »
Yes, but how do I use?

emery

  • Hero Member
  • *****
  • Posts: 570
    • Dhpos Self Checkout module
Re: MYSQL CLIENT for QB64
« Reply #17 on: September 11, 2011, 08:27:03 AM »
oh, ok
well, i geuss i will telll you
1.
 FIND this line
    '*** Open the db ***
    'PRINT mysql_real_connect(conn, "qb64db2.db.7445102.hostedresource.com",  "qb64", "qb64db2", "table", 0, 0, 0)

qb64db2.db.7445102.hostedresource.com is your database host
qb64 is your username
qb64db2 is your password
table is your database

EDIT all of those

2.
FIND
mysql_query conn, "SELECT * FROM pizza
pizza is your database you want to read from

3.
            FOR i = 0 TO num_fields - 1
                a$ = offset_to_string(offset_at_offset(row))
print a$ '-- ADD TO MAKE IT PRINT ALL RECORDS
                            NEXT
a$ will print each record
Checkout my free selfcheckout module for dhpos at http://dhpos.midislandsnoblazers.ca/index.php

dadogiobbe

  • Newbie
  • *
  • Posts: 4
    • Email
Re: MYSQL CLIENT for QB64
« Reply #18 on: September 19, 2011, 07:46:52 AM »
hi, how can i add mysql_fetch_fields  in declarations?

 and obviously how can i fetch data from rows??
  i think the problem is that i have to fetch an array but i don't now how...
thanks
« Last Edit: September 19, 2011, 11:14:34 AM by dadogiobbe »

Galleon

  • Administrator
  • Hero Member
  • *****
  • Posts: 4664
  • QB Forever
    • Email
Re: MYSQL CLIENT for QB64
« Reply #19 on: September 20, 2011, 06:14:33 AM »
Quote
hi, how can i add mysql_fetch_fields  in declarations?
 and obviously how can i fetch data from rows??
  i think the problem is that i have to fetch an array but i don't now how...
Here's some code which uses 'mysql_fetch_fields'. I think it would be best if you posted what you are trying to do with C code, so I can help you implement it in QB64.

Code: [Select]
'MYSQL CLIENT for QB64

'Notes:
'1) Make sure you are running QB64 V0.942 or higher
'2) Download 'mysql.dll' and place it in your qb64 folder (http://www.qb64.net/mysql.dll)
'3) Create 'mysql_helper.h' in your QB64 folder (see below)
'4) Run & enjoy browsing our sample database as a member with read-only priv.

'#### mysql_helper.h (you create this file) ####
'    void *offset_to_offset(void* offset){
'    return offset;
'    }
'    void *offset_at_offset(void** offset){
'    return *offset;
'    }
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

TYPE MYSQL_FIELD_TYPE
'typedef struct st_mysql_field {
'    char *name;
    name AS _OFFSET
    '    char *org_name;
    org_name AS _OFFSET
    '    char *table;
    table AS _OFFSET
    '    char *org_table;
    org_table AS _OFFSET
    '    char *db;
    db AS _OFFSET
    '    char *catalog;
    catalog AS _OFFSET
    '    char *def;
    default AS _OFFSET
    '    unsigned long length;
    length AS _UNSIGNED _OFFSET
    '    unsigned long max_length;
    max_length AS _UNSIGNED _OFFSET
    '    unsigned int name_length;
    name_length AS _UNSIGNED LONG
    '    unsigned int org_name_length;
    org_name_length AS _UNSIGNED LONG
    '    unsigned int table_length;
    table_length AS _UNSIGNED LONG
    '    unsigned int org_table_length;
    org_table_length AS _UNSIGNED LONG
    '    unsigned int db_length;
    db_length AS _UNSIGNED LONG
    '    unsigned int catalog_length;
    catalog_length AS _UNSIGNED LONG
    '    unsigned int def_length;
    def_length AS _UNSIGNED LONG
    '    unsigned int flags;
    flags AS _UNSIGNED LONG
    '    unsigned int decimals;
    decimals AS _UNSIGNED LONG
    '    unsigned int charsetnr;
    charsetnr AS _UNSIGNED LONG
    '    enum enum_field_types type;
    type AS _UNSIGNED LONG
    '} MYSQL_FIELD;
END TYPE

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

'#### download mysql.dll from http://www.qb64.net/mysql.dll (~2MB) ####
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)
    FUNCTION mysql_fetch_fields%& (BYVAL result AS _OFFSET)
    '...
END DECLARE

DIM conn AS _OFFSET

PRINT "MYSQL Client: " + mysql_get_client_info$

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

'*** Open the db ***
'PRINT mysql_real_connect(conn, "qb64db2.db.7445102.hostedresource.com", "", "", "qb64db2", 0, 0, 0)
PRINT mysql_real_connect(conn, "qb64db2.db.7445102.hostedresource.com", "qb64guest", "QB64forever", "qb64db2", 0, 0, 0)

'*** Write to the db (not possible as a guest!) ***
GOTO skip_write '(guests can't do this anyway)
mysql_query conn, "CREATE TABLE writers(name VARCHAR(25))"
mysql_query conn, "INSERT INTO writers VALUES('Leo Tolstoy')"
mysql_query conn, "INSERT INTO writers VALUES('Jack London')"
mysql_query conn, "INSERT INTO writers VALUES('Honore de Balzac')"
mysql_query conn, "INSERT INTO writers VALUES('Lion Feuchtwanger')"
mysql_query conn, "INSERT INTO writers VALUES('Emile Zola')"
skip_write:

'*** Read from the db ***
mysql_query conn, "SELECT * FROM writers"
DIM result AS _OFFSET
result = mysql_store_result(conn)
DIM num_fields AS LONG
num_fields = mysql_num_fields(result)

f%& = mysql_fetch_fields(result)

DIM ft AS MYSQL_FIELD_TYPE
OFFSETtoMYSQL_FIELD ft, f%&, LEN(ft)
PRINT offset_to_string(ft.name)
PRINT offset_to_string(ft.org_name)
x%& = ft.length: PRINT x%& '***PRINT ft.length doesn't work in QB64 yet, this needs to be addressed***

END

DIM row AS _OFFSET
DO
    row = mysql_fetch_row(result)
    IF row THEN
        FOR i = 0 TO num_fields - 1
            PRINT offset_to_string(offset_at_offset(row))
        NEXT
    END IF
LOOP UNTIL row = 0
mysql_free_result result

'*** Close the db ***
mysql_close conn
« Last Edit: September 20, 2011, 06:20:26 AM by Galleon »
Something old... Something new... Something borrowed... Something blue...

dadogiobbe

  • Newbie
  • *
  • Posts: 4
    • Email
Re: MYSQL CLIENT for QB64
« Reply #20 on: September 28, 2011, 05:00:36 AM »
thank you galleon...perfect for mysql_field_type...
another question...how can i fetch data from rows?

probably i have to declare a new TYPE with the structure of the table...
i have to fetch name, surname and telephone N. from a table...

thank you.

emery

  • Hero Member
  • *****
  • Posts: 570
    • Dhpos Self Checkout module
Re: MYSQL CLIENT for QB64
« Reply #21 on: October 18, 2011, 09:23:42 PM »
I need some help with this, it will only get the first feild in the table ... PLZ HELPPPPPPPPPPPPPPPPP  :o
Checkout my free selfcheckout module for dhpos at http://dhpos.midislandsnoblazers.ca/index.php

Kyledub213

  • Full Member
  • ***
  • Posts: 199
    • Email
Re: MYSQL CLIENT for QB64
« Reply #22 on: November 19, 2011, 04:49:20 PM »
Thanks for the library! It works perfectly, of course.

I used it to make a high score function for my a Game I made.

Check it out! http://www.mediafire.com/file/nex7849v2ve8a3g/Slither.zip

I'm not sure how long the database will allow this to work... it's from freesql.org and there is limited # of connections... So, I probably will get shut down soon.

Any chance you have any sql space I could use? ;) wishful thinking.

Thanks for everything!
~ It's easier to ask for forgiveness than it is to get permission ~

brealmsys

  • Guest
Re: MYSQL CLIENT for QB64
« Reply #23 on: January 06, 2012, 11:01:37 AM »
This is some genius stuff. QB64 rocks!

EricR

  • Jr. Member
  • **
  • Posts: 68
  • Loading Humor.sys on boot should be mandatory
    • Email
Re: MYSQL CLIENT for QB64
« Reply #24 on: January 24, 2012, 02:06:49 PM »
Thanks Galleon  

I am working on creating a new database for a non profit I am involved with.  MySQL is the db back end.  This dll will make it much easier for me to create a parser going from the old format via CSV to SQL in MySQL.  I may even try putting direct SQL commands into a modified QB64 program and create the entire SQL db via a QB64 program.  QB64 does have Shell or something close so this may be possible.







« Last Edit: January 24, 2012, 02:13:53 PM by EricR »

emery

  • Hero Member
  • *****
  • Posts: 570
    • Dhpos Self Checkout module
Re: MYSQL CLIENT for QB64
« Reply #25 on: February 25, 2012, 03:45:46 PM »
I have this
Quote
mysql_query conn, "UPDATE genconfig SET sname='" + $sn + "'"   

it isn't working, how do i fix it?

 
Checkout my free selfcheckout module for dhpos at http://dhpos.midislandsnoblazers.ca/index.php

Catclaw

  • Jr. Member
  • **
  • Posts: 71
    • Major Media & Entertainment
    • Email
Re: MYSQL CLIENT for QB64
« Reply #26 on: March 21, 2012, 06:03:48 PM »
Removed post since new code was too long to be posted.
« Last Edit: March 26, 2012, 07:32:14 AM by Catclaw »
“Discovery consists in seeing what everyone else has seen and thinking what no one else has thought.”  --Albert Szent-Gyorgyi

Lucid

  • Full Member
  • ***
  • Posts: 132
Re: MYSQL CLIENT for QB64
« Reply #27 on: April 06, 2012, 02:48:55 PM »
Wow...just wow! :D
squirrel-monkey.com
freedutch.nl
igodu.org

cbrooks999

  • Newbie
  • *
  • Posts: 47
Re: MYSQL CLIENT for QB64
« Reply #28 on: August 04, 2012, 08:36:58 PM »
hi all - thought you might be  able to hep me out

when I write data (multiple coloumns) with:
    mysql_query conn, "INSERT INTO `alex`.`test` (`name`, `address`, `phone`) VALUES ('" + name$ + "', '" + address$ + "', '" + phone$ + "');"

the data stores properly (seen in sql editor)

but when i read it back, I ONLY get the first coloumn (name)
code reading back is:

Code: [Select]

result = mysql_store_result(conn)
num_fields = mysql_num_fields(result)
DO
    row = mysql_fetch_row(result)
    'PRINT row: INPUT z$
    IF row THEN
        FOR i = 0 TO num_fields - 1
            LOCATE 5, 1
            t = t + 1
            'PRINT t
            PRINT offset_to_string(offset_at_offset(row))
            ' INPUT z$
        NEXT
    END IF
LOOP UNTIL row = 0
mysql_free_result result


Can anyone help?? 

emery

  • Hero Member
  • *****
  • Posts: 570
    • Dhpos Self Checkout module
Re: MYSQL CLIENT for QB64
« Reply #29 on: August 04, 2012, 10:26:27 PM »
i have the same problem
Checkout my free selfcheckout module for dhpos at http://dhpos.midislandsnoblazers.ca/index.php

  • Print