Thomas Sampson

Category Archives: SQL


by 1 Comment

SQL Generator

I found this tool today which allows you to use a webUI to design a database table, and will then generate you the SQL code to create that table at runtime.

http://ondras.zarovi.cz/sql/demo/

I used this to design a table to use with sqlite3 and although the code generated is intended for use with mysql, a few minor adjustments allowed me to execute this on a sqlite3 database with no problems.


by 2 Comments

Odbc Memory allocation error

Today I tried upgrading the odbc drivers on my server from 3.5 to 5.1. I immediately ran in to troubles and got a “Memory allocation error” when executing a non query to mySql. After reverting back to driver version 3.5 i found that the error was caused simply by a “data too long for field” error which i quickly fixed in no time at all.

I am not sure if the memory allocation error i received with the new drivers was related to this but if anyone could explain this I would love to know whats going off. If anyone gets a memory allocation error in 5.1 it might be worth checking to ensure the data your inserting fits into the row.


by Leave a comment

Connection string rules

Important rules for connection strings

This is general rules adopted by most drivers and providers. However, keep in mind that these rules is not 100% accurate for every driver and every situation.

Never the less, following and knowing about these basic rules will keep some common problems out of your way. Okay? Here we go..
  • All blank characters, except those placed within a value or within quotation marks, are ignored
  • Blank characters will though affect connection pooling mechanism, pooled connections must have the EXACT same connection string
  • If a semicolon (;) is part of a value it must be delimited by quotation marks (“)
  • Use a single-quote (‘) if the value begins with a double-quote (“)
  • Conversely, use the double quote (“) if the value begins with a single quote (‘)
  • No escape sequences are supported
  • The value type is NOT relevant
  • Names are case iNsEnSiTiVe
  • If a KEYWORD=VALUE pair occurs more than once in the connection string, the value associated with the LAST occurrence is used
  • But!… if the PROVIDER keyword occurs multiple times in the string, the FIRST occurrence is used.
  • If a keyword contains an equal sign (=), it must be preceded by an additional equal sign to indicate that it is part of the keyword.


by 4 Comments

SQL Table of countries

I realised many online data driven applications require some form of list of countries.

Therefore I managed to get the official list of global countries online and generate an SQL script to insert these into your database.

Without modification this script inserts into the table name “tbl_countries“

into the column “country_name” (PRIMARY KEY)

link to script


by Leave a comment

Dates!

I took this evening out to play aruond with dates n times , with regards to inserting and retrieveing and manipulating to and from a database.

fisrt things first, in a sql insert you can use Now() to put the date and time into most databases, provided that the filed is cast as a Date/Time field.

example

insert into mytable (‘id’,’text’,Now(),’helooo’);

so now you have a workable date in your table.

This is fine but you want to bind them to a DateTime type in .net when you read them. This allows you to do example…

bindeddatetime.Hour
indeddatetime.ToShortDateString();

etc etc.

to do this, simply convert the data from your data reader

Example


while(mydatareader.Read())
{DateTime thedate=Convert.ToDateTime(mydatareader[“timefield”]);
//notice time field not converted to string at all, just binded straight to a DateTime
}

Conclusion

Your timestamp is now in a format ready to

  • manipulate,
  • sort ( ORDER BY DATE DESC)
  • add, time difference, etc

Associated links


by Leave a comment

Avioding SQL injection

After much playing around and problems I finally figured how to add safe parameters into an odbc command. Here is a sample….

OdbcCommand postcomment = new OdbcCommand(“insert into comments values (‘”+newguid+”‘,'”+itemid+”‘,?)”, temp);

Here the ? represents the first and only parameter to be passed into the command using..

postcomment.Parameters.AddWithValue(“@comment”, comment);

then

postcomment.ExecuteNonQuery();

If you were to include > 1 ? symbols, the parameters must be added in the order they appear in your original command.