Announcement

Collapse
No announcement yet.

ODBC TEXT driver - syntax for SELECT using individual fieldnames

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

  • ODBC TEXT driver - syntax for SELECT using individual fieldnames

    I'm working through this with Eric and grateful for his help, and in the meantime, I thought I'd canvas the community for additional insights.

    I'm re-learning SQL_Tools Pro (again - it's been awhile), and in this situation, I have a 300K (for now; it'll grow) log file that I need to query.

    The log file has 15 fields, uses TAB delimiters, is named COC_LOG.TAB, has a .DSN that specifies the Text driver.

    During development and testing, I've been using a simple SELECT statement that queries all columns using the * indicator. I get perfect results - every column from all rows!

    >>> I need to change to query only several columns, like: PREPAREDFOR, PRODID, NSN

    ...but I'm having trouble with syntax, and delimiters in particular. See code below...

    I've searched MSDN and other online sites, but it seems as if everyone always grabs all columns. All my search results show only queries that use SELECT *
    I am NOT finding ANY code in ANY SQL variation that queries individual fields.

    So I'd appreciate it if anyone can show me the proper syntax/delimiters for a multiple-field query. Thanks in advance!!!

    Here's the code I've been using... (I'm leaving in some lines of code that are commented out, to show what I've been doing by way of verifying the data file/connection...)

    Thanks,
    -John
    Code:
    The .DSN:    "D:\ProjFF\CoC_Creator\release\_LOGFILES\CoC_LOG.tab.dsn"
    [ODBC] DRIVER=Microsoft Text Driver (*.txt; *.csv) UID=admin UserCommitSync=Yes Threads=3 SafeTransactions=0 PageTimeout=5 MaxScanRows=25 MaxBufferSize=2048 FIL=text Extensions=txt,csv,tab,asc DriverId=27 DefaultDir=D:\ProjFF\CoC_Creator\release\_LOGFILES DBQ=D:\ProjFF\CoC_Creator\release\_LOGFILES\CoC_LOG.tab
    Here's my code: lResult = SQL_OpenDB ("D:\ProjFF\CoC_Creator\release\_LOGFILES\CoC_LOG.tab.dsn") 'various tests: 'x = ReportDBConnection() '? x ,, "ReportDBConnection()" ''' x = SQL_TblInfoStr(1,%TABLE_NAME) ''' ? "x = " & x ,, "SQL_TblInfoStr(1,%TABLE_NAME)" 'is this still useful to see? 'Local types As String 'SQL_SetOptionStr %OPT_TABLE_TYPES, "TABLE" 'lResult = SQL_GetTblInfo '??? '? "lResult = " & Str$(lResult) ,, "After: SQL_GetTblInfo" ' is returning a 0 'Back to the main process: sStatement = "SELECT * FROM " & $Dq & "COC_LOG.tab & $Dq ' YES! the DQs work (should I be using "LOGFILES" ???) 'sStatement = "SELECT PreparedFor, ProdID, NSN FROM " & $Dq & "COC_LOG.tab & $Dq ' <<<<<<<< ' the last line is NO GOOD, fieldnames are not recognized SQL_Stmt %IMMEDIATE, sStatement 'now show results Local lOutBuf As Long lOutBuf = FreeFile m = "" Do SQL_FETCH 'Check for errors If SQL_ErrorPending Then Open "SQL_ErrorLog.txt" For Append As #lOutBuf Print #lOutBuf, "----- " & ISODateFromDos(Date$) & $Spc & Time$ & " -----" & $CrLf Print #lOutBuf, sStatement & $CrLf Print #lOutBuf, SQL_ErrorQuickAll & $CrLf & $CrLf Close #lOutBuf SQL_CloseDB 'and be sure we Close the database ? sStatement,,"Failure after SQL_FETCH" Exit Sub 'Function End If If SQL_EOD Then Exit Loop m &= SQL_ResColString(%All_Cols) ' does it insert CRLFs? Loop ? m,,"from the SELECT statement" lOutBuf = FreeFile Open "Results_Log.txt" For Append As #lOutBuf Print #1, "----- " & ISODateFromDos(Date$) & $Spc & Time$ & " -----" & $CrLf Print #lOutBuf, m & $CrLf & $CrLf Close #lOutBuf 'Exit sub . . .


    P.S. In case anyone else is thinking of trying this, here's a tip that is not evident at first:
    - Put your data source file(s) into a separate folder with the .DSN and no other files!
    Why?
    The ODBC Text driver considers the folder to be the Database name, and each .ASC, .CSV, .TAB, or .TXT file to be a Table.
    DO NOT leave your other .TXT note files in that folder, or they the driver will consider them tables, and it will mess with your getting access to the actual data files... (don't ask me how I learned this...)


    ALSO: In the .DSN, if you have a DBQ= line, be sure it does NOT have a trailing slash, or else the ODBC panel will display.
    Pretty sure it does not want a filename either...



  • #2
    Your SQL looks fine. Check your DSN

    Did you check "Column Name Header" and then click "Guess"?

    Click image for larger version  Name:	ColName.jpg Views:	0 Size:	103.2 KB ID:	786666

    Comment


    • #3
      Hi John,

      I captured the following from a quickie tool I wrote in 2010 that used the MS Text Driver.

      That driver was 32-bit only and is not available on 64 bit Windows 10. It was referred to as the MS jet 4.0 driver.

      Anyway, my 2010 tool included a couple of hints that might help you (below)




      CSV SQL TOOL by Kevin Diggins 2010

      CSV file must have column headings

      Example: Select * FROM job_data.csv WHERE jobnumber LIKE'abc%' and notes = 'sold'



      I remember using MS Jet driver for select queries and sometimes it was hit-or-miss, other times completely wacky.

      My recommendation would be to import your csv file into a real database and save yourself a lot of frustration.


      Comment


      • #4
        Originally posted by Mr. Kevin Diggins View Post
        That driver was 32-bit only and is not available on 64 bit Windows 10. It was referred to as the MS jet 4.0 driver.
        MS Jet driver is the MS Access engine for .mdb files (i.e.up to Office 2003), not a text file driver.


        Comment


        • #5
          Originally posted by John Montenigro View Post

          The ODBC Text driver considers the folder to be the Database name, and each .ASC, .CSV, .TAB, or .TXT file to be a Table.
          DO NOT leave your other .TXT note files in that folder, or they the driver will consider them tables, and it will mess with your getting access to the actual data files... (don't ask me how I learned this...)
          Only if you don't change the defaults. see the Files - Extension list section in the screencap above
          Tell it that "tab" is the only table extension.

          Comment


          • #6
            Originally posted by John Montenigro View Post
            I'm working through this with Eric and grateful for his help, and in the meantime, I thought I'd canvas the community for additional insights.

            I'm re-learning SQL_Tools Pro (again - it's been awhile), and in this situation, I have a 300K (for now; it'll grow) log file that I need to query.

            The log file has 15 fields, uses TAB delimiters, is named COC_LOG.TAB, has a .DSN that specifies the Text driver.
            One further wrinkle. If you don't want to edit the DSN using the wizard, you can create the necessary table definition file in the directory.
            Create a file called "schema.ini" and populate it with blocks like this:

            Code:
            [testdata.txt]
            ColNameHeader=True
            Format=TabDelimited
            MaxScanRows=0
            CharacterSet=OEM
            Col1=PREPAREDFOR Char Width 255
            Col2=PRODID Char Width 255
            Col3=NSN Integer

            Comment


            • #7
              @Stuart (post #2),
              Thanks - I will have to go back to the ODBC Admin. I used it to initially create the DSN, but I've tweaked things along the way, so probably best to start over.
              I don't recall doing any setting such as you show, so that'll be good to know how to do.

              @Kevin,
              The data file does have a header, but I'm not using CSV. Everything is tab-delimited. I will explore your suggestion about importing the file into a regular table. So far I have not worked with any real DB files, only text (log files), so that's another thing I'll need to learn. Maybe easiest to use the Excel driver and create an XLS... I now have a weekend project!

              @Stuart (post #5)
              I've seen that part of the panel, but didn't know how to use it (could not find instructions on MSDN). I didn't realize that it tells the driver what it CANNOT use... That's very cool, and I'm looking forward to trying it out. (Normally the log files are .TSV, and I renamed to TAB to comply with what I saw there... It would be nice to not have to re-code a lot!

              @Stuart (post #6),
              Wow! THAT is cool! Does the SQL_OpenDB statement need to refer to schema.ini by name? Should I get rid of the DSN file? Is it required for the FieldNames to be all uppercase?

              Thanks for all the help!!!

              I'm going to shut down and get some sleep, but I'm excited to try these things in the morning!

              -John


              Comment


              • #8
                @Stuart,
                Hmm, a quick test before sleeping (couldn't wait to try the schema.ini).
                ODBC Admin came up (NOT the same as the one you screen captured above) and wanted me to specify a DSN... so I entered the datafile name...
                I removed the other file extensions except .TAB...
                I thought I had it, but then got this:

                ---------------------------
                ODBC Text Driver Login Failed
                ---------------------------
                Invalid setting in Excel key of the Engines section of the Windows Registry.
                ---------------------------
                OK Cancel
                ---------------------------
                So, I'm REALLY stopping now, and tomorrow I'll do some searching on how to use the schema.ini stuff.

                Thanks for providing guidance!
                -John

                Comment


                • #9
                  Originally posted by John Montenigro View Post
                  @Stuart (post #2),
                  Thanks - I will have to go back to the ODBC Admin. I used it to initially create the DSN, but I've tweaked things along the way, so probably best to start over.
                  I don't recall doing any setting such as you show, so that'll be good to know how to do.

                  @Kevin,
                  The data file does have a header, but I'm not using CSV. Everything is tab-delimited. I will explore your suggestion about importing the file into a regular table. So far I have not worked with any real DB files, only text (log files), so that's another thing I'll need to learn. Maybe easiest to use the Excel driver and create an XLS... I now have a weekend project!
                  Aaaaaaaahhhhh! No!. Excel is NOT a database - trying to use it as one causes no end of problems when it changes things (Like when it autoomatically changesthe text string "1 Sep" to the number 43709. (And next year, it would change it to 44075

                  @Stuart (post #6),
                  Wow! THAT is cool! Does the SQL_OpenDB statement need to refer to schema.ini by name? Should I get rid of the DSN file? Is it required for the FieldNames to be all uppercase?
                  SQL Tools doesn't know anything about Schema's they are handled by the ODBC driver, so you still need a DSN* for SQL_OpenDB
                  The DSN points the ODBC driver to the directory and it knows to look for the Schema file.

                  Actually, you don't need a DSN file at all, If you create the appropriate schema.ini in your data directory, you can just pass an ODBC Connection string to SQL_Open

                  Code:
                  ...
                  $DSN = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=C:\MyPath\;Extensions=tsv"
                  ...
                  SQL_OpenDatabase 1, $DSN ,%PROMPT_TYPE_NOPROMPT
                  ...
                  ou can do similar with any ODBC driver. ou can find appropriate strings here:
                  https://www.connectionstrings.com/


                  Field names don't have to be all upper case. It depends on the particular ODBC driver, but in many (most?) drivers, fieldname case in irrelevant in queries.

                  So any of these should work regardless of how you you capitalised the first row of the file.
                  SELECT PreparedFor, ProdID, NSN FROM SELECT PREPAREDFOR, ProdID, NSN FROM SELECT preparedfor, ProdID, NSN FROM

                  Comment


                  • #10
                    Originally posted by John Montenigro View Post
                    @Stuart,
                    Hmm, a quick test before sleeping (couldn't wait to try the schema.ini).
                    ODBC Admin came up (NOT the same as the one you screen captured above) and wanted me to specify a DSN... so I entered the datafile name...
                    I removed the other file extensions except .TAB...
                    I thought I had it, but then got this:



                    So, I'm REALLY stopping now, and tomorrow I'll do some searching on how to use the schema.ini stuff.

                    Thanks for providing guidance!
                    -John
                    If you saw a different screen, you are probably running the 64bit ODBC administrator (that's what you get from the control panel on 64bit Windows)

                    You need to be using the 32bit ODBC drivers with PB/SQLTools. Run C:Windows\SysWOW64\odbcad.exe (or wherever you SysWOW64 is)

                    Comment


                    • #11
                      John,

                      It's a small point (because your program is working ok) but you should use $SQ not $DQ as a delimiter in SQL statements.

                      > ODBC Admin came up (NOT the same as the one you screen captured above)

                      The ODBC Admin applet displays some dialogs itself, but it also calls standard-name function in the various drivers, so the dialogs you see will vary from driver to driver and even version to version.


                      Stuart,

                      > MS Jet driver is the MS Access engine for .mdb files (i.e.up to Office 2003), not a text file driver.

                      The Jet driver does in fact support text file, as does the "Access Text Driver".

                      "Not my circus, not my monkeys."

                      Comment


                      • #12
                        Originally posted by Eric Pearson View Post
                        John,

                        It's a small point (because your program is working ok) but you should use $SQ not $DQ as a delimiter in SQL statements.

                        > ODBC Admin came up (NOT the same as the one you screen captured above)

                        The ODBC Admin applet displays some dialogs itself, but it also calls standard-name function in the various drivers, so the dialogs you see will vary from driver to driver and even version to version.


                        Stuart,

                        > MS Jet driver is the MS Access engine for .mdb files (i.e.up to Office 2003), not a text file driver.

                        The Jet driver does in fact support text file, as does the "Access Text Driver".
                        Yes, because Access/Jet directly supports connections to text files.
                        But it's a kludge to use it for ODBC to text files as the Jet engine has a number of limitations (notably, maximum number of columns in the table and in the columns returned by a UNION ALL)

                        Comment


                        • #13
                          Originally posted by Stuart McLachlan View Post
                          ... Yes, because Access/Jet directly supports connections to text files.
                          Another thing about Jet (pre-64bit, pre-Win10) was that it came included with the OS. That made it so folks-in-the-know didn't need to purchase MS Office (for example), in order to gain some basic DB querying power. I used Jet in several desktop apps and in a handful of long running web apps, before MSSQLSVR was deployed at our company.

                          Comment


                          • #14
                            Oh man, you guys are fabulous! This is exactly the kind of discussion on nuances that I need! Very informative and helpful, thank you.

                            I played a bit with the in-line $DSN connection string, but I'll have to come back to see what I'm doing wrong...


                            I went back and tried the (specifically) 32-bit ODBC Admin and was able to see and set the fields as shown in post #2.
                            HOWEVER, the error message that I mentioned in post #8 about registry key for Excel engine did come up.
                            Whereas before I had clicked Cancel, this time I clicked OK (twice) and got to the second ODBC screen. I then set the values and got the .DSN
                            I was able to connect and see the full return set.

                            BUT!
                            lNumColumns = SQL_TblColCount(1) always returns 2 (but the header contains 16 fieldnames),
                            and
                            SQL_TblColInfoStr(1,i,%TBLCOL_COLUMN_NAME) returns 2 strings:
                            1. a string containing the first 48 or so characters of my header row (filenames),
                            2. "F2"

                            I'm not getting any SQL error messages, so I presume it's something in the .DSN. Here's the contents:
                            Code:
                            [ODBC]
                            DRIVER=Microsoft Text Driver (*.txt; *.csv)
                            PageTimeout=5
                            MaxBufferSize=2048
                            FIL=text
                            Extensions=tab
                            DriverId=27
                            DefaultDir=D:\PROJFF\COC_CREATOR\LOGFILES
                            DBQ=D:\PROJFF\COC_CREATOR\LOGFILES
                            I'm guessing that if I can fix the cause of only seeing 2 fields, I'll then be able to execute: SELECT PREPAREDFOR, PRODID, NSN FROM...

                            ...and I haven't even had breakfast yet!

                            Thanks for the help!
                            -John

                            Comment


                            • #15
                              ...so with a working DSN, would this be a proper schema.ini?
                              Code:
                              [schema.ini]
                              ColNameHeader=True
                              Format=TabDelimited
                              MaxScanRows=0
                              CharacterSet=OEM
                              Col1=COCID Char Width 255
                              Col2=PREPAREDFOR Char Width 255
                              Col3=TODAYSDATE Char Width 255
                              Col4=NSN Char Width 255
                              Col5=NEWCATNUM Char Width 255
                              Col6=OLDCATNUM Char Width 255
                              Col7=PRODID Char Width 255
                              Col8=NSN Char Width 255
                              Col9=CONTAINER Char Width 255
                              Col10=DATEOFMANUFACTURE Char Width 255
                              Col11=BATCHLOTCODE Char Width 255
                              Col12=CUSTOMERPONUMBER Char Width 255
                              Col13=OURINVOICENUMBER Char Width 255
                              Col14=PREPPEDBY Char Width 255
                              Col15=SPECIALFEATURES Char Width 255
                              Col16=EMAILTO Char Width 255
                              Eric, when I change to $SQ, I get this error:
                              ---------------------------
                              DEVTEST - after SQL_Stmt
                              ---------------------------
                              sSQLErrCode = [33878.879] SQL_Statement 1 1 -1 999999999 42000 -1002 -- [Microsoft][ODBC Text Driver] ''COC_LOG.tab'' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.
                              ---------------------------
                              OK
                              ---------------------------
                              and my code reports:
                              ---------------------------
                              Failure after SQL_FETCH
                              ---------------------------
                              SELECT * FROM 'COC_LOG.tab'
                              ---------------------------
                              OK
                              ---------------------------

                              At first I thought maybe the complaint was for the underbar in the name, but with DQs, it's not a problem???

                              -John


                              Comment


                              • #16
                                I'm thinking about the suggestion to read the TAB file into some other format. If an Excel file is not a good alternative, what would be better? Access? or some other product?

                                It's been many years since I was proficient in SQL, but even then I was not creating tables, just querying them. I'm not sure how to specify a particular "kind" of file (Access vs other)

                                And AFAIK I don't have a working DBMS installed. Possibly have a very old SQLitening that I never mastered, but I really wanted to keep this simple and maintaining another product is not what I had in mind...

                                -John

                                Comment


                                • #17
                                  At first I thought maybe the complaint was for the underbar in the name, but with DQs, it's not a problem???
                                  Nope, that's not it... I renamed and tried again with SQs, but got the same error messages as in the second half of post #15

                                  OK, now I'm reading up on CREATE TABLE... I'll be back later if any progress...

                                  -jhm

                                  Hmm... not sure what to CREATE TABLE "into"... In other words, do I need to have a file in the first place that I "CREATE TABLE" in?
                                  i.e., there needs to be a database already opened in order to CREATE TABLE...

                                  Comment


                                  • #18
                                    John -- If you have MS Access, I'd start there. IIRC, MS Access has an easy import wizard for creating tables. It also has a very slick query builder (QBE grid).

                                    Comment


                                    • #19
                                      Originally posted by John Montenigro View Post
                                      I'm thinking about the suggestion to read the TAB file into some other format. If an Excel file is not a good alternative, what would be better? Access? or some other product?

                                      It's been many years since I was proficient in SQL, but even then I was not creating tables, just querying them. I'm not sure how to specify a particular "kind" of file (Access vs other)

                                      And AFAIK I don't have a working DBMS installed. Possibly have a very old SQLitening that I never mastered, but I really wanted to keep this simple and maintaining another product is not what I had in mind...

                                      -John
                                      I'm biased. For this task, I wouldn't use PB, I'd do it all in Access.

                                      Access is a great tool for this sort of thing. Periodically Import (not link) the text file using the "External Data - Text" then use the QBE grid to create standard queries for your analyses.

                                      OTOH, it's only 300KB. Even if it grows to a few MB, you could just read the data into an array in PB.

                                      Then again, your current approach is fine and is helping you to learn more about ODBC and SQL, which is a good thing.

                                      Comment


                                      • #20
                                        Originally posted by John Montenigro View Post
                                        BUT!
                                        lNumColumns = SQL_TblColCount(1) always returns 2 (but the header contains 16 fieldnames),
                                        and
                                        SQL_TblColInfoStr(1,i,%TBLCOL_COLUMN_NAME) returns 2 strings:
                                        1. a string containing the first 48 or so characters of my header row (filenames),
                                        2. "F2"
                                        Please post the schema.ini file.

                                        F1, F2 etc are the default column names if you haven't specified names in schema.ini.

                                        If it is only finding two fields, I suspect that the column delimiter is NOT specified as Tab. What's the 49th (or so) character in the first row of the file?

                                        Comment

                                        Working...
                                        X