_sqlite_exec trimming cells

[Jochem]

For $i = 9 To UBound($aSoftwarecheck[0][0]) Step -1
$APP = $aSoftwarecheck[$i][0]
_SQlite_Query (-1, "SELECT " & $APP & " FROM " & $table & " WHERE computer LIKE 'w%';", $hQuery)
While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK ;1 row at a time
$sMsg = $aRow[0]if StringInStr($sMsg," ")then
$aupdatedversion = stringsplit($sMsg," ")_SQLite_Exec(-1, "update " & $table & " set " & $APP & "='" & $aupdatedversion[2];")
endif
WEnd
next

I have a problem with removind a part of some collumns. I have a collumn with the software name + version and now I want to remove the software name and keep the version in the table. (The collumns allready contains the names).

so I made the above code but the the sqlite exec needs to run the command on the row the selected by the sqllte_fetchdata command. And i don`t know how

by the way is there a good tutorial somewhere about sql commands?? My knowledge is 0....

[jchd]

You're not giving enough information for us to help you. We need to know:

What is your schema, which means: show us the SQL CREATE statement used to create your table and everything related to this table (index, ...).

I understand that your "name" column carries the name of the software along with its version. I get that you want to remove the name part, which you store elsewhere in the row.

An easy way to do that would be to select both the "name" column and the column where the name (only) is stored. Then it would be very easy to remove the name from "name". That is, provided the name column is identical to the name part of "name". If we can rely on that, the whole operation can be carried out with a single SQL statement. If names part may differ, then you'll have to expose us how you determine what is the name part and consequently what is the version part. In other words we need a programmatic way to split the string.

Please try to understand that your naming of columns is unfortunate and prone to gross errors.

Showing a few complete rows with column names would definitely help to help you.

There are too many tutorials to list about generic SQL on the web, so: http://lmgtfy.com/?q=sql+tutorial and some SQLite-specific tutorials http://lmgtfy.com/?q=sqlite+tutorial

Edited October 27, 2011 by jchd

[Jochem]

#include <SQLite.au3>
#include <networklist.au3>
 
global $aCompListtotaal = _netwerklist(0x1), $sCompList = _ArrayToString($aCompListtotaal, @LF)
global $aCompList = StringRegExp($sCompList, "(?i)(ws\-[^l].*)(?:\v*|\z)", 3)
global $hQuery, $aNames, $aRow
global $table = "INVENTORY"
global $aSoftwarecheck[10][3] = [ _
["profit", "profit", ""], _
["adobe_CS", "CS", ""], _
["adobe_CS", "CS", ""], _
["acrobat_reader ", "reader", ""], _
["flash", "flash", ""], _
["java", "Java(TM)", ""], _
["norton", "symantec", ""], _
["sketchup", "sketchup", ""], _
["autocad", "autocad", ""], _
["profit_web_wizard", "profit web wizard", ""]]
 
_SQLite_Startup()
_SQLite_Open(".\sql\KCAP_SYSTEEMBEHEER")
For $i = 9 To UBound($aSoftwarecheck[0][0]) Step -1
$APP = $aSoftwarecheck[$i][0]
_SQlite_Query (-1, "SELECT " & $APP & " FROM " & $table & " WHERE computer LIKE 'w%';", $hQuery)
While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK ;1 row at a time
$sMsg = $aRow[0]
ConsoleWrite($sMsg & @CRLF)
if StringInStr($sMsg," ")then
$aupdatedversion = stringsplit($sMsg," ")
ConsoleWrite($aupdatedversion[2] & @CRLF)
;~  _SQLite_Exec(-1, "update " & $table & " set " & $APP & "='" & $aupdatedversion[2] & "' where computer LIKE 'w%';")
endif
WEnd
next
_SQLite_Close()
_SQLite_Shutdown()

this is the complete code, but not working offcoarse because my last _SQLite_Exec command is running on all cell instead of running on the cell selected by the loop "While _SQLite_FetchData"

the schema is like

computer autocad adobe

-----------------------------------------------------------

ws-c99 autocad 2011 adobe CS3

ws-c100 autocad 2012 adobe CS5

etc for the rest of the used software and I want a table like

computer autocad adobe

-----------------------------------------------------------

ws-c99 2011 CS3

ws-c100 2012 CS5

Edited October 28, 2011 by Jochem

[Zedna]

Good SQL tutorial

http://www.w3schools.com/sql/default.asp

[Jochem]

Good SQL tutorial

http://www.w3schools.com/sql/default.asp

thanks that is a nice one!

[jchd]

I start to get what you have.

OK, let me put it straight: what you have is SQL used 100% backwards.

Let's gone one step at a time. I asked for the schema: this is the exact SQL statement used for creation of the database. That doesn't seem to carry any personal information nor the recipe of any MacDonald sauce so please do that.

I wished to see something like: create table inventory (...) ... but the real thing!

I suspect you also have a table of computers in the same DB. Show me its schema as well.

You should never have several software columns in your inventory: your DB is awfully denormalized. Normalization means that a single information is only present _once_ in the DB. If you and your company are serious about maintaining a DB I urge you to consider this as a primary truth.

Anyway can you please FIRST make a backup of your DB then only try this:

#include <SQLite.au3>
;~ #include <networklist.au3>
;~ global $aCompListtotaal = _netwerklist(0x1), $sCompList = _ArrayToString($aCompListtotaal, @LF)
;~ global $aCompList = StringRegExp($sCompList, "(?i)(ws\-[^l].*)(?:\v*|\z)", 3)
;~ global $table = "INVENTORY"
;~ Global $hQuery, $aNames, $aRow
Global $aSoftwarecheck[9][3] = [ _
  ["profit", "profit", ""], _
  ["adobe_CS", "CS", ""], _
  ["acrobat_reader ", "reader", ""], _
  ["flash", "flash", ""], _
  ["java", "Java™", ""], _
  ["norton", "symantec", ""], _
  ["sketchup", "sketchup", ""], _
  ["autocad", "autocad", ""], _
  ["profit_web_wizard", "profit web wizard", ""] _
]
;~   ["adobe_CS", "CS", ""], _  ; duplicate entry removed
_SQLite_Startup()
Local $hDB = _SQLite_Open(".\sql\KCAP_SYSTEEMBEHEER")
Local $aRows, $nrows, $ncols, $aupdatedversion
_SQLite_Exec($hDB, "begin immediate;")
For $i = 0 To UBound($aSoftwarecheck) - 1
_SQLite_GetTable2d($hDB, "SELECT rowid, [" & $aSoftwarecheck[$i][0] & "] FROM inventory WHERE computer LIKE 'w%';", $aRows, $nrows, $ncols)
For $j = 0 To UBound($aRow) - 1
  ConsoleWrite($aRows[$j][1] & @CRLF)
  If StringInStr($aRows[$j][1], " ") Then
   $aupdatedversion = StringSplit($aRows[$j][1], " ")
   ConsoleWrite($aupdatedversion[2] & @CRLF)
   _SQLite_Exec($hDB, "update inventory set [" & $aSoftwarecheck[$i][0] & "] = " & $aupdatedversion[2] & " where rowid = " & $aRows[$j][0])
  EndIf
Next
Next
_SQLite_Exec($hDB, "commit;")
_SQLite_Close()
_SQLite_Shutdown()

Realize that what you're going to do is the following:

For each software:

For each 'w%' computer:

trim the app if needed

next

next

With a normalized DB, you would have a table of computers, a table of softwares (editor, name, version) and a table of cross-links between those two tables (and even a separate list of editors!). SQL is not an Excel spreadsheet and even if a normalized design is much harder to follow for human eyes, SQL has been precisely designed to manipulate a bunch of thusly linked tables _very_ efficiently and produce highly flexible reports.

With the current design, should you have a very detailed software inventory on a large list of computers, you'd end up with an unmanageable inventory table having hundreds or even thousands of columns. This doesn't make sense. Also this design precludes situations where a computer has more than one version of a given software installed.

Don't hesitate to ask if something isn't clear (or not working as expected, since I have no way to test the code I guessed).

[Jochem]

Hi, thanks for help,

I used a script to convert my excel spreadsheet to a database. I had a (not to big) spreadsheet with 10 software packages and some other data. And a script to update / check en run some commandos remote on all the computer in the network. All based on the information in the spreadsheet.

The problem with excel was that I need to have excel installed from the computer that I am running the script. So I decided to change it to a DB.

And every works now.

I will have to overlook the schema.

Is there a way to get the schema, because I never created the DB; I converted my spreadsheet?

It can be very usefull to generate some nice reports etc.

I allready printed some tutorials to check.

Edited October 28, 2011 by Jochem

[jchd]

Go download the freeware version of SQLite Expert. This is the best free SQLite manager I know of. You'll have good use of it anyway as it will enormously help you learn SQL and SQLite use.

Open your DB and hit the Schema tab. This displays the overall schema of the entire DB. For exact detail about each table, select the table in the left pane and hit the DDL tab. This displays the exact SQL statements used to create the table and indices, constraints, etc.

If your DB is that simple, you can copy and paste that. I'll then guide you towards a better design and SQL reporting.

Viewing your table is as simple as hitting the Data tab. You can edit it from there (but beware, databases don't have Undo!)

Edited October 28, 2011 by jchd

[Jochem]

so the schema is :

CREATE TABLE [iNVENTORY] (

[computer] NOT NULL,

[user] ,

[servicetag] CHAR,

[mac_adres] CHAR,

[install_date] DATETIME,

[type] ,

[windows_version] ,

[windows_serial] ,

[office_version] ,

[office_serial] ,

[adobe_CS] ,

[acrobat_reader] ,

,

[java] ,

[norton] ,

[sketchup] ,

[autocad] ,

[profit] ,

[profit_web_wizard] );

as you see any constrain, more like a spreadsheet.

The problem with trimming is already solved actualy, I did that just before your post last time, with this code:

yours is more flexible, so I keep yours.

_SQLite_Startup()
_SQLite_Open(".\sql\KCAP_SYSTEEMBEHEER")
for $ii = 10 To 150 step 1
$cI_CompName = "WS-C" & $ii
ConsoleWrite($cI_CompName & @crlf)
For $i = 9 To UBound($aSoftwarecheck[0][0]) Step -1
$APP = $aSoftwarecheck[$i][0]
_SQlite_Query (-1, "SELECT " & $APP & " FROM " & $table & " WHERE computer='" & $cI_CompName & "';", $hQuery)
While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK ;1 row at a time
$sMsg = $aRow[0]
ConsoleWrite($sMsg & @CRLF)
if StringInStr($sMsg," ")then
$aupdatedversion = stringsplit($sMsg," ")
ConsoleWrite($aupdatedversion[2] & @CRLF)
_SQLite_Exec(-1, "update " & $table & " set " & $APP & "='" & $aupdatedversion[2] & "' where computer='" & $cI_CompName & "';")
endif
WEnd
next
next
_SQLite_Close()
_SQLite_Shutdown()

Edited October 31, 2011 by Jochem

[Jochem]

Oke, I have setup some constraints, wasn`t that hard after all. but i have a question about setting up the database further:

I have now a table with computers (ID, NAME)

and a table with software (ID, TITLE, VERSION)

and after all a table inventory (COMPUTER_ID, SOFTWARE_ID)

CREATE TABLE [COMPUTERS] (

[iD] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

[NAME] CHAR NOT NULL UNIQUE COLLATE NOCASE);

CREATE TABLE [sOFTWARE] (

[iD] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

[TITLE] CHAR NOT NULL UNIQUE COLLATE NOCASE,

[VERSION] CHAR NOT NULL);

CREATE TABLE [iNVENTORY_TOTAL] (

[COMPUTER_ID] INTEGER NOT NULL CONSTRAINT [COMPUTERS] REFERENCES [COMPUTERS]([iD]) ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY DEFERRED,

[sOFTWARE_ID] CHAR NOT NULL CONSTRAINT [sOFTWARE] REFERENCES [sOFTWARE]([iD]) ON DELETE CASCADE ON UPDATE CASCADE MATCH FULL NOT DEFERRABLE INITIALLY DEFERRED);

I am filling the table with:

_SQLite_Exec(-1,"INSERT OR IGNORE INTO INVENTORY_TOTAL (COMPUTER_ID,SOFTWARE_ID) VALUES((select ID from COMPUTERS where NAME = '" & $cI_CompName & "'),(select ID from SOFTWARE where TITLE = '" & $APP & "'));")

But what is the normal schema for such an table, do I need to make for all the softwares in the inventory table an collumn? or am I missing something?

Edited November 2, 2011 by Jochem

[jchd]

For the record, this thread turned out into yet another SQL tutorial rallye. It has little AutoIt relevance and I switched to PMs with this member.