Tweet
I have been trying to use MYSQLINI with this program below. The mysql.ini file doesn't show an example to UPDATE a record and I am at a loss how to display my data except when I use ODBC4ALL, which is a program I purchased years ago. I have been using the MYSQL 8.0 server and/or the MYSQL 5.7server. The program will go to completion but my problem is at the MSGBOX "data" part of the program..
#COMPILE EXE
#INCLUDE "MySQL.inc" ' header file - declares for MySQL
FUNCTION PBMAIN() AS LONG
DIM rs AS LONG
LOCAL i AS LONG, nError AS LONG
LOCAL sHost AS STRING, sUser AS STRING, sPassword AS STRING, sTemp AS STRING
LOCAL sTableName AS STRING, sOrderField AS STRING
LOCAL sDbname AS ASCIIZ*255, selectdb AS ASCIIZ*255, selecttb AS ASCIIZ*255
LOCAL nField AS LONG
sHost = "localhost"
sUser = "djthain"
sPassword = "[email protected]"
sDbname = "testdb5"
sTableName = "table5"
'***********************************************************
'Init System
IF mysql_init() THEN
MSGBOX mySQL_Error(),16,"[Error] mysql_init"
EXIT FUNCTION
END IF
MSGBOX "init"
'***********************************************************
'Setup Options
IF mysql_options( %MYSQL_OPT_LOCAL_INFILE, "local-infile=1" ) THEN
MSGBOX mySQL_Error(),16,"[Error] mysql_options"
EXIT FUNCTION
END IF
MSGBOX "options"
'***********************************************************
'Connect to Server
IF mySQL_Connect( sHost, sUser, sPassword, 0 ) THEN '-- Connect to mySQL
MSGBOX mySQL_Error(),16,"[Error] mySQL_Connect"
EXIT FUNCTION
END IF
MSGBOX "connect"
'***********************************************************
'Create Database
' selectdb = "create database "+sdbname
' IF mySQL_Query( selectdb ) THEN '-- create DB
' MSGBOX mySQL_Error(),16,"[Error] mySQL_query"
' EXIT FUNCTION
' END IF
MSGBOX "create db"
'***********************************************************
'Select the Database
IF mySQL_Select_Db( sDbname ) THEN '-- Select DB
MSGBOX mySQL_Error(),16,"[Error] mySQL_Select_Db"
EXIT FUNCTION
END IF
MSGBOX "select DB"
'~~~ Create a table in the current database ******************
' selecttb = "CREATE TABLE "+sTableName+ "(" + _
' "pers_id int unsigned auto_increment primary key," + _
' "pers_name varchar(50)," + _
' "pers_phone varchar(25) )"
'
' mySQL_query(selecttb)
' IF mySQL_errno(selecttb) <> 0 THEN EXIT FUNCTION
MSGBOX "create table"
'*************************************************************
' Insert a record
' mysql_query("INSERT INTO "+ stablename + " (pers_name,pers_phone) " + " VALUES ('Dickinson, Don','555-555-2345')+")"
mySQL_query "INSERT INTO " + stablename +_
" (pers_name,pers_phone) " + _
"VALUES ('Dickinson, Don','555-555-234c')"
MSGBOX "insert record"
'**************************************************************
' Select records from the table
IF mySQL_Query( "select * from " + sTableName ) THEN
MSGBOX mySQL_Error(),16,"[Error] mySQL_Query"
EXIT FUNCTION
END IF
MSGBOX "select records"
'**************************************************************
'Get records to display on screen
ptresult = mysql_use_result()
'
' DO
mySQL_query "mysql_fetch_row()"
MSGBOX "get rows"
mysql_query "mySQL_data(1)"
MSGBOX "data"
' MSGBOX mySQL_data(record,1)
' '- before going to the next record we need to
' ' free the memory for this record.
' '
mySQL_Free_Result()
' LOOP
'
' msgbox "end loop"
'***************************************************************
'End of process
' IF mySQL_Query( "LOAD DATA LOCAL INFILE 'C:/server/some.txt' INTO TABLE1 " + sTableName ) THEN
' MSGBOX mySQL_Error(),16,"[Error] mySQL_Query"
' EXIT FUNCTION
' END IF
MSGBOX mysql_info,64,"Success"
CALL mySQL_Close
END FUNCTION
#COMPILE EXE
#INCLUDE "MySQL.inc" ' header file - declares for MySQL
FUNCTION PBMAIN() AS LONG
DIM rs AS LONG
LOCAL i AS LONG, nError AS LONG
LOCAL sHost AS STRING, sUser AS STRING, sPassword AS STRING, sTemp AS STRING
LOCAL sTableName AS STRING, sOrderField AS STRING
LOCAL sDbname AS ASCIIZ*255, selectdb AS ASCIIZ*255, selecttb AS ASCIIZ*255
LOCAL nField AS LONG
sHost = "localhost"
sUser = "djthain"
sPassword = "[email protected]"
sDbname = "testdb5"
sTableName = "table5"
'***********************************************************
'Init System
IF mysql_init() THEN
MSGBOX mySQL_Error(),16,"[Error] mysql_init"
EXIT FUNCTION
END IF
MSGBOX "init"
'***********************************************************
'Setup Options
IF mysql_options( %MYSQL_OPT_LOCAL_INFILE, "local-infile=1" ) THEN
MSGBOX mySQL_Error(),16,"[Error] mysql_options"
EXIT FUNCTION
END IF
MSGBOX "options"
'***********************************************************
'Connect to Server
IF mySQL_Connect( sHost, sUser, sPassword, 0 ) THEN '-- Connect to mySQL
MSGBOX mySQL_Error(),16,"[Error] mySQL_Connect"
EXIT FUNCTION
END IF
MSGBOX "connect"
'***********************************************************
'Create Database
' selectdb = "create database "+sdbname
' IF mySQL_Query( selectdb ) THEN '-- create DB
' MSGBOX mySQL_Error(),16,"[Error] mySQL_query"
' EXIT FUNCTION
' END IF
MSGBOX "create db"
'***********************************************************
'Select the Database
IF mySQL_Select_Db( sDbname ) THEN '-- Select DB
MSGBOX mySQL_Error(),16,"[Error] mySQL_Select_Db"
EXIT FUNCTION
END IF
MSGBOX "select DB"
'~~~ Create a table in the current database ******************
' selecttb = "CREATE TABLE "+sTableName+ "(" + _
' "pers_id int unsigned auto_increment primary key," + _
' "pers_name varchar(50)," + _
' "pers_phone varchar(25) )"
'
' mySQL_query(selecttb)
' IF mySQL_errno(selecttb) <> 0 THEN EXIT FUNCTION
MSGBOX "create table"
'*************************************************************
' Insert a record
' mysql_query("INSERT INTO "+ stablename + " (pers_name,pers_phone) " + " VALUES ('Dickinson, Don','555-555-2345')+")"
mySQL_query "INSERT INTO " + stablename +_
" (pers_name,pers_phone) " + _
"VALUES ('Dickinson, Don','555-555-234c')"
MSGBOX "insert record"
'**************************************************************
' Select records from the table
IF mySQL_Query( "select * from " + sTableName ) THEN
MSGBOX mySQL_Error(),16,"[Error] mySQL_Query"
EXIT FUNCTION
END IF
MSGBOX "select records"
'**************************************************************
'Get records to display on screen
ptresult = mysql_use_result()
'
' DO
mySQL_query "mysql_fetch_row()"
MSGBOX "get rows"
mysql_query "mySQL_data(1)"
MSGBOX "data"
' MSGBOX mySQL_data(record,1)
' '- before going to the next record we need to
' ' free the memory for this record.
' '
mySQL_Free_Result()
' LOOP
'
' msgbox "end loop"
'***************************************************************
'End of process
' IF mySQL_Query( "LOAD DATA LOCAL INFILE 'C:/server/some.txt' INTO TABLE1 " + sTableName ) THEN
' MSGBOX mySQL_Error(),16,"[Error] mySQL_Query"
' EXIT FUNCTION
' END IF
MSGBOX mysql_info,64,"Success"
CALL mySQL_Close
END FUNCTION
Comment