Announcement

Collapse
No announcement yet.

mysql

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • mysql

    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
    djthain

  • #2
    Code:
    ' '- before going to the next record we need to
    ' ' free the memory for this record.
    ' '
    mySQL_Free_Result()
    Free_Result clears the result set, not the row.
    You will never see more than the first record

    This is not right at all:
    Code:
    mySQL_query "mysql_fetch_row()"
    It should be more like
    Code:
    row = mySQL_fetch_row
    Msgbox Exrtract(row,1)
    I don't think that "Extract" can just be PB's PARSE$. I don't use mySQL this way so I'm not sure how the return value will look in PB. I do know that mySQL_fetch_row returns an array of values.

    From the mySL documentation:
    "mySQL ROW: This is a type-safe representation of one row of data. It is currently implemented as an array of counted byte strings. (You cannot treat these as null-terminated strings if field values may contain binary data, because such values may contain null bytes internally.) Rows are obtained by calling"

    I suspect you need to do something like
    Code:
    NoOfFields = mySQL_num_fields(ptresult)
    ...
    row = mySQL_fetch_row(ptresult)
    REDIM flds(1 to NoOfFields) as string at strPtr(row)
    Msgbox flds(1)

    Apparently the field values arre nulterminated, so a simple PARSE$ will work.

    Comment


    • #3
      If you're using the headers from here https://forum.powerbasic.com/forum/u...ders#post28754

      it turns out to be a lot simpler since the header file does the parsing for you
      Code:
       DO
           IF mysql_fetch_row() = 0 THEN EXIT DO
           MSGBOX  mySQL_data(1)
      LOOP
      mySQL_Free_Result()

      Comment


      • #4
        Here is an example of my code. The ADD section and the Update section are working except for the mySql_data() call. The NEXT & PREV & DELETE have been commented out with #if 0/#endif because I haven't had time to troubleshoot those sections. The mySql_data() stops working after about the mySql_data(5). It gets chopped off after that field. I know the ADD section works because I use ODBC4ALL to see my true results and each field is complete. If I use the UPDATE section in my program the fields are chopped off after about the 5th field. I use the mysql.ini that is found in the forums. I have been using the 5.7 server. I have been using the 8.0 server on another computer with the same results.

        Create Database "bookkeepint and table addrs"

        Code:
        'programData\mysql\mysql server 5.7\data is where database is found
        
        #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,datarow AS LONG
        LOCAL sDbname AS ASCIIZ*255, selectdb AS ASCIIZ*255, selecttb AS ASCIIZ*1255
        LOCAL nField AS LONG,ptfield AS LONG,numrows AS LONG
        
        sHost = "localhost"
        sUser = "djthain1"
        sPassword = "[email protected]"
        
        sDbname = "bookkeeping2"
        
        sTableName = "addrs"
        
        '***********************************************************
        'Init System
        
        IF mysql_init() THEN
        MSGBOX mySQL_Error(),16,"[Error] mysql_init"
        EXIT FUNCTION
        END IF
        
        '***********************************************************
        'Setup Options
        
        IF mysql_options( %MYSQL_OPT_LOCAL_INFILE, "local-infile=1" ) THEN
        MSGBOX mySQL_Error(),16,"[Error] mysql_options"
        EXIT FUNCTION
        END IF
        
        '***********************************************************
        '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
        
        '*********************************************************** first run only then comment out ''
        'Create Database
        
        selectdb = "create database "+sdbname
        IF mySQL_Query( selectdb ) THEN '-- create DB
        MSGBOX mySQL_Error(),16,"[Error] mySQL_query"
        EXIT FUNCTION
        END IF
        
        '***********************************************************
        'Select the Database
        
        IF mySQL_Select_Db( sDbname ) THEN '-- Select DB
        MSGBOX mySQL_Error(),16,"[Error] mySQL_Select_Db"
        EXIT FUNCTION
        END IF
        
        '~~~ Create a table in the current database ****************** first run only then comment out
        selecttb = "CREATE TABLE "+sTableName+ "(" + _
        "rcdno int unsigned auto_increment primary key," + _
        "primkey varchar(70)," + _
        "lastname varchar(35)," + _
        "firstname varchar(35)," + _
        "addr1 varchar(35)," + _
        "addr2 varchar(35)," + _
        "cityst varchar(35)," + _
        "zip varchar(35)," + _
        "phone1 varchar(35)," + _
        "phone2 varchar(35)," + _
        "email1 varchar(45)," + _
        "email2 varchar(45)," + _
        "chk varchar(35) )"
        
        mySQL_query(selecttb)
        
        mySQL_Free_Result()
        
        '***************************************************************
        
        MSGBOX mysql_info,,"Success"
        
        CALL mySQL_Close
        
        END FUNCTION
        My program to update file addrs in database bookkeeping

        Code:
        'Program ad002.bas
        'Date 05/07/20
        'Update Address File without .rc file
        
        #COMPILE EXE
        '#CONSOLE OFF 'pbcc only
        '#DIM ALL
        '%ccwin=1 'pbcc only
        
        #INCLUDE "WIN32API.INC"
        #INCLUDE "MySQL.inc" ' header file - declares for MySQL
        
        GLOBAL flaga%
        GLOBAL flena1$
        GLOBAL infofle$
        GLOBAL varble$()
        GLOBAL kynext AS STRING
        GLOBAL ky AS STRING
        GLOBAL rcdnumber AS LONG
        GLOBAL hdlg AS LONG
        
        GLOBAL varble$()
        GLOBAL primkey$()
        GLOBAL lastname$()
        GLOBAL firstname$()
        GLOBAL addr1$()
        GLOBAL addr2$()
        GLOBAL cityst$()
        GLOBAL zip$()
        GLOBAL ky AS STRING
        GLOBAL rcdnumber AS LONG
        GLOBAL hdlg AS LONG
        GLOBAL sTableName AS STRING
        
        
        DECLARE FUNCTION DialogBox (BYVAL hCurInstance AS LONG, lpTemplateName AS ASCIIZ, BYVAL hWndParent AS LONG, BYVAL lpDialogFunc AS LONG) AS LONG
        
        %snaddr=1
        %ID_text1 = 112
        %addrtn = 124
        %updatertn = 125
        %prevrtn = 128
        %nextrtn = 127
        %deletertn = 126
        %exitrtn = 129
        %getkey = 130
        %ID_LST1 = 131
        
        '------------------------------------------------------------------------------
        
        FUNCTION WINMAIN (BYVAL hCurInstance AS LONG, _ 'Not PBMain since
        BYVAL hPrevInstance AS LONG, _ 'hCurInstance is needed
        BYVAL lpszCmdLine AS ASCIIZ PTR, _
        BYVAL nCmdShow AS LONG ) EXPORT AS LONG
        DEFLNG a-z
        
        ' DIM i%
        
        DIM varble$(12)
        DIM primkey$(1)
        DIM lastname$(1)
        DIM firstname$(1)
        DIM addr1$(1)
        DIM addr2$(1)
        DIM cityst$(1)
        DIM zip$(1)
        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 sOrderField AS STRING,datarow AS LONG
        LOCAL sDbname AS ASCIIZ*255, selectdb AS ASCIIZ*255, selecttb AS ASCIIZ*255
        LOCAL nField AS LONG,ptfield AS LONG,numrows AS LONG,resrows AS LONG
        
        sHost = "localhost"
        sUser = "djthain1"
        sPassword = "[email protected]"
        
        sDbname = "bookkeeping2"
        
        sTableName = "addrs"
        
        '***********************************************************
        'Init System
        
        IF mysql_init() THEN
        MSGBOX mySQL_Error(),16,"[Error] mysql_init"
        EXIT FUNCTION
        END IF
        
        '***********************************************************
        'Setup Options
        
        IF mysql_options( %MYSQL_OPT_LOCAL_INFILE, "local-infile=1" ) THEN
        MSGBOX mySQL_Error(),16,"[Error] mysql_options"
        EXIT FUNCTION
        END IF
        
        '***********************************************************
        '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
        
        
        '***********************************************************
        'Select the Database
        
        IF mySQL_Select_Db( sDbname ) THEN '-- Select DB
        MSGBOX mySQL_Error(),16,"[Error] mySQL_Select_Db"
        EXIT FUNCTION
        END IF
        
        
        
        LOCAL windowtitle AS ASCIIZ * 256
        
        flaga=0
        
        
        OPEN "ad002b.dss" FOR INPUT AS 1
        INPUT #1, apsn&,bpsn&
        CLOSE 1
        
        
        
        ''' numrcds$ = slselstr("select count(*) from addrs","E2")
        ' rcdnumber=VAL(numrcds$)
        
        IF mySQL_Query( "select * from " + sTableName ) THEN
        MSGBOX mySQL_Error(),16,"[Error] mySQL_Query"
        EXIT FUNCTION
        END IF
        
        ResRows = mySQL_Num_Rows
        rcdnumber=resrows
        
        REDIM primkey$(rcdnumber)
        REDIM lastname$(rcdnumber)
        REDIM firstname$(rcdnumber)
        REDIM addr1$(rcdnumber)
        REDIM addr2$(rcdnumber)
        REDIM cityst$(rcdnumber)
        REDIM zip$(rcdnumber)
        
        
        
        ' sTemp = sTemp + STR$(i) + " " + mySQL_Data(1) + " " + mySQL_Data(2)+ " " + mySQL_Data(3) + $CRLF
        DO WHILE ( mysql_fetch_row() )
        
        primekey$ =mySQL_Data(2) '"primkey",%snaddr)
        lstname$ =mySQL_Data(3) '"lastname",%snaddr)
        fstname$ =mySQL_Data(4) '"firstname",%snaddr)
        addrs1$ =mySQL_Data(5) '"addr1",%snaddr)
        addrs2$ =mySQL_Data(6) '"addr2",%snaddr)
        ctyst$ =mySQL_Data(7) '"cityst",%snaddr)
        zp$ =mySQL_Data(8) '"zip",%snaddr)
        
        INCR y&
        lstname$=RTRIM$(lstname$)
        ARRAY INSERT primkey$(y&), primekey$
        ARRAY INSERT lastname$(y&), lstname$
        ARRAY INSERT firstname$(y&), fstname$
        ARRAY INSERT addr1$(y&), addrs1$
        ARRAY INSERT addr2$(y&), addrs2$
        ARRAY INSERT cityst$(y&), ctyst$
        ARRAY INSERT zip$(y&), zp$
        
        LOOP
        
        
        mySQL_Free_Result()
        
        
        ' FONT NEW "Arial", 10, 1 TO hBoldFont
        FONT NEW "Arial",15,1,0 TO fHndl
        FONT NEW "Arial2",10,1,0 TO fHndl2
        
        DIALOG NEW 0, " Address Updates Database "+sdbname,apsn& ,bpsn& , 541, 400, _
        %WS_POPUP OR %WS_BORDER OR %WS_DLGFRAME OR %WS_CAPTION OR _
        %WS_SYSMENU OR %WS_MINIMIZEBOX OR %WS_CLIPSIBLINGS OR %WS_VISIBLE _
        OR %DS_MODALFRAME OR %DS_3DLOOK OR %DS_NOFAILCREATE OR %DS_SETFONT, _
        %WS_EX_CONTROLPARENT OR %WS_EX_LEFT OR %WS_EX_LTRREADING OR _
        %WS_EX_RIGHTSCROLLBAR, TO hDlg
        
        ' FONT NEW "Testfont",10,4,1,16,10 TO fHndl
        
        CONTROL ADD LABEL, hDlg,132, " Address Data Input", 175,13,165,20, SS_LEFT
        ' CONTROL SET COLOR hDlg, 132, %green, -2
        CONTROL SET COLOR hDlg,132,%RGB_RED,%RGB_BLUE
        CONTROL SET FONT hDlg, 132, fHndl
        
        CONTROL ADD LABEL, hDlg,100, "Primkey", 65, 38, 35, 11
        CONTROL SET COLOR hDlg,100,%RGB_WHITE,%RGB_BLUE
        CONTROL SET FONT hDlg, 100, fHndl2
        
        CONTROL ADD LABEL, hDlg,101, "Last Name", 65, 57, 49, 11
        CONTROL SET COLOR hDlg,101,%RGB_WHITE,%RGB_BLUE
        CONTROL SET FONT hDlg, 101, fHndl2
        
        CONTROL ADD LABEL, hDlg,102, "First Name", 65, 76, 49, 10
        CONTROL SET COLOR hDlg,102,%RGB_WHITE,%RGB_BLUE
        CONTROL SET FONT hDlg, 102, fHndl2
        
        CONTROL ADD LABEL, hDlg,103, "Address 1", 65, 92, 47, 12
        CONTROL SET COLOR hDlg,103,%RGB_WHITE,%RGB_BLUE
        CONTROL SET FONT hDlg, 103, fHndl2
        
        CONTROL ADD LABEL, hDlg,104,"Address 2", 65, 109, 47, 11
        CONTROL SET COLOR hDlg,104,%RGB_WHITE,%RGB_BLUE
        CONTROL SET FONT hDlg, 104, fHndl2
        
        CONTROL ADD LABEL, hDlg,105, "City/St", 65, 127, 45, 12
        CONTROL SET COLOR hDlg,105,%RGB_WHITE,%RGB_BLUE
        CONTROL SET FONT hDlg, 105, fHndl2
        
        CONTROL ADD LABEL, hDlg,106, "Zip Code", 65, 145, 43, 10
        CONTROL SET COLOR hDlg,106,%RGB_WHITE,%RGB_BLUE
        CONTROL SET FONT hDlg, 106, fHndl2
        
        CONTROL ADD LABEL, hDlg,107, "Phone 1", 65, 161, 42, 12
        CONTROL SET COLOR hDlg,107,%RGB_WHITE,%RGB_BLUE
        CONTROL SET FONT hDlg, 107, fHndl2
        
        CONTROL ADD LABEL, hDlg,108, "Phone 2", 65, 177, 46, 12
        CONTROL SET COLOR hDlg,108,%RGB_WHITE,%RGB_BLUE
        CONTROL SET FONT hDlg, 108, fHndl2
        
        CONTROL ADD LABEL, hDlg,109, "Email 1", 65, 195, 45, 12
        CONTROL SET COLOR hDlg,109,%RGB_WHITE,%RGB_BLUE
        CONTROL SET FONT hDlg, 109, fHndl2
        
        CONTROL ADD LABEL, hDlg,110, "Email 2", 65, 214, 48, 11
        CONTROL SET COLOR hDlg,110,%RGB_WHITE,%RGB_BLUE
        CONTROL SET FONT hDlg, 110, fHndl2
        
        CONTROL ADD LABEL, hDlg,111, "Check Mark", 65, 231, 42, 11
        CONTROL SET COLOR hDlg,111,%RGB_WHITE,%RGB_BLUE
        CONTROL SET FONT hDlg, 111, fHndl2
        
        
        CONTROL ADD TEXTBOX, hDlg, 112, "", 117, 36, 248, 12
        CONTROL ADD TEXTBOX, hDlg, 113, "", 117, 55, 248, 12
        CONTROL ADD TEXTBOX, hDlg, 114, "", 117, 73, 248, 12
        CONTROL ADD TEXTBOX, hDlg, 115, "", 117, 90, 247, 13
        CONTROL ADD TEXTBOX, hDlg, 116, "", 117, 108, 248, 13
        CONTROL ADD TEXTBOX, hDlg, 117, "", 117, 127, 249, 12
        CONTROL ADD TEXTBOX, hDlg, 118, "", 117, 143, 85, 12
        CONTROL ADD TEXTBOX, hDlg, 119, "", 117, 160, 85, 12
        CONTROL ADD TEXTBOX, hDlg, 120, "", 117, 177, 85, 12
        CONTROL ADD TEXTBOX, hDlg, 121, "", 117, 194, 200, 13
        CONTROL ADD TEXTBOX, hDlg, 122, "", 117, 212, 201, 13
        CONTROL ADD TEXTBOX, hDlg, 123, "", 117, 230, 11, 12
        
        CONTROL ADD BUTTON, hDlg, 124, "&Add", 401, 30, 35, 15
        CONTROL ADD BUTTON, hDlg, 125, "&Update", 442, 30, 35, 15
        CONTROL ADD BUTTON, hDlg, 126, "&Delete", 401, 49, 35, 15
        CONTROL ADD BUTTON, hDlg, 127, "&Next", 442, 50, 35, 15
        CONTROL ADD BUTTON, hDlg, 128, "&Prev", 401, 68, 35, 15
        CONTROL ADD BUTTON, hDlg, 129, "&Exit", 442, 69, 35, 15
        CONTROL ADD BUTTON, hDlg, 130, "&Getkey", 401, 88, 35, 15
        
        CONTROL ADD LISTVIEW, hDlg, 131, "",65, 255, 409, 130, _
        %WS_CHILD OR %WS_VISIBLE OR %WS_TABSTOP OR %LVS_REPORT OR %LVS_SHOWSELALWAYS OR %LVS_SINGLESEL
        
        LISTVIEW INSERT COLUMN hDlg, %ID_LST1, 1, "Primkey",100,0
        LISTVIEW INSERT COLUMN hDlg, %ID_LST1, 2, "Last Name",100,0
        LISTVIEW INSERT COLUMN hDlg, %ID_LST1, 3, "First Name",100,0
        LISTVIEW INSERT COLUMN hDlg, %ID_LST1, 4, "Addr1",100,0
        LISTVIEW INSERT COLUMN hDlg, %ID_LST1, 5, "Addr2",100,0
        LISTVIEW INSERT COLUMN hDlg, %ID_LST1, 6, "City/St",100,0
        LISTVIEW INSERT COLUMN hDlg, %ID_LST1, 7, "Zip",100,0
        
        LISTVIEW SET STYLEXX hdlg, %ID_LST1, %LVS_EX_GRIDLINES
        
        FOR i=1 TO rcdnumber
        LISTVIEW INSERT ITEM hDlg, %ID_LST1, i, 0,primkey$(i)
        
        LISTVIEW SET TEXT hDlg, %ID_LST1, i, 2,lastname$(i)
        LISTVIEW SET TEXT hDlg, %ID_LST1, i, 3,firstname$(i)
        LISTVIEW SET TEXT hDlg, %ID_LST1, i, 4,addr1$(i)
        LISTVIEW SET TEXT hDlg, %ID_LST1, i, 5,addr2$(i)
        LISTVIEW SET TEXT hDlg, %ID_LST1, i, 6,cityst$(i)
        LISTVIEW SET TEXT hDlg, %ID_LST1, i, 7,zip$(i)
        
        NEXT i
        
        LISTVIEW SORT hDlg, %ID_LST1, 1,ASCEND
        
        DIALOG SET COLOR hdlg, -1&, %RGB_BLUE
        DIALOG SHOW MODAL hDlg, CALL DialogProc
        ' DialogBox hCurInstance, "dialog_1", hconsole, CODEPTR(DialogProc) 'the DialogProc will receive the events from the dialog
        
        CALL mySQL_Close
        
        CLOSE
        
        
        END FUNCTION ' WinMain
        
        '------------------------------------------------------------------------------
        'FUNCTION DialogProc(BYVAL hDlg AS LONG, BYVAL wMsg AS LONG, _
        ' BYVAL wParam AS LONG, BYVAL lParam AS LONG) AS LONG
        CALLBACK FUNCTION DialogProc
        LOCAL buffer AS ASCIIZ * 56
        LOCAL datav&
        
        SELECT CASE AS LONG CB.MSG
        ' SELECT CASE wMsg
        CASE %WM_INITDIALOG
        y&=0
        
        flaga%=0
        
        ' hListBox = GetDlgItem(hDlg, %ID_LST1)
        ' RES = SendMessage (hListBox, %lb_resetcontent, 0, BYVAL VARPTR(buffer))
        
        CASE %WM_COMMAND
        
        SELECT CASE AS LONG CB.CTL
        CASE %getkey
        
        LISTVIEW GET SELECT CB.HNDL,%ID_LST1 TO datav&
        
        LISTVIEW GET TEXT CB.HNDL,%ID_lst1,datav&,1 TO buffer
        setdlgitemtext(CB.HNDL,%ID_text1,buffer)
        
        
        CASE %addrtn 'add ****************************************
        '***************************************************************************************
        ' SELECT CASE AS LONG CB.MSG
        ' CASE %WM_INITDIALOG
        ' code here.......
        '
        ' CASE %WM_COMMAND ********* prevent blank records *********
        ' SELECT CASE AS LONG CB.CTL
        ' SELECT CASE AS LONG CB.CTLMSG code to prevent blank records *
        ' CASE %BN_CLICKED from key down on add *
        ' IF flaga%=0 AND %WM_KEYUP THEN *
        '***************************************************************************************
        
        SELECT CASE AS LONG CB.CTLMSG
        CASE %BN_CLICKED
        
        IF flaga%=0 AND %WM_KEYUP THEN
        
        FOR x%=1 TO 12
        varble$(x%)=""
        NEXT
        
        GetDlgItemtext(hdlg,113&,buffer,35) 'get text from textbox
        varble$(2)=buffer
        
        GetDlgItemtext(hdlg,114&,buffer,35)
        varble$(3)=buffer
        
        GetDlgItemtext(hdlg,115&,buffer,35)
        varble$(4)=buffer
        
        GetDlgItemtext(hdlg,116&,buffer,35)
        varble$(5)=buffer
        
        GetDlgItemtext(hdlg,117&,buffer,35)
        varble$(6)=buffer
        
        GetDlgItemtext(hdlg,118&,buffer,35)
        varble$(7)=buffer
        
        GetDlgItemtext(hdlg,119&,buffer,35)
        varble$(8)=buffer
        
        GetDlgItemtext(hdlg,120&,buffer,35)
        varble$(9)=buffer
        
        GetDlgItemtext(hdlg,121&,buffer,45)
        varble$(10)=buffer
        
        GetDlgItemtext(hdlg,122&,buffer,45)
        varble$(11)=buffer
        
        GetDlgItemtext(hdlg,123&,buffer,35)
        varble$(12)=buffer
        
        
        INCR rcdnumber
        
        REPLACE "'" WITH "''" IN varble$(2) 'not needed with slBuildInsertOrUpdate
        REPLACE "'" WITH "''" IN varble$(3)
        REPLACE "'" WITH "''" IN varble$(4)
        REPLACE "'" WITH "''" IN varble$(5)
        REPLACE "'" WITH "''" IN varble$(6)
        REPLACE "'" WITH "''" IN varble$(7)
        REPLACE "'" WITH "''" IN varble$(8)
        REPLACE "'" WITH "''" IN varble$(9)
        REPLACE "'" WITH "''" IN varble$(10)
        REPLACE "'" WITH "''" IN varble$(11)
        REPLACE "'" WITH "''" IN varble$(12)
        
        
        lastname$ = varble$(2)
        firstname$=varble$(3)
        addr1$ = varble$(4)
        addr2$ = varble$(5)
        cityst$ = varble$(6)
        zip$ = varble$(7)
        phone1$ = varble$(8)
        phone2$ = varble$(9)
        email1$ = varble$(10)
        email2$ = varble$(11)
        chk$ = varble$(12)
        
        primkey$=UCASE$(REMOVE$(lastname$,ANY " .'") + REMOVE$(firstname$,ANY " .'"))
        
        ' mySQL_query "INSERT INTO " + stablename +_
        ' " (pers_name,pers_phone) " + _
        ' "VALUES ('Dickinson, Don','555-555-23cb')"
        
        mysql_query "insert into addrs (primkey,lastname,firstname,addr1,addr2,cityst,zip,phone1,phone2,email1,email2,chk) values ('"+ _
        primkey$+"','"+lastname$+"','"+firstname$+"','"+addr1$+"','"+addr2$+"','"+cityst$+"','"+zip$+"','"+phone1$+ _
        "','"+phone2$+"','"+email1$+"','"+email2$+"','"+chk$+"')"
        
        
        
        buffer=""
        
        setdlgitemtext(hdlg,112&,buffer)
        setdlgitemtext(hdlg,113&,buffer)
        setdlgitemtext(hdlg,114&,buffer)
        setdlgitemtext(hdlg,115&,buffer)
        setdlgitemtext(hdlg,116&,buffer)
        setdlgitemtext(hdlg,117&,buffer)
        setdlgitemtext(hdlg,118&,buffer)
        setdlgitemtext(hdlg,119&,buffer)
        setdlgitemtext(hdlg,120&,buffer)
        setdlgitemtext(hdlg,121&,buffer)
        setdlgitemtext(hdlg,122&,buffer)
        setdlgitemtext(hdlg,123&,buffer)
        
        setfocus(getdlgitem(hdlg,113&))
        
        
        END IF
        
        FUNCTION = 0
        
        END SELECT
        
        CASE %updatertn 'update *************************************
        
        IF flaga%=0 THEN
        
        FOR x%=1 TO 12
        varble$(x)=""
        NEXT
        
        
        GetDlgItemtext(hDlg,112&,buffer,35)
        varble$(1)=buffer
        ky$=UCASE$(REMOVE$(varble$(1),ANY " "))
        
        IF mySQL_Query( "select * from " + sTableName + " where primkey like '"+ky$+"%'" ) THEN
        MSGBOX mySQL_Error(),16,"[Error] mySQL_Query"
        EXIT FUNCTION
        END IF
        
        ' slSel "select * from addrs where primkey like '"+ky$+ "%'",%snaddr,"E"
        IF ( mysql_fetch_row() ) THEN
        varble$(2) =mySQL_Data(2) '"primkey",%snaddr)
        varble$(3) =mySQL_Data(3) '"lastname",%snaddr)
        varble$(4) =mySQL_Data(4) '"firstname",%snaddr)
        varble$(5) =mySQL_Data(5) '"addr1",%snaddr)
        varble$(6) =mySQL_Data(6) '"addr2",%snaddr)
        varble$(7) =mySQL_Data(7) '"cityst",%snaddr)
        varble$(8) =mySQL_Data(8) '"zip",%snaddr)
        varble$(9) =mySQL_Data(9)
        varble$(10) =mySQL_Data(10)
        varble$(11) =mySQL_Data(11)
        varble$(12) =mySQL_Data(12)
        
        
        mySQL_Free_Result()
        
        ky$=UCASE$(REMOVE$(varble$(2),ANY " "))
        kynext$=ky$
        
        
        buffer=varble$(2)
        ' CONTROL SET TEXT hdlg, 112&,buffer 'DDT pbwin10
        setdlgitemtext(hDlg,112&,buffer)
        
        buffer=varble$(3)
        setdlgitemtext(hDlg,113&,buffer)
        
        buffer=varble$(4)
        setdlgitemtext(hDlg,114&,buffer)
        
        buffer=varble$(5)
        setdlgitemtext(hDlg,115&,buffer)
        
        buffer=varble$(6)
        setdlgitemtext(hDlg,116&,buffer)
        
        buffer=varble$(7)
        setdlgitemtext(hDlg,117&,buffer)
        
        buffer=varble$(8)
        setdlgitemtext(hDlg,118&,buffer)
        
        buffer=varble$(9)
        setdlgitemtext(hDlg,119&,buffer)
        
        buffer=varble$(10)
        setdlgitemtext(hDlg,120&,buffer)
        
        buffer=varble$(11)
        setdlgitemtext(hDlg,121&,buffer)
        
        buffer=varble$(12)
        setdlgitemtext(hDlg,122&,buffer)
        
        ' buffer=varble$(12)
        ' setdlgitemtext(hDlg,123&,buffer)
        
        
        flaga%=1
        END IF
        
        
        ELSE
        IF flaga%=1 THEN
        
        ' slSel "select * from addrs where primkey like '"+ky$+"'",%snaddr,"E"
        
        
        IF mySQL_Query( "select * from " + sTableName + " where primkey like '"+ky$+"%'" ) THEN
        MSGBOX mySQL_Error(),16,"[Error] mySQL_Query"
        EXIT FUNCTION
        END IF
        
        ' slSel "select * from addrs where primkey = '"+ky$+"'",%snaddr,"E2"
        
        IF mysql_fetch_row() THEN
        ' CONTROL GET TEXT hdlg,1003& TO buffer 'DDT statement pbwin10
        GetDlgItemtext(hdlg,113&,buffer,35)
        varble$(3)=buffer
        GetDlgItemtext(hdlg,114&,buffer,35)
        varble$(4)=buffer
        GetDlgItemtext(hdlg,115&,buffer,35)
        varble$(5)=buffer
        GetDlgItemtext(hdlg,116&,buffer,35)
        varble$(6)=buffer
        GetDlgItemtext(hdlg,117&,buffer,35)
        varble$(7)=buffer
        GetDlgItemtext(hdlg,118&,buffer,35)
        varble$(8)=buffer
        GetDlgItemtext(hdlg,119&,buffer,35)
        varble$(9)=buffer
        GetDlgItemtext(hdlg,120&,buffer,35)
        varble$(10)=buffer
        GetDlgItemtext(hdlg,121&,buffer,45)
        varble$(11)=buffer
        GetDlgItemtext(hdlg,122&,buffer,45)
        varble$(12)=buffer
        ' GetDlgItemtext(hdlg,123&,buffer,35)
        ' varble$(12)=buffer
        '
        ' slcloseset %snaddr
        
        'used with update of sqlitening
        REPLACE "'" WITH "''" IN varble$(3)
        REPLACE "'" WITH "''" IN varble$(4)
        REPLACE "'" WITH "''" IN varble$(5)
        REPLACE "'" WITH "''" IN varble$(6)
        REPLACE "'" WITH "''" IN varble$(7)
        REPLACE "'" WITH "''" IN varble$(8)
        REPLACE "'" WITH "''" IN varble$(9)
        REPLACE "'" WITH "''" IN varble$(10)
        REPLACE "'" WITH "''" IN varble$(11)
        REPLACE "'" WITH "''" IN varble$(12)
        
        IF mySQL_query ( "update addrs set lastname = '"+varble$(3)+"',firstname = '"+varble$(4)+_
        "',addr1 = '"+varble$(5)+"',addr2 = '"+varble$(6)+_
        "',cityst = '"+varble$(7)+"',zip = '"+varble$(8)+_
        "',phone1 = '"+varble$(9)+"',phone2 = '"+varble$(10)+"',email1 ='"+varble$(11)+ _
        "',email2 = '"+varble$(12)+"',chk = '"+varble$(12) +_
        "' where primkey = '" + ky$ + "'") THEN
        MSGBOX mySQL_Error(),16,"[Error] mySQL_Query"
        EXIT FUNCTION
        END IF
        
        mySQL_Free_Result()
        
        buffer=""
        ' CONTROL SET TEXT hdlg, 1002&,buffer 'DDT pbwin10
        ' CONTROL SET TEXT hdlg, 1003&,buffer 'DDT pbwin10
        setdlgitemtext(hdlg,112&,buffer)
        setdlgitemtext(hdlg,113&,buffer)
        setdlgitemtext(hdlg,114&,buffer)
        setdlgitemtext(hdlg,115&,buffer)
        setdlgitemtext(hdlg,116&,buffer)
        setdlgitemtext(hdlg,117&,buffer)
        setdlgitemtext(hdlg,118&,buffer)
        setdlgitemtext(hdlg,119&,buffer)
        setdlgitemtext(hdlg,120&,buffer)
        setdlgitemtext(hdlg,121&,buffer)
        setdlgitemtext(hdlg,122&,buffer)
        setdlgitemtext(hdlg,123&,buffer)
        
        ' control set focus hdlg,1002& 'DDT pbwin10
        setfocus(getdlgitem(hdlg,112&)) 'set tab focus to textbox 2
        
        
        flaga%=0
        END IF
        
        
        END IF
        
        END IF
        #IF 0
        CASE %prevrtn 'prev ************************************
        
        y=0
        
        FOR x&=1 TO numflds&
        varble$(x&)=""
        NEXT
        
        
        ' slSel "select * from addrs where primkey < '"+kynext$+"' order by primkey desc",%snaddr,"E"
        
        lErrNum = slGetErrorNumber
        
        IF lErrNum <> 0 THEN
        slcloseset %snaddr
        RETURN
        END IF
        
        IF slgetrow (%snaddr) THEN
        varble$(1) =slFn("primkey",%snaddr)
        varble$(2) =slFn("lastname",%snaddr)
        varble$(3) =slFn("firstname",%snaddr)
        varble$(4) =slFn("addr1",%snaddr)
        varble$(5) =slFn("addr2",%snaddr)
        varble$(6) =slFn("cityst",%snaddr)
        varble$(7) =slFn("zip",%snaddr)
        varble$(8) =slFn("phone1",%snaddr)
        varble$(9) =slFn("phone2",%snaddr)
        varble$(10) = slfn("email1",%snaddr)
        varble$(11) = slfn("email2",%snaddr)
        varble$(12) = slfn("chk",%snaddr)
        
        slcloseset %snaddr
        
        ky$=UCASE$(REMOVE$(varble$(1),ANY " "))
        kynext$=ky$
        
        buffer=varble$(1)
        ' CONTROL SET TEXT hdlg, 112&,buffer 'DDT pbwin10
        setdlgitemtext(hDlg,112&,buffer)
        
        buffer=varble$(2)
        setdlgitemtext(hDlg,113&,buffer)
        
        buffer=varble$(3)
        setdlgitemtext(hDlg,114&,buffer)
        
        buffer=varble$(4)
        setdlgitemtext(hDlg,115&,buffer)
        
        buffer=varble$(5)
        setdlgitemtext(hDlg,116&,buffer)
        
        buffer=varble$(6)
        setdlgitemtext(hDlg,117&,buffer)
        
        buffer=varble$(7)
        setdlgitemtext(hDlg,118&,buffer)
        
        buffer=varble$(8)
        setdlgitemtext(hDlg,119&,buffer)
        
        buffer=varble$(9)
        setdlgitemtext(hDlg,120&,buffer)
        
        buffer=varble$(10)
        setdlgitemtext(hDlg,121&,buffer)
        
        buffer=varble$(11)
        setdlgitemtext(hDlg,122&,buffer)
        
        buffer=varble$(12)
        setdlgitemtext(hDlg,123&,buffer)
        
        
        END IF
        
        
        
        CASE %nextrtn 'next *************************************
        y=0
        
        FOR x&=1 TO 12
        varble$(x&)=""
        NEXT
        
        slSel "select * from addrs where primkey > '"+kynext$+"' order by primkey",%snaddr,"E"
        
        lErrNum = slGetErrorNumber
        
        IF lErrNum <> 0 THEN
        slcloseset %snaddr
        RETURN
        END IF
        
        IF slgetrow (%snaddr) THEN
        varble$(1) =slFn("primkey",%snaddr)
        varble$(2) =slFn("lastname",%snaddr)
        varble$(3) =slFn("firstname",%snaddr)
        varble$(4) =slFn("addr1",%snaddr)
        varble$(5) =slFn("addr2",%snaddr)
        varble$(6) =slFn("cityst",%snaddr)
        varble$(7) =slFn("zip",%snaddr)
        varble$(8) =slFn("phone1",%snaddr)
        varble$(9) =slFn("phone2",%snaddr)
        varble$(10) = slfn("email1",%snaddr)
        varble$(11) = slfn("email2",%snaddr)
        varble$(12) = slfn("chk",%snaddr)
        
        slcloseset %snaddr
        
        ky$=UCASE$(REMOVE$(varble$(1),ANY " "))
        kynext$=ky$
        
        buffer=varble$(1)
        ' CONTROL SET TEXT hdlg, 112&,buffer 'DDT pbwin10
        setdlgitemtext(hDlg,112&,buffer)
        
        buffer=varble$(2)
        setdlgitemtext(hDlg,113&,buffer)
        
        buffer=varble$(3)
        setdlgitemtext(hDlg,114&,buffer)
        
        buffer=varble$(4)
        setdlgitemtext(hDlg,115&,buffer)
        
        buffer=varble$(5)
        setdlgitemtext(hDlg,116&,buffer)
        
        buffer=varble$(6)
        setdlgitemtext(hDlg,117&,buffer)
        
        buffer=varble$(7)
        setdlgitemtext(hDlg,118&,buffer)
        
        buffer=varble$(8)
        setdlgitemtext(hDlg,119&,buffer)
        
        buffer=varble$(9)
        setdlgitemtext(hDlg,120&,buffer)
        
        buffer=varble$(10)
        setdlgitemtext(hDlg,121&,buffer)
        
        buffer=varble$(11)
        setdlgitemtext(hDlg,122&,buffer)
        
        buffer=varble$(12)
        setdlgitemtext(hDlg,123&,buffer)
        buffer=varble$(1)
        
        
        END IF
        
        
        
        CASE %deletertn 'delete **************************************
        IF flaga%=0 THEN
        
        FOR x%=1 TO 12
        varble$(x)=""
        NEXT
        
        ' CONTROL GET TEXT hdlg,1002& TO buffer 'a DDT statement pbwin10
        GetDlgItemtext(hDlg,112&,buffer,35)
        varble$(1)=buffer
        ky$=UCASE$(REMOVE$(varble$(1),ANY " "))
        
        
        slSel "select * from addrs where primkey like '"+ky$+ "%'",%snaddr,"E"
        
        IF slgetrow (%snaddr) THEN
        varble$(1) =slFn("primkey",%snaddr)
        varble$(2) =slFn("lastname",%snaddr)
        varble$(3) =slFn("firstname",%snaddr)
        varble$(4) =slFn("addr1",%snaddr)
        varble$(5) =slFn("addr2",%snaddr)
        varble$(6) =slFn("cityst",%snaddr)
        varble$(7) =slFn("zip",%snaddr)
        varble$(8) =slFn("phone1",%snaddr)
        varble$(9) =slFn("phone2",%snaddr)
        varble$(10) = slfn("email1",%snaddr)
        varble$(11) = slfn("email2",%snaddr)
        varble$(12) = slfn("chk",%snaddr)
        kynext = slfn("rcdno",%snaddr)
        
        ky$=UCASE$(REMOVE$(varble$(1),ANY " "))
        kynext$=ky$
        slcloseset %snaddr
        
        buffer=varble$(1)
        ' CONTROL SET TEXT hdlg, 112&,buffer 'DDT pbwin10
        setdlgitemtext(hDlg,112&,buffer)
        
        buffer=varble$(2)
        setdlgitemtext(hDlg,113&,buffer)
        
        buffer=varble$(3)
        setdlgitemtext(hDlg,114&,buffer)
        
        buffer=varble$(4)
        setdlgitemtext(hDlg,115&,buffer)
        
        buffer=varble$(5)
        setdlgitemtext(hDlg,116&,buffer)
        
        buffer=varble$(6)
        setdlgitemtext(hDlg,117&,buffer)
        
        buffer=varble$(7)
        setdlgitemtext(hDlg,118&,buffer)
        
        buffer=varble$(8)
        setdlgitemtext(hDlg,119&,buffer)
        
        buffer=varble$(9)
        setdlgitemtext(hDlg,120&,buffer)
        
        buffer=varble$(10)
        setdlgitemtext(hDlg,121&,buffer)
        
        buffer=varble$(11)
        setdlgitemtext(hDlg,122&,buffer)
        
        buffer=varble$(12)
        setdlgitemtext(hDlg,123&,buffer)
        
        flaga%=1
        END IF
        
        ELSE
        IF flaga%=1 THEN
        
        ' slexe "delete from addrs where trim(primkey) = '"+ky$+"'"
        
        
        buffer=""
        setdlgitemtext(hdlg,112&,buffer)
        setdlgitemtext(hdlg,113&,buffer)
        setdlgitemtext(hdlg,114&,buffer)
        setdlgitemtext(hdlg,115&,buffer)
        setdlgitemtext(hdlg,116&,buffer)
        setdlgitemtext(hdlg,117&,buffer)
        setdlgitemtext(hdlg,118&,buffer)
        setdlgitemtext(hdlg,119&,buffer)
        setdlgitemtext(hdlg,120&,buffer)
        setdlgitemtext(hdlg,121&,buffer)
        setdlgitemtext(hdlg,122&,buffer)
        setdlgitemtext(hdlg,123&,buffer)
        
        ' control set focus hdlg,1002& 'DDT pbwin10
        setfocus(getdlgitem(hdlg,112&)) 'set tab focus to textbox 2
        flaga%=0
        END IF
        END IF
        #ENDIF
        CASE %exitrtn 'exit
        mySQL_Free_Result()
        
        CLOSE
        
        DIALOG GET LOC hdlg TO apsn&, bpsn&
        
        OPEN "ad002b.dss" FOR OUTPUT AS 1
        WRITE #1,apsn&,bpsn&
        CLOSE 1
        
        EndDialog hDlg, 0
        FUNCTION = 0
        END
        CASE %IDCANCEL 'esc cancel
        ' smySQL_Free_Result()
        
        CLOSE
        DIALOG GET LOC hdlg TO apsn&, bpsn&
        
        OPEN "ad002b.dss" FOR OUTPUT AS 1
        WRITE #1,apsn&,bpsn&
        CLOSE 1
        
        EndDialog hDlg, 0
        
        FUNCTION = 1
        END
        END SELECT
        
        CASE %IDCANCEL 'X cancel
        ' smySQL_Free_Result()
        
        CLOSE
        
        DIALOG GET LOC hdlg TO apsn&, bpsn&
        
        OPEN "ad002b.dss" FOR OUTPUT AS 1
        WRITE #1,apsn&,bpsn&
        CLOSE 1
        
        EndDialog hDlg, 1
        FUNCTION = 1
        END
        END SELECT
        END FUNCTION
        djthain

        Comment


        • #5
          I gave up trying to work my way through all that redundant code. Ever heard of loops?
          e.g.
          Code:
          FOR x = 1 TO 12
              setdlgitemtext(hDlg,111 + x,varble$(x))
          NEXT
          is much easier to follow, maintain and debug than
          Code:
          buffer=varble$(1)
          ' CONTROL SET TEXT hdlg, 112&,buffer 'DDT pbwin10
          setdlgitemtext(hDlg,112&,buffer)
          
          buffer=varble$(2)
          setdlgitemtext(hDlg,113&,buffer)
          
          buffer=varble$(3)
          setdlgitemtext(hDlg,114&,buffer)
          
          buffer=varble$(4)
          setdlgitemtext(hDlg,115&,buffer)
          
          buffer=varble$(5)
          setdlgitemtext(hDlg,116&,buffer)
          
          buffer=varble$(6)
          setdlgitemtext(hDlg,117&,buffer)
          
          buffer=varble$(7)
          setdlgitemtext(hDlg,118&,buffer)
          
          buffer=varble$(8)
          setdlgitemtext(hDlg,119&,buffer)
          
          buffer=varble$(9)
          setdlgitemtext(hDlg,120&,buffer)
          
          buffer=varble$(10)
          setdlgitemtext(hDlg,121&,buffer)
          
          buffer=varble$(11)
          setdlgitemtext(hDlg,122&,buffer)
          
          buffer=varble$(12)
          setdlgitemtext(hDlg,123&,buffer)
          I think I counted 13 places where you can do that.

          Comment


          • #6
            Deleted
            (I mmissed that you ARE escaping apostophes before updating).

            Comment


            • #7

              Here is a short version of my program. The record is chopped off?

              Code:
              'Here is what the input record looks like:
              '1 THAINDAVID thain david 3329 whippoorwill ln. xxx enid ok 73701 5492394666
              
              '2 ROBBERTWLARRY robbertw larry 2277 west wallnut street xyz enid ok 73703 5493668022 enid ok 73703 5493668022
              
              'Here is what the output looks like:
              'THAINDAVID thain david 3329 whippoo
              
              'ROBBERTWLARRY robbertw larry 2277
              
              #COMPILE EXE
              
              #INCLUDE "MySQL.inc" ' header file - declares for MySQL
              
              GLOBAL primkey$()
              GLOBAL lastname$()
              GLOBAL firstname$()
              GLOBAL addr1$()
              GLOBAL addr2$()
              GLOBAL cityst$()
              GLOBAL zip$()
              GLOBAL rcdnumber AS LONG
              
              GLOBAL sTableName AS STRING
              
              '------------------------------------------------------------------------------
              
              FUNCTION PBMAIN() AS LONG
              
              DIM primkey$(1)
              DIM lastname$(1)
              DIM firstname$(1)
              DIM addr1$(1)
              DIM addr2$(1)
              DIM cityst$(1)
              DIM zip$(1)
              DIM x AS LONG
              
              LOCAL y AS LONG, nError AS LONG
              LOCAL sHost AS STRING, sUser AS STRING, sPassword AS STRING, sTemp AS STRING
              LOCAL sOrderField AS STRING,datarow AS LONG
              LOCAL sDbname AS ASCIIZ*255, selectdb AS ASCIIZ*255, selecttb AS ASCIIZ*255
              LOCAL nField AS LONG,ptfield AS LONG,numrows AS LONG,resrows AS LONG
              
              sHost = "localhost"
              sUser = "djthain1"
              sPassword = "[email protected]"
              
              sDbname = "bookkeeping2"
              sTableName = "addrs"
              
              '***********************************************************
              'Init System
              
              IF mysql_init() THEN
              MSGBOX mySQL_Error(),16,"[Error] mysql_init"
              EXIT FUNCTION
              END IF
              
              '***********************************************************
              'Setup Options
              
              IF mysql_options( %MYSQL_OPT_LOCAL_INFILE, "local-infile=1" ) THEN
              MSGBOX mySQL_Error(),16,"[Error] mysql_options"
              EXIT FUNCTION
              END IF
              
              '***********************************************************
              '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
              
              
              '***********************************************************
              'Select the Database
              
              IF mySQL_Select_Db( sDbname ) THEN '-- Select DB
              MSGBOX mySQL_Error(),16,"[Error] mySQL_Select_Db"
              EXIT FUNCTION
              END IF
              
              LOCAL windowtitle AS ASCIIZ * 256
              
              IF mySQL_Query( "select * from " + sTableName ) THEN
              MSGBOX mySQL_Error(),16,"[Error] mySQL_Query"
              EXIT FUNCTION
              END IF
              
              ResRows = mySQL_Num_Rows
              rcdnumber=resrows
              
              REDIM primkey$(rcdnumber)
              REDIM lastname$(rcdnumber)
              REDIM firstname$(rcdnumber)
              REDIM addr1$(rcdnumber)
              REDIM addr2$(rcdnumber)
              REDIM cityst$(rcdnumber)
              REDIM zip$(rcdnumber)
              
              DO WHILE (mysql_fetch_row())
              
              primekey$ =mySQL_Data(2) '"primkey",%snaddr)
              lstname$ =mySQL_Data(3) '"lastname",%snaddr)
              fstname$ =mySQL_Data(4) '"firstname",%snaddr)
              addrs1$ =mySQL_Data(5) '"addr1",%snaddr)
              addrs2$ =mySQL_Data(6) '"addr2",%snaddr)
              ctyst$ =mySQL_Data(7) '"cityst",%snaddr)
              zp$ =mySQL_Data(8) '"zip",%snaddr)
              
              INCR y&
              lstname$=RTRIM$(lstname$)
              ARRAY INSERT primkey$(y&), primekey$
              ARRAY INSERT lastname$(y&), lstname$
              ARRAY INSERT firstname$(y&), fstname$
              ARRAY INSERT addr1$(y&), addrs1$
              ARRAY INSERT addr2$(y&), addrs2$
              ARRAY INSERT cityst$(y&), ctyst$
              ARRAY INSERT zip$(y&), zp$
              
              LOOP
              
              FOR x = 1 TO rcdnumber
              MSGBOX primkey$(x)+" "+lastname$(x)+" "+firstname$(x)+" "+addr1$(x)+" "+addr2$(x)+" "+cityst$(x)+" "+zip$(x)
              NEXT x
              
              mySQL_Free_Result()
              
              CALL mySQL_Close
              
              CLOSE
              
              
              END FUNCTION ' PbMain
              djthain

              Comment


              • #8
                Please post link to your include file (as noted in next post by Stuart)

                Globals arrays are defined and then local arrays. Don't need both.
                Array Insert is not needed.
                Something like this would eliminate a lot of code and is reusable.
                Code:
                FUNCTION GetRecordSet(sql AS STRING, s() AS STRING) AS LONG
                 'no idea how mySQL returns column names
                 LOCAL c,r,cols,rows AS LONG
                
                 IF mySQL_Query(sql) THEN EXIT FUNCTION
                
                 rows = mySQL_Num_Rows
                 cols = mySQL_Num_Cols  'or whatever it is called
                
                 REDIM s(1 TO cols,1 TO rows) AS STRING
                 FOR r = 1 TO rows
                  mysql_fetch_row()
                  FOR c = 1 TO cols
                   s(c,r)=mySQL_Data(c)
                  NEXT
                 NEXT
                
                 mySQL_Free_Result()
                 mySQL_Close
                
                END FUNCTION

                Code:
                FUNCTION PBMAIN AS LONG
                 LOCAL sql, s() AS STRING
                 GetRecordSet sql, s()
                 ? JOIN$(s(),$CR),,USING$("cols #  rows #",UBOUND(s,1),UBOUND(s,2))
                END FUNCTION
                
                FUNCTION GetRecordSet(sql AS STRING, s() AS STRING) AS LONG
                 LOCAL c,r,cols,rows AS LONG
                 if mysql_query(sql) THEN EXIT FUNCTION
                 'rows = mySQL_Num_Rows
                 'cols = mySQL-Num_Cols
                 rows = 1
                 cols = 3
                 REDIM s(1 TO cols,1 TO rows) AS STRING
                 FOR r = 1 TO rows
                  'mysql_fetch_row()
                  FOR c = 1 TO cols
                   's(c,r)=mySQL_Data(c)
                   s(c,r) = USING$("c#_,r#",c,r)
                  NEXT
                 NEXT
                 'mySQL_Free_Result()
                 'mySQL_Close
                END FUNCTION

                Comment


                • #9
                  Your records appear to be be truncated at 35 characters, which is also the size you are defining your fields as. Clue?

                  When I asked earlier what .ini file you are using you just repled "I use the mysql.ini that is found in the forums". That was NOT helpful, beccause there is more than one implementation here. I'm starting to suspect a problem with mySQL_Data or mySQL_fetch_row. your .ini file. Please post it here.

                  If you are using the file linked to in Post#3, then note the comment therein:

                  ' You must call mysql_store_result() or mysql_use_result() for every query that successfully retrieves data

                  Comment


                  • #10
                    Originally posted by Mike Doty View Post
                    Something like this would eliminate a lot of code and is reusable.
                    ]
                    You mean similar to my comment in Post#5?
                    i.e. get rid of all of those unnecessary temporary buffers (that may be the root of the undesired result)

                    Comment


                    • #11
                      Same idea, but I filled a 2-dimensional array in a reusable function.

                      Comment


                      • #12
                        I suspect that a judicious use of debugging message boxes will reveal the answer.

                        Code:
                        DO WHILE (mysql_fetch_row())
                        
                        primekey$ =mySQL_Data(2) '"primkey",%snaddr)
                        
                        ? mySQL_DAta(2)
                        
                        lstname$ =mySQL_Data(3) '"lastname",%snaddr)
                        fstname$ =mySQL_Data(4) '"firstname",%snaddr)
                        addrs1$ =mySQL_Data(5) '"addr1",%snaddr)
                        addrs2$ =mySQL_Data(6) '"addr2",%snaddr)
                        ctyst$ =mySQL_Data(7) '"cityst",%snaddr)
                        zp$ =mySQL_Data(8) '"zip",%snaddr)
                        
                        INCR y&
                        lstname$=RTRIM$(lstname$)
                        ARRAY INSERT primkey$(y&), primekey$
                        
                        ? primkey$(y&)
                        
                        ARRAY INSERT lastname$(y&), lstname$
                        ARRAY INSERT firstname$(y&), fstname$
                        ARRAY INSERT addr1$(y&), addrs1$
                        ARRAY INSERT addr2$(y&), addrs2$
                        ARRAY INSERT cityst$(y&), ctyst$
                        ARRAY INSERT zip$(y&), zp$
                        
                        LOOP
                        
                        FOR x = 1 TO rcdnumber
                        ? primkey$(x)
                        MSGBOX primkey$(x)+" "+lastname$(x)+" "+firstname$(x)+" "+addr1$(x)+" "+addr2$(x)+" "+cityst$(x)+" "+zip$(x)
                        NEXT x
                        etc

                        I'm betting that somewhere a 35 character STRING with a NULL is being carried through

                        Comment


                        • #13
                          '
                          Code:
                          electtb = "CREATE TABLE "+sTableName+ "(" + _
                          "rcdno int unsigned auto_increment primary key," + _
                          "primkey varchar(70)," + _
                          "lastname varchar(35)," + _
                          "firstname varchar(35)," + _
                          "addr1 varchar(35)," + _
                          "addr2 varchar(35)," + _
                          "cityst varchar(35)," + _
                          "zip varchar(35)," + _
                          "phone1 varchar(35)," + _
                          "phone2 varchar(35)," + _
                          "email1 varchar(45)," + _
                          "email2 varchar(45)," + _
                          "chk varchar(35) )"
                          '

                          primekey$ =mySQL_Data(2)

                          Is mySQL_Data() a ZERO based array?
                          If so you are loading the 35 character LastName into primkey$(y&) which is the first part of your MessageBox string.

                          Comment


                          • #14
                            Originally posted by Stuart McLachlan View Post
                            If you are using the file linked to in Post#3, then note the comment therein:
                            OK, you are NOT using the mySL.ini linked in Post #3, that one won't compile, it gives lots of "BYVAL required with pointers" errors

                            So once again, what mySQL.ini are you using. Please post it in its entirety.

                            Without that info, we can't help you. (I've actually replicated your booking2 data base and addr table, but can't go any further with identifying the problem)




                            Comment


                            • #15
                              https://forum.powerbasic.com/forum/u...-mysql-headers
                              David posted in the above thread.

                              Comment


                              • #16
                                Doh! Edited the MySQL.ini so that it compiles. But program won't run. Then I realised I'm running 64bit mySQL via WAMPServer, so after setting up the database, i can't use it in this 32 bit application
                                (SQLTools looks good for the OP!)


                                Comment


                                • #17
                                  Deleted. See next post


                                  Comment


                                  • #18
                                    Using the MySQL.ini linked earlier with the PTR definitions modified to BYVAL
                                    and libmySQL.dll 32 bit downloaded from the web. Stripped down still truncates retreived data at 36 characters!

                                    The only libmyql.dll I found to download standalone is ver 5.5.9.0 dated 12 march 2011, so that may be an issue?
                                    (Since located and tested 6.1.10.0 with same results)

                                    '
                                    Code:
                                    #COMPILE EXE
                                    #INCLUDE "MySQL.inc" ' header file - declares for MySQL
                                    
                                    FUNCTION PBMAIN() AS LONG
                                    LOCAL y AS LONG, nError AS LONG
                                    LOCAL sHost AS STRING, sUser AS STRING, sPassword AS STRING, sTemp AS STRING
                                    LOCAL sDbname AS ASCIIZ*255, selectdb AS ASCIIZ*255, selecttb AS ASCIIZ*255
                                    
                                    sHost = "localhost"
                                    sUser = "djthain1"
                                    sPassword = "[email protected]"
                                    
                                    IF mysql_init() THEN
                                    MSGBOX mySQL_Error(),16,"[Error] mysql_init"
                                    EXIT FUNCTION
                                    END IF
                                    
                                    IF mysql_options( %MYSQL_OPT_LOCAL_INFILE, "local-infile=1" ) THEN
                                    MSGBOX mySQL_Error(),16,"[Error] mysql_options"
                                    EXIT FUNCTION
                                    END IF
                                    
                                    IF mySQL_Connect( sHost, sUser, sPassword, 0 ) THEN '-- Connect to mySQL
                                    MSGBOX mySQL_Error(),16,"[Error] mySQL_Connect"
                                    EXIT FUNCTION
                                    END IF
                                    
                                    IF mySQL_Select_Db( "bookkeeping2") THEN '-- Select DB
                                    MSGBOX mySQL_Error(),16,"[Error] mySQL_Select_Db"
                                    EXIT FUNCTION
                                    END IF
                                    'primkey = VARCHAR(150)
                                    IF mySQL_Query( "select primkey from addrs" ) THEN
                                    MSGBOX mySQL_Error(),16,"[Error] mySQL_Query"
                                    EXIT FUNCTION
                                    END IF
                                    
                                    'Table structure is :
                                    'CREATE TABLE addrs (
                                    '  primkey varchar(150) DEFAULT NULL
                                    ') ENGINE=MyISAM DEFAULT CHARSET=latin1;
                                    'Value of primkey in first record is: 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
                                    
                                    mysql_fetch_row()
                                    ? mysql_Data(1)  ' returns 123456789012345678901234567890123456  ' i,e, truncated at 36 characters!
                                    mySQL_Free_Result()
                                    CALL mySQL_Close
                                    
                                    CLOSE
                                    END FUNCTION
                                    '

                                    Comment


                                    • #19
                                      This thread should me moved to a different subforum.
                                      "Not my circus, not my monkeys."

                                      Comment


                                      • #20
                                        Originally posted by Eric Pearson View Post
                                        This thread should me moved to a different subforum.
                                        True! Who's going to do it?

                                        Comment

                                        Working...
                                        X