Posts Tagged ‘queries’
Monday, August 23rd, 2010
Hi,
I am using MS Word API to generate .docx which contains the data fetched from DB, in which i am applying the respective styles, fonts, symbols, etc. If the data fetched from the DB is quite huge, then there is a problem in displaying those data in the .docx file. I found that internally MS Word 2007 will write some content through tags which may not be needed to display the data. Hence i am figuring out what are the necessary MS Word tags needed when converting into a .xml file. So that i can avoid unnecessary tags and build only the respective tags which are needed to display the data. Hence i am planning to write my own .xml with the MS Word tags which are needed, than generating a .XML from .docx file
My queries are:-
1) Whether it is right that the MS Word will generate some tags which may not be needed during the conversion of .docx to document.xml? That makes it heavy? If so what are the tags , so that i can avoid them when write by own .xml file.
2) Please send links to understand about the MS Word tags and its advantages, which tags are needed and which are not ?
3) Whether my approach to write a new .xml similar to document.xml (.docx conversion) is worthy one to go forward so that i can build the .xml with the tags i needed , so that i can improve the performance of the data display?
Please shed some light into it and thanks in advance..
Thanks,
Rithu
Tags: conversion, docx file, ms word, queries, thanks in advance, unnecessary tags, worthy one, xml file
Posted in dotnet, vb, vb.net | No Comments »
Sunday, August 22nd, 2010
I have a pagination class the uses sql_calc_found_rows to get the total row count for the query. However, I am in the process of changing all my queries to prepared statements and now I can’t seem to use sql_cal_found_rows and select found_rows() to get total count.
Do I have to execute a second count(*) query if I am using prepared statements? Or am I just not using the correct syntax to get the count from select found_rows().
$sql = "select sql_calc_found_rows from tbl1 …")
$prep1 = $link->prepare($sql);
$prep1->execute();
$cnt = $link->query("select found_rows()");
Tags: calc, correct syntax, queries
Posted in dotnet, vb, vb.net | No Comments »
Saturday, August 21st, 2010
I have a prototype server[0] that’s doing an os.walk()[1] for each query a client[0] makes.
I’m currently looking in to ways of caching this data in memory, speeding up queries and hopefully allowing for expansion in to storing metadata and data persistence later on.
I’ve experimented with SQLite before, but I find SQL complicated for tree structures, so i thought I would get some advice before actually commiting to SQLite
so is there any cross-platform, embeddable or bundle-able non-SQL databases that might be able to handle this kind of data, with a small (10k-100k files) list and extremely small amount of connections 10-20 maybe? which will scale to handling metadata as well
[0] the server and client are actually the same piece of software, this is a P2P application, thats designed to share files over a local trusted network with out a main server, using zeroconf for discovery, and twisted for pretty much everything else
[1] query time is currently 1.2s with os.walk() on 10,000 files
here’s the related function in my python code that does the walking
def populate(self, string):
for name, sharedir in self.sharedirs.items():
for root, dirs, files, in os.walk(sharedir):
for dir in dirs:
if fnmatch.fnmatch(dir, string):
yield os.path.join(name, *os.path.join(root, dir)[len(sharedir):].split("/"))
for file in files:
if fnmatch.fnmatch(file, string):
yield os.path.join(name, *os.path.join(root, ile)[len(sharedir):].split("/"))
Tags: 100k, 2s, cross platform, discovery, embeddable, len, memory, metadata, p2p application, persistence, prototype server, python code, queries, query time, sql databases, sqlite, tree structures, trusted network, zeroconf
Posted in dotnet, vb, vb.net | No Comments »
Saturday, August 21st, 2010
Is there a way to force Mysql from PHP to kill a query if it didn’t return within a certain time frame?
I sometimes see expensive queries running for hours (obviously by this time HTTP connection timed out or the user has left). Once enough such queries accumulate it starts to affect the overall performance badly.
Tags: mysqlquery, queries, time frame
Posted in dotnet, vb, vb.net | No Comments »
Friday, August 20th, 2010
I have two queries in MS.Access, qry1 and qry2.
qry1 is based on table1,
qry1 has two fields i.e Qty and Product.
qry2 is sum of qry1.
qry2 has two fields i.e Sum_of_Qty and Product,
qry2 is Grouped on Product.
The problem is following,
Dim rs As ADODB.Recordset
Dim con As ADODB.Connection
Set rs = New ADODB.Recordset
Set con = New ADODB.Connection
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= D:DataWorkSolDotNetSolDotNet.mdb"
rs with Qry1 runs fine in MS.ACCESS VBA, MS.EXCEL VBA, VB6
- – - – - – - – - – - – -
rs.Open "qry1", con, adOpenStatic, adLockReadOnly
rs.Close
but rs with Qry2 gives error in MS.ACCESS VBA, perhaps runs fine in MS.EXCEL VBA and VB6.
- – - – - – - – - – - – -
rs.Open "qry2", con, adOpenStatic, adLockReadOnly
rs.Close
—————————————————-
Error : Run-time Error. ‘-2147217900(80040e14)’
Syntax error in FROM clause.
—————————————————-
can any one help me out of this
Tags: dim rs, ms access, queries
Posted in dotnet, vb, vb.net | No Comments »
Friday, August 20th, 2010
When providing software support it is important to be able to:
- See what other related problems an organisation currently having
- See what version of a piece of software a customer has, and what patches / fixes they have been supplied with
- Identify when multiple customers are experiencing a known issue
- Identify what customers have already experienced a particular issue
- Link questions or queries raised by customers to a knowledge base (internal or external)
- Be able to easily transfer a support issue to someone else and have them understand the issue & what has already been done
What software is available to help with tracking these sorts of things?
Tags: knowledge base, patches, providing software, queries, software support
Posted in dotnet, vb, vb.net | No Comments »
Friday, August 20th, 2010
My boss would like me to split our main database into two databases. I’m not really a fan of the idea, but as I have no experience with joins between databases other than knowing they are technically possible, I have no specific objections (well, other than it being more work for me without any clear benefit in my eyes…)
Are there issues with joining between two MySQL databases? Would it slow down queries significantly? Would I have to index things differently? Etc.?
Any real advantages?
Tags: advantages and disadvantages, benefit, boss, mysql databases, objections, queries
Posted in dotnet, vb, vb.net | No Comments »
Friday, August 20th, 2010
I have run in to a slight problem. The story goes as follows:
I have a document archive system (written in PHP) which runs at multiple clients (23 at present). On their system they only have their documents. Every night, they all need to be ‘synced’ to a master database on site (central server). I have access to each MySQL database from the central server, so connecting to them is no problem.
I have a script that connects to the client database, selects all the entries from a table where the sync column = ’0000-00-00 00:00:00′ (default to indicate it wasnt synced). I would then iterate through each record, insert it to the central server, and set the sync time on the client database record to the time the script was executed. This works, but obviously has a large overhead with the multiple queries and I have just noticed the problems now.
Each client can generate up to 2000 – 3000 odd documents a day. With these large numbers it is taking way too long (1sec / 2documents).
Is there a better solution to my problem? Preferably a PHP scripted solution as I need to do logs to check if everything was succesful.
Thanks
EDIT:
My current process is:
- Select all the un-synced data
- Begin transaction
- Insert record into central database server
- Select the document record from the client
- Insert the document into the central database server
- Update sync column on client
- Update sync column on server
- Commit transaction
This is a script run on the central server.
Now that I come to think of it, i can remove step 7 and have it part of step 5, but that wont reduce the processing time by much.
Tags: archive system, better solution, central database server, central server, client database, database record, document archive, document record, large numbers, logs, master database, mysql database, odd documents, processing time, queries, sync time
Posted in dotnet, vb, vb.net | No Comments »
Friday, August 20th, 2010
I have many, (15-20) different XML files that I need to load to VB.Net. They’re designed as they would be in a database; they’re designed in Access and bulk exported into XML files. Each file represents a different table in the database.
Now, I need to load this information into VB.Net. Initially, I’d love to use DAO and access the MDB directly via queries, but this won’t be possible as I’m making sure the project will be easily ported to XNA/C# down the road.
So, I’m stuck now trying to figure out how to wrangle together all of these XML files together. I’ve tried using Factories to parse each one individually. E.g., if three XML files contain data for a ‘character’ class, i’d pass in an instance of Character to each XML factory and the classes would apply the necessary data.
I’m trying to get past this though, as maintaining many different classes with redundant code is a pain. plus it is hard to debug as well. So I’m trying to figure out a new solution.
The only thing I can think of right now is using System.Reflection, where I parse through each member of the class/structure I’m instantiating, and then using the names of those members to read in the data from that element of the XML file.
However, this makes the assumption that each member of the structure/class has a matching element in the XML file, and vice-versa.
Tags: assumption, character class, class structure, dao, element, factories, love, mdb, necessary data, new solution, queries, redundant code, reflection, structure class, xml file
Posted in dotnet, vb, vb.net | No Comments »
Thursday, August 19th, 2010
Hello,
I am trying to adjust the Criteria of an Update Query based on a multi-select List Box in a Form. I have attached the simple database i am using during my testing of this functionality. Below is some more detailed information.
Below are the tables/queries/list boxes etc in my database:
Table: Operator Rates
Field to update: New Rate per M-Unit
Apply update based on field: Basic Material
Query: Update
Form: Adjust Date
List Box: lstBasicM
Text Box: txtRate
Specifically what i am trying to do is update the New Rate per M-Unit field in table Operator Rates based on the Basic Material selected from the Adjust Date form using the List Box lstBasicM to pass the criteria and update based on the data entered in the text box txtRate.
I know that i cannot simply insert Forms![Adjust Date]![lstBasicM] into the Criteria field of the query as you would using a combo or text box.
I have tried copying a variety of different codes in different places but cannot get anything to work. Currently I am trying to create a command button that, in theory, on click creates a string which would be passed as the criteria to the Update Query. I am not even sure if that is the right approach and how the string needs to get passed to the criteria field in the query
Below is the code I have started in the on click command that i have pulled and adjusted from a messgae board:
Dim SQLStr As String
Dim vSel As Variant
With Adjust Date!lstBasicM
If lstBasicM.ItemsSelected.Count > 0 Then ‘ Did user select any rows?
SQLStr = SQLStr & " AND [Basic Material] IN("
For Each vSel In lstBasicM.ItemsSelected
SQLStr = SQLStr & lstBasicM.Column(0, varItem) & ", "
Next vSel
SQLStr = Left(SQLStr, Len(SQLStr) – 2) & ");"
End If
End With
I am a novice MS Access user and do not really know VBA/SQL so any help is much appreciated!
Many thanks!
Joe
Tags: boxes, command button, database table, different places, functionality, messgae board, queries
Posted in dotnet, vb, vb.net | No Comments »
